Fast table construction with Table.FromColumns and lists with metadata records

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…

• dates,
• day names,
• month names and
• month numbers.

The 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 ListOfColumns.

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