Chris Webb's BI Blog: The M Behind The New Power Query Data Types In Excel
source link: https://blog.crossjoin.co.uk/2020/09/06/the-m-behind-the-new-power-query-data-types-in-excel/
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
The M Behind The New Power Query Data Types In Excel
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:
letSource = #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.
Like this:
Recommend
About Joyk
Aggregate valuable and interesting links.
Joyk means Joy of geeK