Power Query: The Wrong Type
4 December 2019
Welcome to our Power Query blog. This week, I look at a problem when assigning data types to columns.
Last time, I looked at several methods for setting the data type when creating a table. I used Table.FromRecords, Table.FromColumns and #table.
To recap, the Table.FromRecords method had three steps (which I could combine to two).
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2019/power-query/157/image1.png/e774d10cbbb9450fc45efbe51abdf434.jpg)
The M code I used was:
= Table.FromRecords({ [Name = "Mary", Employee_Number = 1], [Name = "Paul", Employee_Number = 18], [Name = "John", Employee_Number = 18], [Name = "Newbie", Employee_Number = 150]} )
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2019/power-query/157/image2.png/f32e5a15e2cf9c3e4d2d058458ce054d.jpg)
The M code I used to define the data types was:
= type table[Name = text, Employee_Number = number]
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2019/power-query/157/image3.png/f1140ff857fc3b6f5f97a6a24f4a6fc7.jpg)
I created another step to assign this type to my table.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2019/power-query/157/image4.png/72aa864d2854c6fefb1083fba0ab5792.jpg)
The M code I used was:
= Value.ReplaceType(Source, Custom1)
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2019/power-query/157/image5.png/36776d1da4d05b45bb5a5d09375f407c.jpg)
Using #table, I could refine the table creation to one step:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2019/power-query/157/image6.png/23912d3b1671861e02bebcd5183f1607.jpg)
The M code I used was:
= #table(type table[Name = text, Employee_Num = number],{{"Mary",1},{"Paul",18},{"John",15},{"Newbie",150}})
I also used Table.FromColumns, which accepts the table types as the second parameter.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2019/power-query/157/image7.png/6f49c288a0d88a66b427eaf4ece923d6.jpg)
The M code I used was:
= 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/157/image8.png/b9ee28d90e6b5bc92ea4aeafdad51628.jpg)
There are some disadvantages to declaring data types in this way. For example, in the next screen, I have entered a step to create a new table.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2019/power-query/157/image9.png/0485ccbc83bdeec1d741bad442a1ea5f.jpg)
There is a deliberate mistake, but when I upload my step, everything looks fine:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2019/power-query/157/image10.png/daf8c4f0259ce428269c0d3d4badd32b.jpg)
I ‘Close & Load To’ so that I can load my query.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2019/power-query/157/image11.png/22c6daeb82d7d69ac88f878227e04b28.jpg)
I decide to ‘Add this data to the Data Model’.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2019/power-query/157/image12.png/a1537847463e660a31158c8032525438.jpg)
I can view the error in Excel, and correct it using the dropdown.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2019/power-query/157/image13.png/917da985be13220165c8d2823e95344f.jpg)
But that does not solve the issue with the data model.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2019/power-query/157/image14.png/8c3be7af9f73d031acae69ed85a2e148.jpg)
I have four rows loaded and one error. I try clicking on ‘1 error’ to see what has happened.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2019/power-query/157/image15.png/e63c0a4c21afc9afb438aacc09a59317.jpg)
Not very useful!
In my simple example, I know that one row has failed to load because I put “15” instead of 15 as John’s employee number. However, in a long list of data, this would be hard to spot. The error doesn’t appear because the query doesn’t have an error flagged. This is the problem when I manually define the column types in the table.
When I go to look at the Data Model (from the ‘Power Pivot’ tab) there is no data loaded!
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2019/power-query/157/image16.png/d082e3477129350b8a2a589156028e63.jpg)
If I am defining datatypes for columns manually, I need to bear in mind that I am bypassing the checks that Power Query uses. This functionality should be used with caution.
Come back next time for more ways to use Power Query!