Creating static tables that can be edited via its M code

In Power BI, you can use the “Enter Data” button to manually create a table. Looking at the Power Query source code you will see a long text string (nonsensical to the human eye) that is decompressed via a function into a table.

In Power BI you can easily adjust those values by clicking on the cog symbol of the first step. However M-Code is getting far more ubiquitous and this option may not always be available. So you may want raw M-Code which you can adjust easily.

Hence I have created a function that will generate the source code for the table using the “#table” function instead. This means you can directly edit the data, by editing the code.

It’s loosely based on an idea and function by Chris Webb:
https://blog.crossjoin.co.uk/2018/10/03/function-m-table-type/

The Instructions

  1. Create a static table (i.e. Click “Enter Data”)
  2. Copy and paste the function from here:
    https://github.com/camwally/Power-Query/blob/master/CreateTable2
  3. Create a blank query
  4. Click on the “Advanced Editor” and paste the function in and click “Done”. It’s now a function and you can select it’s parameter (InputTable) with the GUI. It’s probably best that you rename the function to something like “CreateTable” so that you can remember it for future use, buts that’s up to you.
  5. After selecting it’s parameter (A table which you have already created) click Invoke and a new query should be generated called “Invoked Function”. The output of that function will be a text value that you can copy and paste back into the original table via the advanced editor. So the table will look exactly the same, but the underlying M-Code will be code that you can directly change.

The Result

You go from something like this:

To this:

As you can see, the function also lines up the values in the table to make everything much more readable.

8 Comments

Leave a Reply to Mike Allen Cancel reply

Your email address will not be published. Required fields are marked *

Follow this blog!

Get the latest posts delivered to your mailbox: