How to handle M-code samples

If you use Power Query or the query editor in Power BI every step you take will be recorded and translated to M-code. You can share this code and it will run in the new environment – so no need to actually exchange a file.

1 Check out what the M-code does

In order to make this code-sharing easy for beginners, I’m sharing my code in a way that it will run instantly with the test-data provided within the code. So you just copy my code examples into the advanced editor like this (example given for Power BI):

Home -> External Data -> Get Data -> Blank Query -> Home -> Query -> Advanced Editor.

There you replace all the content in the window: Check existing content, paste and click Done.

That’s all, now check out how it works: On the Home-tab check Close -> Close & Apply. This will load the data to the data model and you can build your reports with it.

2 Check out how the M-code does it

If you want to see how the M-code did all these transformations, go back to the editor-window (Home -> External Data -> Edit Queries). You will see the result in the preview window and to the right in the Query Settings you will see all steps performed under “Applied Steps”. So if you want to know what happened in the query, just click on each step and see it’s results in the preview. You should also be able to see the formula in M-code in the Formula Bar above. It it’s not shown, you have to activate it: View -> Show -> Formula Bar.

3 Make this M-code work for you

In order to make this code work for your data, you have to divert the sources of my code to your queries. You find these as the first steps, starting with a “q”.

Check each of them if the column names fit yours (otherwise they cannot be referenced by the code). If not, you can either edit the code or (if you are a beginner) just rename the columns in your queries (check column -> rightclick mouse -> Rename).

Then rename your query to the name of the step in my query (click on your query -> Query Settings (right) -> Properties: Adjust “Name”).

Now you “deactivate” the sources in my query: Home -> Advanced Editor: Put “//” in front of all codelines that are going to be replaced by yours, then click “Done”:

Et voilà: Query now works on your data.

In case it doesn’t – but showing errors, just enter the Advanced editor again, remove the “//” and compare your queries with the tables in my “q”-steps: Are the column names the same, have you renamed your queries correctly?

You can download this file in order to see how it should work: [Download not found]

New to Power Query and M? Read this great intro.

Enjoy & stay queryious 🙂

PS: If you want to create code with the data in it, check out this article showing how to automatically create the data-code from existing tables in your model.