Dynamic Data Types in Power Query

If you have dabbled any bit with Power Query, I’m sure you must have come across “Changed Type” as a step applied.

The problem with Changed Type is that both the Column Name and the Data Type applied are hard coded, which means..

• The user will have to change the data type manually each time.
• If the column gets deleted / renamed, the Changed Type step will result in an error.

 

In this post I’ll show you 2 ways to make the Changed Type step dynamic.

 

2 Methods..

• Define Data Types from an Excel Sheet
• Automatically detecting Data Types from the first row of the data.

 

Method 1 – Dynamic Data Types by Declaring Data Types from an Excel Sheet

 

Loading two simple tables in Power Query

The Data – Which needs to have dynamic Data Type applied

Notice that the Changed Type applied has two parts to the formula

1. Table Name – Source (from the previous step)
2. List of List – A List containing sub lists which has column name and data type applied.

To be able to solve this problem we’ll also need to create a list of a list containing column name and data type applied.

 

Next, we also have the DataType Table which has been manually created in excel with column names that match the data and the data type to be applied.

Note that the data type needs to be written exactly like below. We’ll be working ahead with this table to transform it into an actual data type.

 

In DataTypes, I’ll create a new column (Add Column >> Custom Column) with the following code.

Value.Type(
  if [Type] = 'date' then #date(2020,1,1)
  else
  if [Type] = 'number' then 1
  else
  'A'
)

The output of the above formula will be a column that returns type. See the results below

Since I only want to work with Column Names and Data Type (Custom) column created, I’ll remove the Type column.

Next, transpose (Transform Tab >> Transpose) the data and so that each column and it’s text are placed into individual columns.

Now, to be able to create a List of a List..

1. I need to convert these columns into a list and
2. Pack the individual lists into a list.

I’ll create a new step (fx icon next to the formula bar) and use the following code.

= Table.ToColumns ( #'Transposed Table' )

Finally applying this Headers query which is not a List of a List to the Changed Type Step in my Data query.

• Now based on data type given by user the data type will change dynamically.
• This query will throw an error unless a correct data type corresponding to the column name are entered.

Extending this let's say, I want to change the data type based on first row of the data.

 

Method 2 – Dynamic Data Type in Power Query Based on First Row of Data

 

Again, using the same approach, we need a list of a list i.e. a list containing sub lists with column name and data type. The only difference is that the data type will declared using the first row of data rather than spelling it out as an Excel Input.

I used the following M code in a blank query.

let
  Source             = Excel.CurrentWorkbook(){[Name = 'Table1']}[Content],
  Custom1            = Table.FirstN(Source, 1),
  #'Demoted Headers' = Table.DemoteHeaders(Custom1),
  Custom2            = Table.Transpose(#'Demoted Headers'),
  #'Added Custom'    = Table.AddColumn(Custom2, 'Custom', each if [Column2] = null then Any.Type else Value.Type([Column2])),
  #'Removed Columns' = Table.RemoveColumns(#'Added Custom', {'Column2'}),
  Custom3            = Table.Transpose(#'Removed Columns'),
  Custom4            = Table.ToColumns(Custom3)
in
  Custom4

This creates the same list of a list structure with 2 parts – Column Name and Data Type Applied

Quick explanation here..

1. I keep the first row (custom1) of data and it’s headers in the first row (demote headers).
2. After transposing the table, I create the data types in a new column.
3. Keep the relevant columns, transpose again and convert it into a a list of a list.

 

I repeat the same process of editing my Changed Type step by adding DataType query in my formula.

Caveat – The data in a column is should be consistent. A column with the first row as a number and the rest values as text will get converted to a number type and will result in an error for all text values in that column.

I originally read about the idea of detecting data types from the first row from Imke’s Blog. If you still reading this, you should check it out, it’s pretty awesome.

 

 

More Power Query Tricks

1. Nested Let Statement in Power Query
2. Expand All Columns Dynamically
3. Dynamic File Path in Power Query
4. Unstack Rows in Columns
5. Change Dates from mm-dd to dd-mm format or vice versa
6. Split by Variable Columns in Power Query