The big news this week – at least for me – was the release of the new Power Query data types to the Excel insiders channel. You can read all about it here:
https://insider.office.com/en-us/blog/power-query-data-types-in-excel
They’re the latest manifestation of Excel linked data types; cool things are also happening with them and Power BI featured tables too.
The announcement blog post explains pretty much everything you can do right now with Power Query data types but I was curious about the M code that is used to create them. Here’s an example query that takes this source table:
…and creates this Power Query data type:
let
Source = #table(
type table[
Fruit = text,
Colour = text,
Sales = number
],
{
{"Apples", "Green", 10},
{"Lemons", "Yellow", 20},
{"Strawberries", "Red", 30}
}
),
#"Created data type"
= Table.CombineColumnsToRecord(
Source,
"Data type",
{"Fruit", "Colour", "Sales"},
[DisplayNameColumn = "Fruit", TypeName
= "Excel.DataType"]
)
in
#"Created data type"
The magic happens with the #”Created data type” step and the Table.CombineColumnsToRecord function; so Power Query data types are basically columns that contain record values with (I guess, I need to check) some extra metadata.
Hi Chris! Thanks for this article! Can I ask you about the performance and final size? Is this method more efficient both in speed and size? Have a good day!
Great Sharing Chris. Any way we can achieve json data type
> The magic happens with the #”Created data type” step and the Table.CombineColumnsToRecord function; so Power Query data types are basically columns that contain record values with (I guess, I need to check) some extra metadata.
If anyone is interested, the metadata is along these lines:
#table(
type table [
ID = number,
Combined = (type [A=any, B=any] meta [Documentation.TypeName=”Excel.DataType”])
],
{
{ 1, [A = “hi”, B = “bye”] meta [Documentation.DisplayName = “hello world”]}
}
)
This was a super useful hint (as usual from Ben). If you ever need to create a custom data-type column by hand use something like the following (combining just one column “someField” into the data type)
Table.AddColumn(
someTbl,
“Custom”,
each [someField= “a cell value”] meta [Documentation.DisplayName = “a cell value”],
type [someField = text] meta [Documentation.TypeName=”Excel.DataType”]
)
Note the meta info is on each individual record in the column: hence if you want it to display the value you need to set this row-wise.