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.
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:
If I decide to change the type, I have the option of changing the ‘Changed Type’ step or by creating a new step, viz.
If I create a new step, I can see the M code that Power Query uses to change the type.
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.
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.
The M code I have used is
= type table[Name = text, Employee_Number = number]
This has created a table type. I need another step to assign this type to my table:
The M code I have used is
= Value.ReplaceType(Source, Custom1)
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.
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]
Next, I need to provide the data to populate the table.
The M code I have used is:
= #table(Source,{{"Mary",1},{"Paul",18},{"John",15},{"Newbie",150}})
My table has the correct column data types. I can combine these steps and achieve this in one more complicated step.
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.
The M code I have used is:
= Table.FromColumns({{"Mary", "Paul", "John", "Newbie"},{1,18,15,150}},type table [Name = text, Employee_Number = number])
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!
