Add or change data types (Power Query)

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

Icon	Data type	Description
	Any	Indicates no explicit data type definition.
	Binary	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.
	Date	A date with no time and having a zero for the fractional value.
	Date/Time	A date and time value stored as a Decimal Number type.
	Date/Time/TimeZone	A UTC Date/Time with a time-zone offset.
	Duration	A length of time converted into a Decimal Number.
	True/False	A Boolean value of either True or False.
	Decimal number	A 64-bit (eight-byte) floating point number.
	Percentage	A fixed decimal number with a mask to format as a percentage.
	Text	Strings, numbers, or dates represented in a text format.
	Time	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.

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.