Add or change data types (Power Query)

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.

Icon

Data type

Description

The Any data type icon

Any

Indicates no explicit data type definition.

TBD

Binary

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

TBD

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.

TBD

Date

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

TBD

Date/Time

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

TBD

Date/Time/TimeZone

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

TBD

Duration

A length of time converted into a Decimal Number.

TBD

True/False

A Boolean value of either True or False.

TBD

Decimal number

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

TBD

Percentage

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

TBD

Text

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

TBD

Time

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

TBD

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}})

Procedure

  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 (docs.com)

A subscription to make the most of your time

Need more help?

Expand your Office skills
Explore training
Got It
Get instant Excel help
Connect to an expert now
Subject to Got It terms and conditions

Was this information helpful?

Thank you for your feedback!

Thank you for your feedback! It sounds like it might be helpful to connect you to one of our Office support agents.

×