Power Query Data Types: Choosing, Changing, and Fixing Them

Power Query data types tell the editor what kind of value each column contains—such as text, a whole number, a date, or a time—and determine which transformations make sense. Choose a type that matches the meaning of the data, then check that Power Query interpreted every row correctly before loading the result.

Why data types matter

A value can look right on screen but behave incorrectly if Power Query assigns it the wrong type. For example, the text values “2” and “10” sort alphabetically, putting 10 before 2; numbers sort numerically. A date stored as text may not group by month or support date calculations. A numeric-looking customer ID may lose leading zeros if treated as a number.

Types also affect which operations are available. Power Query can add two numbers, compare dates, or combine text, but it cannot reliably perform a numeric calculation on text without converting it first. A conversion that encounters an incompatible value can produce an error, so type selection is part of data cleaning, not merely a display choice.

Power Query’s common data types

The type menu in Excel’s Power Query Editor includes the types most people need for everyday tables. Names may vary slightly between the menu and the underlying M language, but the underlying distinctions are useful to know:

Text: Words, labels, codes, and identifiers. Use it for values such as postal codes or product numbers when leading zeros or exact characters matter.
Whole Number: Integers without a fractional part, such as a count of items. Power Query represents this as a 64-bit integer.
Decimal Number: Numbers that can have a fractional part. This is a floating-point type, so some decimal fractions may be represented approximately.
Fixed Decimal Number: A decimal type intended for values needing consistent decimal precision, commonly currency-like amounts. It stores four digits after the decimal point.
Percentage: A numeric proportion displayed or treated as a percentage. A value of 0.15 represents 15%; do not convert it to 15 unless the source data actually uses 15 to mean 15 percent.
Date: A calendar date without a time of day.
Time: A time of day without a date.
Date/Time: A date and time together, without a time-zone offset.
Date/Time/Timezone: A date and time that includes a UTC offset, such as -05:00.
Duration: An elapsed span of time, rather than a time of day. It can represent intervals such as two days and three hours.
True/False: Logical values, commonly used for flags and yes/no conditions.
Binary: Raw data such as file contents. It is common in folder and file-combination workflows rather than ordinary analytical columns.
Any: A flexible type that can hold different kinds of values. It can be useful during early import steps, but a final table is usually easier to analyze when its columns have deliberate types.

The M language also has structural types such as list, record, and table, as well as function and null-related types. These matter when building more advanced queries, but they are not typical choices for a flat column in a worksheet.

How to choose the right type

Choose based on what a value means and what you need to do with it, not only on how it looks. A column of numbers can still be text if the values are identifiers. Conversely, a date displayed as a string may need conversion before it can be used as a date.

Use Text for IDs, phone numbers, postal codes, and other codes where arithmetic is meaningless or leading zeros matter.
Use Whole Number for counts that cannot be fractional, such as units sold, after checking that all source values are whole numbers.
Use Decimal Number for measurements or calculations where fractional values are expected and approximate floating-point arithmetic is acceptable.
Consider Fixed Decimal Number for currency-like values where predictable decimal places are important. Check the source precision first; values with more than four fractional digits cannot be represented at full precision by this type.
Use Date for dates that should not shift with a time zone, and Date/Time when the time of day is meaningful too.
Use Date/Time/Timezone when the offset is part of the source information. Do not assume that a date/time value without an offset identifies a universal moment.

For example, a column containing account numbers 00124 and 00125 should generally be text: converting it to a number can remove the zeros. A sales amount such as 24.50 is numeric, while a discount rate stored as 0.15 is a proportion that represents 15%. The distinction prevents accidental scaling and formatting mistakes.

Change a column’s type in Excel

In Excel for Microsoft 365 on Windows, select a query and choose Data > Queries & Connections to open its query list, then double-click the query to open the Power Query Editor. In the Editor, select the column and use the data type control on the Transform tab, or right-click the column header and choose Change Type. Choose the intended type from the list.

Power Query records the conversion as a step, usually named Changed Type, in the Applied Steps pane. You can select that step to inspect its effect, remove it with the step’s delete control, or adjust its position. Step order matters: converting a value before cleaning unwanted characters can create errors, while cleaning it first may allow a successful conversion.

To set types for several columns, select the columns together and apply the relevant type, where appropriate. For a repeatable query, review the Applied Steps sequence and confirm that the type step targets the intended columns. If the source schema changes, a step that refers to a removed or renamed column can itself cause a query error.

Power Query’s interface on Mac and Excel for the web can differ from the Windows ribbon, and query authoring availability depends on the Excel version. The type concepts and M transformations are shared, but do not assume every ribbon path or editing feature appears in every platform’s interface.

Automatic type detection and its limits

