What are Data Types in Excel Power Query

Excel Power Query is a powerful inbuilt tool provided by Microsoft in Excel versions 2016 and higher. The power query feature allows you to get and extract data from multiple internal and external sources to Excel. This feature imports the data from the source to excel and displays it in an excel table format. As it creates an excel table, the column header data types play a very important role in Excel Power Query.

In this tutorial, we would learn everything about the data type, its importance, and what happens if you do not give ignore the power query header data type. We would also unlock multiple ways to change the data type of table headers in power query.

Power Query Data Types – Meaning and Types

The term Data Types is quite a self-explanatory word. Data Types provide information about the type of data that exists in a particular column in an excel table. There are various data types available in the power query, listed below

  1. Text – The text data type represents anything (string, number, or date) which is in a text format.
  2. True/False – This represents a boolean character (value is either True or False).
  3. Decimal Number This data type can handle any decimal number (negative or positive) with any number of digits after the decimal point. It also accepts a whole number (without decimal digits).
  4. Currency – Unlike the Decimal Number, the Currency data type has a fixed decimal point location which is at the fourth character from the right. Another name of this data type is Fixed Decimal Number.
  5. Whole Number – This numeric data type is generally used when you are not sure about the number of digits. It can accept positive or negative integer number with 19 digits. In the case of whole numbers, there are no digits after the decimal point.
  6. Percentage – Percentage is similar to the Decimal Number data type. However, it represented in a percentage format.
  7. Date/Time – The column formatted as Date/Time shows both the values – date as well as time.
  8. Date – This data type only displays the Date value.
  9. Time – This data type only shows the Time value
  10. Date/Time/TimeZone – When you apply this data type to a column, you would see the data having all – date, time as well as the time zone.
  11. Duration – This data format shows the column values in duration format.
  12. Binary – This shows any data other than listed above.

Background – Power Query Data Types

When you import any table data into a power query, you would notice that the power query automatically performs some pre-defined steps on the imported data. One of these default steps is the ‘Changed Type‘ step.

In this step, the power query intelligently understands the content of the imported data and detects/applies the data type to the column based on the first 200 values in each of the columns.

For example – Let’s say, if a particular column in the imported data contains date values, then the power query would detect this data and change the data type of the column to ‘Dates’. Likewise, if a column contains only string characters, then it would smartly convert the data type to ‘Text’.

Where Can I Check Table Data Types?

To check the applied data types, just have a look at the column headings in the ‘Power Query Editor’ window. On the left end of the column headers, you would notice small icons (which may be either the same or different in each of the columns). These are nothing but the intelligently detected table column data types.

In the below image you can see the icon as ‘ABC’. This denotes the ‘Text’ data type for the three columns.

Data Types on Column Header Power Query

You can use the ‘Detect Data Type’ option on the ‘Transform’ tab (in Power Query Editor window) to help excel automatically detect the data types for your table headers, as shown below:

Detect Data Type option

The detected data types may be the correct one or maybe wrong. The accuracy of data type detection is totally dependent on the structure of the source data. As in the above image, you can see that the table values for column B and C is numeric, however, the detected data type is ‘Text’.

If you find these data types incorrect, you can manually change it by following any of the below methods.

Data Types in Power Query

How To Change Data Types in Power Query?

There are multiple ways to change or manually update the power query data types.

  • Using Data Type Icon – Click on the data type icon, and select the correct one, as shown below:
Change Power Query Data Types #1
  • Column Header Right Mouse Click – Simply select right-click on the column header. From the list of options, go to the ‘Change Type’ and select the appropriate data type, as shown below:
Change Power Query Data Types #2
  • Transform Tab – Select the column, and navigate to the ‘Transform’ ribbon tab > ‘Any Column’ group > ‘Data Type: XXX’ option and choose the appropriate type from the list.
Change Power Query Data Types #3
  • Home Tab – Select the column, and navigate to the ‘Home’ tab > ‘Transform’ group > ‘Data Type : XXX’ option and choose the appropriate type from the list.
Change Power Query Data Types #4

Enable or Disable – Automatically Detect Data Types

The automatic detection of data types in power query is by default enabled. To switch or turn on automatic detection of power query data types, simply follow this path – Go to the ‘Power Query Editor’ window and then navigate to File > Options and Settings > Power Query Options as shown below:

File Power Query Options Settings

In the ‘Query Options’ dialog box that appears, choose the second radio button (under ‘Type Detection’ section).

Automatic Data Types and Headers Detection

On enabling it, power query would do two steps by default.

  • Firstly, it would promote the first row of the data as column headers.
  • Secondly, it would detect and change the data types for the column header. The detection of correct data types is totally based on how the first 200 rows of the data are structured.

To disable or turn off automatic data type detection, select the third radio button – ‘Never detect column types and headers for unstructured sources‘ as highlighted below.

Turn Off Automatic Data Type Detection

With this, we have reached the end of this tutorial. Write your feedback on this page in the comment box below.

Leave a Comment