One of the fastest methods I know to create tables in Power Query/ M is the function
Table.FromColumns(), which takes a list of lists and creates a table from them. This way you can easily create a calendar table for example. In this article I will show you how you can use the metadata record of list items to flexibly assign both column names and datatypes to the created table.
Take a look at the following M script. At the beginning I define 4 lists: A list of…
- day names,
- month names and
- month numbers.
ListOfColumns is basically a compilation of the lists defined above, which are to be included in the final table. Here I can flexibly determine 1) if they should be included and 2) in which order they should be placed.
The final Step
GetTable creates a table from the lists defined in
The problem with this solution is that the final table has neither data types nor column names.
A possible solution is to define in the
ListOfColumns list not only the lists to be included in the table, but also the column name that the list is to receive in the table and the later data type. I define this via the metadata record (To be seen in code lines 16 to 23):
In line 28 I use the
Value.Metadata() function to retrieve the ColName information from the metadata record of each list item and get a list of column names.
In line 31 I not only create the table, but also use the second argument of the Table.FromColumn function to define the correct column names.
From row 34 on I assign the correct data types to the columns, which I had stored in the metadata record of the list items. The result is a table that can be created flexibly and that shows both column name and correct data types:
Cheers from Hamburg, Germany
Lars ist Berater, Entwickler und Trainer für Microsoft Power BI. Er ist zertifizierter Power BI-Experte und Microsoft Trainer. Für sein Engagement in der internationalen Community wurde Lars seit 2017 jährlich durch Microsoft der MVP-Award verliehen. Lies hier mehr…