Let’s put Power Query and the M Language under the scope
Power Query is a breakthrough tool for self-service Business Intelligence and it’s the new best tool released by Microsoft within the span of 5 years. I’ve already posted about what Power Query is and what it represent to us but, How can I get started with Power Query and the M Language?
Well, this post will try to tackle that question and give you a sense of how to start using Power Query and understand its language.
I’ve divided this into 2 main sections that I truly believe should be first primary focus of our journey when we try to learn Power Query and the M language. These are:
The Power Query & M mechanism or Workflow
The actual M Language itself
Let’s get started with the basics!
The Workflow – step by step basis
(Image shows the Workflow of a Power Query query. It works on a per step basis based on the result of some previous step *most of the time*)
These are some of the reasons why people love this workflow with Power Query:
It works based on steps, kind of like how a macro-recorder works and it invites you to work on an ordered way.
It keeps you at bay in order to apply the best practices of data transformation which is awesome!
its easy to read and easier to debug
The image above explains that we have a step, and that step has something written in it. The content of that step could be called a script, specifically an M Script of something that has to happen. The Power Query Engine applies such script and the result of that will be shown on the left side your screen which could be called the Query-preview-view. That result can be later used for more transformations, but let’s see an example and how to understand it.
In the image above we see the query that was created based on my previous blog post. We see that the first step is called Source and what source does can be viewed on the formula bar that starts with “=Folder.Files(…” and from that step we’ll be adding a new step called “Get the files” and after that one, and based on that last one, we create a new step called “Removed Other Columns”
Just to summarize, here’s how to dissect just one step:
- The Name of my Step = “Source”
- What my step does = “=Folder.Files(…”
- The result of such step = all the columns shown on the left side of the screen starting from [Content] all the way to [Folder Path]
Something to take in consideration is that any of the inputs or results can be either a record, list, table or a function but for now we are just interested in knowing the workflow and not all the details.
This is the cycle or workflow for Power Query. You work pretty much like using the macro recorder recording everything on a step-by-step basis and when you finish recording such “macro” you’ll just go to “Close & Load” so you can save the query and load it to either Excel or your Data Model.
“Every new beginning comes from some other beginning’s end” plays in my mind every time I think about the Power Query workflow.
How to understand the language
Let’s dissect again that query from my last blog post and try to analyze the patterns of that M code:
Here you have a side by side image of the Advanced Editor, the M code in its raw form, and behind it the Power Query Query Editor with the Steps list to the right. I went ahead and draw a line with each corresponding step in order for me to show you guys where everything’s at.
So that’s quite easy, we know where each step is stored and where its at but you’ll notice that the names are not exactly the same. For example, the second step is called Get the files but in the Advanced editor you see the name shows as #”Get the files”and the reason behind this is because M can not handle spaces between names or conflicts with other names (step names or even function names) so that’s why it adds the # as a prefix and puts everything inside quotes. You won’t notice this on the UI as it’ll show you the name that you’ve given it but, under the hood you’ll notice how it translates to the M language.
I’ve also highlighted 2 words:
- let – is the syntax used to Start the Query.
- in – is the syntax to determine that you finished the transformation process and subsequently you’ll see the name of the Step that should be treated as the output of such query. In our case “Expanded” is the output of our PQ query.
M Language – Learning resources
For official learning resources from Microsoft please visit the following link where you can learn more about the formulas and functions within Power Query.
Also, here’s another easy trick. You probably already love this from Excel and Power Pivot:
and if you want the same experience, or pretty similar to it, you can just type =#shared in the Power Query formula bar and get a full list of all the functions available in your version of Power Query:
and if you want the full help article of any function all you have to do is just type the name of the function like this:
Stay tuned for more posts like this in the coming weeks!
Be sure to subscribe to our mailing list in order to receive our posts in your email or perhaps you’ll like to subscribe to the RSS feed.