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
- Create a static table (i.e. Click “Enter Data”)
- Copy and paste the function from here:
https://github.com/camwally/Power-Query/blob/master/CreateTable2 - Create a blank query
- 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.
- 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.
Non-aggregate pivot with multiple rows in Power Query
Retaining column types in Power Query
Sorting by locale with comparers in multiple columns
About Author
The function is a great idea.
Did you know that you can click on the Source gear icon which will allow you to view and edit the original table data?
Hi Gilbert, just got told via twitter. Can you think of any use for this function? Will probably delete blog post as it more likely to waste peoples time than help people
You should never delete a blog post in my opinion. The code you created is maybe not that useful for the case you described, but could be very relevant as a reference for future challenges.
According to me, you should keep it, but simply update the blog with a few sentences somewhere on the top, discussing your new findings 🙂
According to be, you should keep it, but just include a few sentences on top with your new findings 🙂
Thanks, have updated it
Hi there,
I would suggest to leave it up because it might inspire other people or have them see the way your function works?
Hi All, I like the way the function works and it may be useful to access tables where you don’t have access to the source, in fact, I might have a go at that 🙂
This function is just great!
I tend to use static tables a lot within functions (eg for translations).
Unfortunately, the “Enter data” feature is very much limited.
A few thousand rows (5000 already) and you cannot edit the data anymore because the size limit (obviously less) is exceeded.
While your code is still working …
This is incredibly useful, it will grab any external table and turn in into “M” that can be edited locally/ I’m going to use this, well done Cam!