Sign in with Microsoft
Sign in or create an account.
Select a different account.
You have multiple accounts
Choose the account you want to sign in with.

You can define and detect a data type, but most of the time you don’t have to. By default, data type detection occurs automatically when you connect to:    

  • Structured data sources    Examples include all databases. Power Query reads the table schema from the data source and automatically displays the data by using the correct data type for each column.

  • Unstructured sources    Examples include Excel, CSV, and text files. Power Query automatically detects data types by inspecting the values in the table.

By default, automatic data type detection is enabled in Power Query for unstructured sources, but you can change the option setting. In some cases, you may not want these steps to automatically occur because the action might cause refresh errors of your data source. For more information, see Handling data source errors.

The data types used in Power Query are listed in the following table.


Data type


The Any data type icon


Indicates no explicit data type definition.



A binary value, such as Y/N or 0/1.


Fixed decimal number

Has a fixed format of four digits to the right and 19 digits to the left. Also known as the Currency type.



A date with no time and having a zero for the fractional value.



A date and time value stored as a Decimal Number type.



A UTC Date/Time with a time-zone offset.



A length of time converted into a Decimal Number.



A Boolean value of either True or False.


Decimal number

A 64-bit (eight-byte) floating point number.



A fixed decimal number with a mask to format as a percentage.



Strings, numbers, or dates represented in a text format.



A time with no date having no digits to the left of the decimal place.


Whole Number

A 64-bit (eight-byte) integer value.

The data type of a column is displayed on the left side of the column heading with an icon that symbolizes the data type.

Example of data type icons to the left of column headers

  1. To open a query, locate one previously loaded from the Power Query Editor, select a cell in the data, and then select Query > Edit. For more information see Create, load, or edit a query in Excel (Power Query).

  2. To detect a data type, select a column, and then select Transform > Detect Data Type. You might do this if the current data type is Any, and you want the column to have a specific data type.

  3. To define a data type, select Home > Data Type, and then select a data type from the drop-down menu.

Tip      You can also select the icon on the left side of the column heading. In addition to each data type, you can also select Using locale at the bottom of list to display the Change Type with Locale dialog box, to both select a data type and apply a specific locale to it. This locale overrides the Power Query locale setting. For more information, see Set a locale or region for data (Power Query).

By default for unstructured sources, Power Query automatically inspects and detects column types and headers based on the first 200 rows of your table. When this setting is enabled, Power Query automatically adds two steps to your query immediately after the first Source step:

  • Step: Promoted Headers    Promotes the first row of the table to be the column header. This step is the equivalent of the Use First Row as Headers command in the Home tab. For example:

    = Table.TransformColumnTypes(#"Promoted Headers,{{"OrderID", type number}, {"CustomerID", type text}, {"EmployeeID", type number}, {"OrderDate", type date}, {"RequiredDate", type date}, {"ShipName", type text}})

  • Step: Changed Type    Converts the values from the Any data type to a data type based on the inspection of the values from each column. This step is the equivalent of the Detect Data Type command in the Transform tab. For example: 

    = Table.TransformColumnTypes(Source,{{"OrderID", type number}, {"CustomerID", type text}, {"EmployeeID", type number}, {"OrderDate", type date}, {"RequiredDate", type date}, {"ShipName", type text}})


  1. In the Power Query Editor, Select File > Options and settings > Query Options.

  2. Set the option for all your workbooks      In the left pane under GLOBAL, select Data Load, and then in the right pane under Type Detection, select one of the following options:

    • Always detect column types and headers for unstructured sources

    • Detect column types and headers for unstructured sources according to each file's setting

    • Never detect column types and headers for unstructured sources

  3. Set the option for the open workbook       In the left pane under CURRENT WORKBOOK, select Data Load, and then in the right pane under Type Detection, select or clear Detect column types and headers for unstructured sources.

See Also

Power Query for Excel Help

Add a column based on a data type

Data types in Power Query (

Need more help?

Want more options?

Explore subscription benefits, browse training courses, learn how to secure your device, and more.

Communities help you ask and answer questions, give feedback, and hear from experts with rich knowledge.

Was this information helpful?

What affected your experience?
By pressing submit, your feedback will be used to improve Microsoft products and services. Your IT admin will be able to collect this data. Privacy Statement.

Thank you for your feedback!