Power Query may add a Changed Type step automatically. For unstructured sources such as text and CSV files, automatic detection examines an initial sample—typically the first 200 rows—to infer column types. That is a convenience, not a guarantee that every later row follows the same pattern. A date column may look consistent in the sample and contain an unexpected text value much farther down.

Structured sources can provide type information of their own, so detection behavior depends on the connector and source. Either way, inspect the resulting types rather than relying on inference alone. If a source routinely changes or contains mixed data, consider disabling or removing an inappropriate automatic step and explicitly setting the types after the necessary cleanup.

A practical check is to filter a column for errors after conversion and inspect distinct values in columns that look suspicious. Also check the bottom of a large source or use profiling tools over the entire dataset when available; a preview alone may not expose rare exceptions.

Locale, dates, and number formats

Locale determines how Power Query interprets text that represents dates and numbers. For example, 03/04/2026 can mean March 4 or 3 April depending on the date convention. Likewise, a comma may be a decimal separator in one locale and a thousands separator in another. Merely changing a column’s type may therefore parse the same text differently on different systems or for data produced in another region.

When a conversion needs a particular convention, select the column and choose Transform > Data Type > Using Locale, then specify the desired type and locale. This tells Power Query how to interpret the text; it does not change what the source string originally said. When dates are ambiguous, confirm the source system’s convention before choosing a locale.

Keep parsing separate from display. A date type represents a date value; a worksheet’s cell format controls how a loaded value is shown. Similarly, formatting a number to display a percent sign does not establish that the underlying value was correctly scaled. Verify both the interpreted value in Power Query and the presentation after loading.

Common type errors and how to fix them

A type-conversion error usually means at least one value does not fit the requested type, or the text cannot be interpreted using the chosen locale. For instance, converting a column containing “12”, “15”, and “not recorded” to Whole Number will fail on the last entry unless you decide how that value should be handled.

Find the offending rows: Select the error indicator or use the column filter to show errors, then inspect the source values and the step that introduced them.
Clean before converting: Remove extraneous spaces or symbols where appropriate, and handle placeholders such as “N/A” consistently. Do not silently replace meaningful values with zero.
Choose an explicit policy: Keep a mixed column as text, replace known placeholders with null, or filter invalid rows if that matches the purpose of the analysis.
Check locale: If valid dates or decimals become errors or unexpected values, retry the conversion with the source’s actual locale.
Recheck precision: If a decimal changes after conversion, determine whether floating-point behavior or the four-decimal limit of Fixed Decimal Number is relevant.

Null is not the same as zero, an empty string, or the text “null.” A null represents a missing or absent value. Keeping that distinction can matter in filters, aggregations, and later calculations. Decide how missing data should be treated based on the meaning of the column rather than changing it simply to eliminate an error indicator.

Power Query types versus Excel cell formats

Power Query’s type describes values during import and transformation. Excel’s number format controls how a cell is displayed after loading, such as showing a date as 3/4/2026 or a decimal as currency. They are related but not interchangeable: changing a worksheet format cannot repair a text value that Power Query failed to parse, and changing a query type is not the same as choosing a preferred display format.

When loading to a worksheet, inspect the result for leading zeros, dates, percentages, and large numbers. Excel worksheet cells have their own limits and formatting behavior; a worksheet display is not a complete record of every distinction in Power Query’s type system. If the query feeds the Excel Data Model or Power BI, confirm the model’s formatting and type settings there as well.

Frequently Asked Questions

What is the default data type in Power Query?
Power Query can initially treat values as Any, but it may add an automatic Changed Type step based on the source or inferred values. Check the query’s Applied Steps and column icons rather than assuming every imported column remains Any.

Should IDs be numbers or text in Power Query?
Use text when an ID is a label rather than a quantity, especially if it can contain letters or leading zeros. Numeric types are appropriate only when you need numeric operations and the identifier’s full representation is preserved.

Why does Power Query show errors after changing a type?
One or more values may not be valid for the selected type, or dates and numbers may be interpreted using the wrong locale. Filter for errors, inspect the source values, and clean or parse them before converting.

What is the difference between Date/Time and Date/Time/Timezone?
Date/Time contains a date and clock time without an offset. Date/Time/Timezone includes an offset from UTC, which carries additional information about the represented time. Use the latter only when the source provides meaningful time-zone information.

Does Percentage mean I should multiply by 100?
Usually not. A proportion of 0.15 represents 15 percent; the percentage type or display format expresses that relationship. Multiplying by 100 as well would turn it into 1500 percent.

Can I undo a type change?
Yes. In the Power Query Editor, remove or edit the Changed Type step in Applied Steps, then apply the query changes again. If that step also performs other needed conversions, revise it carefully rather than deleting useful transformations indiscriminately.

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.