Adding a Static Table with Types in Power Query With M Language

SQLBI’s guys recently published the Youtube video Create Static Tables in DAX Using the DATATABLE Function, where they show how to create a static table in Power BI.
At first using Power Query Enter data interface and then using the DAX language.
They also show that the M code generated by Enter data contains the table definition as an unreadable (by humans) encoded string, and that the only way to change it is through its specific editor.

Actually, it’s possible to create a static table using M readable code, using the syntax explained in this Chris Webb’s BI Blog post Creating Tables In Power BI/Power Query M Code Using #table(), where one of the proposed syntaxes also allows to specify the data types.

The M code to create the Segments table is the following

#table(
    type table
        [
            #"Price Range"=text, 
            #"Min Price"=number,
            #"Max Price"=number
        ], 
    {
        {"LOW", 0, 100},
        {"MEDIUM", 100, 1000},
        {"HIGH", 1000, 999999}
    }
)

This code uses the #table() function, specifying the type as the first parameter and the data as the second one.

To enter this code in Power Query first create a Blank Query

Then open the Advanced editor

And finally write the code in the Advanced editor window

selecting Done closes the Advanced editor window and shows the generated table

the #table() M function doesn’t have the same limitation of the corresponding DAX function, that only accept literals. With #table() variables are allowed.

Of course, defining static tables using M language has the disadvantage that a change triggers a refresh of the dataset.

The sample file can be downloaded from my GitHub