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).
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]} )
The M code I used to define the data types was:
= type table[Name = text, Employee_Number = number]
I created another step to assign this type to my table.
The M code I used was:
= Value.ReplaceType(Source, Custom1)
Using #table, I could refine the table creation to one step:
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.
The M code I used was:
= Table.FromColumns({{"Mary", "Paul", "John", "Newbie"},{1,18,15,150}},type table [Name = text, Employee_Number = number])
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.
There is a deliberate mistake, but when I upload my step, everything looks fine:
I ‘Close & Load To’ so that I can load my query.
I decide to ‘Add this data to the Data Model’.
I can view the error in Excel, and correct it using the dropdown.
But that does not solve the issue with the data model.
I have four rows loaded and one error. I try clicking on ‘1 error’ to see what has happened.
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!
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!
