Retaining column types in Power Query

Column types in Power Query are often lost when applying operations (or steps) in Power Query i.e. The column types will change from type number to type any.  Most people will just hard code those values again, but below are some code snippets and functions that you can use to dynamically get your column types back.

Problem Scenario #1 – You wish to extract the column types from a previous step in the query.

This ones easy, but it only applies if the previous step has the same column names.

Value.ReplaceType(PreviousStep, Value.Type(APreviousStepThatHasTheCorrectTypes))

If the table has gained or lost columns, you can use this function instead. It will take types from a previous step and apply them where column names are the same.

(tblToAlter as table, tblGet as table) =>
let
    typetblGet = Value.Type(tblGet)
   ,NameCols = List.Intersect({Table.ColumnNames(tblGet) 
                              ,Table.ColumnNames(tblToAlter)})
   ,NameFuncTypes = List.Transform(NameCols, (x) => { x
                                                     ,each {_}{0}
                                                     ,Type.TableColumn(typetblGet, x) })
   ,Transform = Table.TransformColumns(tblToAlter, NameFuncTypes)
in
    Transform

Problem Scenario #2 – When you expand a column containing tables (with each table having the same structure), all the types in the tables being expanded are “lost” i.e. converted to “type any”.

If the column names stay the same on expansion we could simply use the above code. However there is another, better way.

But firstly  – what is the problem?

The problem occurs because the column type of the column that is being expanded is a generic table-type i.e. “type table”.  However the values that the column contains will be a specific table-type (i.e. contains sub-types), so for example: “type table [Name=text, Quantity=number]”.
When we expand it, the column is expanded based on the type of column, not the type of the values.

Marcel Beugelsdijk, however came up with a method that takes the type from the first value and transplants it onto the column so the column can be subsequently expanded with the types intact.

Marcels method, although simple is not that obvious. The obvious thing to do is to use Value.Type to take the type from a value and then use Table.TransformColumnTypes function to replace the type of the column. However, Table.TransformColumnTypes cannot convert a column to a structured type such as type table.

So we actually use the Table.TransformColumn function.
Looking at the MSDN Power Query M function reference and #shared function reference, the 2nd argument of Table.TranformColumns is “DefaultTransformation as nullable function”. Looking at the examples given in the references you will see that that this “DefaultTransformation” argument is a list containing “sub-arguments”, however only 2 “sub-arguments” are ever shown. There is actually a 3rd sub-argument which we can use to set our specific type onto our column.
The “sub-arguments” contained within the list are as follows:

{ColumnName as text, TransformationFunction as function, TableType as type}

So the function we can use is

(PreviousStep as table,ColName as text) =>
Table.TransformColumns(PreviousStep, {ColName, each {_}{0}, Value.Type(Table.Column(PreviousStep,ColName){0})})

The function is based on code created by Marcel Beugelsdijk at this TechNet post. The post gives some extra background to the above function and also to some of the confusion surrounding types in Power Query.

Update – based on Chris’s comment.

There are multiple reasons why a column of tables (also known as nested tables) can come about. The nested tables are a result of a function. If that function is:

Table.NestedJoin: then the column type is the same as the values type so we do not need to do anything.

Table.Group and Table.ReplaceValue: we can use the custom function above

Table.AddColumn: We can use the custom function above, but but there is a quicker way.  Chris Webb wrote a post about how to do this. Simply put, you can use the 4th argument of Table.AddColumn to define the type of the new column. Making it dynamic is a little trickier but the comments at the bottom of Chris’s post shows you some good ways of doing this.

To summarise those comments:

Marcels solution would be take:

Table.AddColumn(Source, “AddAndMult”, each AddAndMult([x], [y]), type table [Operation=text, Result=number] )

and replace it with:

Table.AddColumn(Source, “AddAndMult”, each AddAndMult([x], [y]), Value.Type(AddAndMult(0,0)))

as “type table [Operation=text, Result=number]” had already been hard coded into the AddAndMult function. This is a very quick way, but if the function was changed and it no longer took those dummy inputs there could be an error. (I might be being pedantic here).

Chris’s solution would be to have that type in a separate query or expression and have that referenced by both his AddAndMult function and the Table.AddColumn function. If this type is being referenced by even more queries or functions then this way is a clear winner.

Problem Scenario #3 – I want a query to automatically detect the appropriate column types.

The first method I have across can be found at the BI Accountant blog by Imke Feldmann which looks at the value types of the first row.
This can be found here:

http://www.thebiccountant.com/2017/08/10/table-transform-column-types-to-first-rows-types/

There is a newer second method created by Colin Banfeild which looks at the first 200 rows of a table.
You will need to search for “Table_ConvertColumnTypes” in the forum post:

Homogeneous-list-types-in-m

to obtain the function.
It’s quite an interesting post, very useful if you want to thoroughly understand how Power Query deals with types.

This article wouldn’t be complete without mentioning:

Data Types, Data Conversion and Ascribed Data Types in Power Query and Power BI

For not a only a general overview of types, but also the difference between Data Type Conversion and Ascribing Data Types.

To my readers:

If you think there is anything else that needs mentioning please comment below and i’ll update the blog post.

2 Comments

Add a Comment

Your email address will not be published. Required fields are marked *

Follow this blog!

Get the latest posts delivered to your mailbox: