Power Query: Not Your Type
27 November 2019
Welcome to our Power Query blog. This week, I look at assigning types to columns.
Setting the data types when transforming data in Power Query is important. Data type specific functions, such as M date functions, will not work correctly if the data does not have the expected data type.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2019/power-query/156/image1.png/e774d10cbbb9450fc45efbe51abdf434.jpg)
When I extract data into Power Query, algorithms are used to determine the most appropriate data type. On the previous screen, this step is called ‘Changed Type’. The M code used is
= Table.TransformColumnTypes(Source,{{"Name ", type text}, {"Employee Number", Int64.Type}})
This sets my Name column to data type text and my Employee Number column to data type whole number.
I can change the data type from the ‘Home’ tab or ‘Transform’ tab:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2019/power-query/156/image2.png/f32e5a15e2cf9c3e4d2d058458ce054d.jpg)
If I decide to change the type, I have the option of changing the ‘Changed Type’ step or by creating a new step, viz.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2019/power-query/156/image3.png/f1140ff857fc3b6f5f97a6a24f4a6fc7.jpg)
If I create a new step, I can see the M code that Power Query uses to change the type.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2019/power-query/156/image4.png/72aa864d2854c6fefb1083fba0ab5792.jpg)
The M code is
= Table.TransformColumnTypes(#"Changed Type",{{"Employee Number", type text}})
I can also use M code to set the data types myself. I start by creating the table.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2019/power-query/156/image5.png/36776d1da4d05b45bb5a5d09375f407c.jpg)
The M code I have used is:
= Table.FromRecords({ [Name = "Mary", Employee_Number = 1], [Name = "Paul", Employee_Number = 18], [Name = "John", Employee_Number = 18], [Name = "Newbie", Employee_Number = 150]} )
I have not defined data types, so both columns have type ‘Any’. I can add a step to change this.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2019/power-query/156/image6.png/23912d3b1671861e02bebcd5183f1607.jpg)
The M code I have used is
= type table[Name = text, Employee_Number = number]
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2019/power-query/156/image7.png/6f49c288a0d88a66b427eaf4ece923d6.jpg)
This has created a table type. I need another step to assign this type to my table:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2019/power-query/156/image8.png/b9ee28d90e6b5bc92ea4aeafdad51628.jpg)
The M code I have used is
= Value.ReplaceType(Source, Custom1)
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2019/power-query/156/image9.png/0485ccbc83bdeec1d741bad442a1ea5f.jpg)
My Name column is now text and the Employee_Number column is a number.
I have achieved my goal in three steps, but I can do it in two. For that, I need to be able to specify the types when creating my table.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2019/power-query/156/image10.png/daf8c4f0259ce428269c0d3d4badd32b.jpg)
I have entered a step to create the types for my table (before I specify the data). The M code is
= type table[Name = text, Employee_Number = number]
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2019/power-query/156/image11.png/22c6daeb82d7d69ac88f878227e04b28.jpg)
Next, I need to provide the data to populate the table.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2019/power-query/156/image12.png/a1537847463e660a31158c8032525438.jpg)
The M code I have used is:
= #table(Source,{{"Mary",1},{"Paul",18},{"John",15},{"Newbie",150}})
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2019/power-query/156/image13.png/917da985be13220165c8d2823e95344f.jpg)
My table has the correct column data types. I can combine these steps and achieve this in one more complicated step.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2019/power-query/156/image14.png/8c3be7af9f73d031acae69ed85a2e148.jpg)
The M code I have used is:
= #table(type table[Name = text, Employee_Num = number],{{"Mary",1},{"Paul",18},{"John",15},{"Newbie",150}})
I can also use Table.FromColumns, which accepts the table types as the second parameter.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2019/power-query/156/image15.png/e63c0a4c21afc9afb438aacc09a59317.jpg)
The M code I have used is:
= Table.FromColumns({{"Mary", "Paul", "John", "Newbie"},{1,18,15,150}},type table [Name = text, Employee_Number = number])
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2019/power-query/156/image16.png/d082e3477129350b8a2a589156028e63.jpg)
My columns have the correct data type.
There are some disadvantages to declaring data types in this way which I will look at next time.
Come back next time for more ways to use Power Query!