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 can actually use the Table.TransformColumns function to do so.
I give a function below that will allow you to do this very easily.

The function that you can use

(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.

Below is some information about how Table.TransformColumns works

Looking at the MSDN Power Query M function reference you will see the 2nd argument of Table.TranformColumns is “transformOperations as list”. This a list that contains lists of arguments. These lists of arguments apply to different columns within the table. For example you might write

Table.TransformColumns(PreviousStep,{{"QuantityColumn", each _ *2},{"ValueCol", each _*4}})

You can also apply a column type to the column using a 3rd argument in the list (this is not covered in the function reference). This works differently to Table.TransformColumnTypes as it does not try and convert the values in that column to the type of the column. E.g

Table.TransformColumns(PreviousStep,{{"ValueCol", each "Value = "&Text.From(_), type text}})

will set the types of the columns without transforming the values again.

So if you just wanted to change the column type without converting the values in that column, you might think you need to write:
Table.TransformColumns(PreviousStep,{{“Col1”, each _ , type text}})
However the column type will not change (this is might be a bug) so instead you need to write:
Table.TransformColumns(PreviousStep,{{“Col1”, each {_}{0} , type text}})

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 generally the 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 Banfield 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.