Please note javascript is required for full website functionality.


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!