Enough to make you dangerous.

When starting out in Power BI, I struggled to understand the difference between M and DAX and measures vs. calculated columns (if you are in the same boat, this overview might help).
TL;DR: Power Query is what you use in Power BI to load in your dataset and clean and manipulate your data. M is the language used in Power Query. You with me? Power Query and M are amazing and a big part of what separates Power BI from other Business Intelligence tools. However, there can also be a bit of a learning curve and it can keep new users from adopting Power BI before they even get into the good stuff.
In order to help you skip over this common obstacle, I want to share with you an overview of what I have learned so far. This is by no means an exhaustive guide to M or Power Query but it will give you everything you need to know to get through the muck and then move on to the fun stuff.
- Conceptual understanding
- Steps to jump-start your learning
- How to start using
For those of you who want to follow along, I’m going to be using the dataset from Week 43 of Makeover Monday. For anyone wanting to grow in Data Visualization, Makeover Monday is an awesome weekly challenge with freely available datasets.
Conceptual understanding
When trying to build a conceptual understanding, it helped me to think about Power Query and M like macros and VBA in Excel.
In Excel, when creating a macro, you can do so by:
- Using the "Record Macro" functionality by pointing and clicking your way through the process
- Using VBA and writing out the code yourself
Similarly, in Power Query, you can point-and-click your way through whatever data manipulations you are trying to accomplish OR you can write code for the process using the language M. Again, M is a language that is used in Power Query when you are loading and cleaning a dataset.
Sidebar: M is totally separate from DAX. DAX is what you use after using Power Query to do things like creating a measure that gives you the total sales for a given year. Okay, back to business.
When do I use M?
When you are loading a new data source into Power Bi, step #1 should always be going into Power Query. Always. Even if your data is absolutely perfect and you don’t have to do any cleaning or manipulations (must be nice) you should still start in Power Query to make sure that everything is loading as expected.
There are two ways to access Power Query. When you load a new data source, you can click on "transform data" instead of "load" or you can click "Transform data" in your Home ribbon at any time.

Once in this view, you will see a Query on the left for each data source you have added, a preview of the data in the middle, and then the applied steps on the right. Before you have done anything, you will see that Power Query has already done a number of steps for you:

You’ll notice that every time you do something in Power Query (e.g. filter the data, remove columns, etc.) you’ll notice that a step will be automatically added for you. Pretty nifty, but what is really going on here?
Each time you point-and-click to complete a step, Power BI is writing the command in M for you on the back-end. Again, it is like we are recording a macro in Excel and the VBA is being written for us.
To be clear, you should be able to get along just fine without ever using M, most of the time. But, knowing some basic fundamentals of M can be enormously helpful and save you a lot of headaches in the future. Find out exactly how below.
Learning M
As an impatient get ‘er done person, I was not about to learn a whole new language when I can point-and-click my way through things pretty darn quickly.
I could not sit down and write an entire query in M from scratch. And ya know what? I’ll never need to. I have found that by knowing just enough I can do everything that I need quickly and efficiently. I’ll share with you how I got to this point.
Disclaimer: for those who want to dive deep into the nitty-gritty and have an independent command of the language, I assure you that you will not find that here. You are a better person than me and I wish you all the best.
3 steps to learning M:
- Point-and-click (that’s right, you heard me!)
- Annotate the steps
- Make tweaks
When learning M you might as well take advantage of all of the work that is being done for you automatically. So start out by doing some pointing and clicking, just a few steps.
I’m going to filter the data to the Year 2020 and remove the Month column. You will see that two steps have been added to the Applied Steps panel on the right.
Now, I want to take a look under the hood and see the M code that Power BI has been kind enough to create for me. To do that, I’ll click on the "Advanced Editor" from my Home ribbon. Trigger warning: this is going to look gross and overwhelming, but stick with me.

You are going to see something like this:
let
Source = Excel.Workbook(File.Contents("C:UsersjeaglesonDownloadsWeek 43.xlsx"), null, true),
#"Week 43_Sheet" = Source{[Item="Week 43",Kind="Sheet"]}[Data],
#"Promoted Headers" = Table.PromoteHeaders(#"Week 43_Sheet", [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Country", type text}, {"Year", Int64.Type}, {"Month", type text}, {"Exports (USD Millions)", Int64.Type}}),
#"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([Year] = 2020)),
#"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Month"})
in
#"Removed Columns"
What in the ding, dang heck am I looking at? Welcome to M. Yes. It looks gross. But, stay with me, I’m going to show you how to quickly make it much more readable and easier to understand.
As a first step, I encourage you to enable word wrap under the display options. This will make it so you can see all of the text at once and don’t have to scroll left to right.

Something to notice about M is that you will see each step has a # and is in quotes. It also took me a while to realize that the first thing you enter in each step is the name of the previous step. Let me show you what I mean. I am going to change the name of each step starting with #"Promoted Headers." It is also important to note that you only need the #"" if you have spaces in the name of your step. I find it roughly 547 times easier to read if I make the name of each step one word.

Now you would probably want to name each step something more meaningful than Step2 (e.g. ChangeDataTypes) but hopefully, this helps illustrate what I mean about each step referencing the previous step.
Once I click Done in the advanced editor, something interesting happens. We have the same Applied Steps, but they are now reflecting our new names.

Pretty cool, huh?
Now, let’s spend some more time in M and think through what each of the steps is doing. Again, we aren’t trying to memorize any formulas, just trying to understand what is happening in each step in case we need to make changes later.
I add a comment above each step that briefly states what that step is doing. Add a "//" to the beginning of your comment so Power Query knows you aren’t trying to type something in M and to ignore it. The "//" tells the editor that you’re just talking to yourself and not trying to tell it something in M.

You can toggle back and forth between the Advanced Editor and point-and-clicking so you can add a comment to each step as you go. But now, when I click Done again, you will see another cool thing happened. There is now an information icon at each step that I can hover over to see my comment. I can hover over that icon for each step and see what it is doing without having to open the advanced editor again.

This is getting pretty exciting, wouldn’t you say?
Using M
Now that we understand what M is and we are getting comfortable with navigating in and out of the Advanced Editor, we are ready to start using some M to make our lives a lot easier. Again, I am a proponent of starting with pointing and clicking and then making small tweaks in the advanced editor. Here are some examples:
- Updating source file locationLet’s say that I want to update my Power BI file with Week 44 data. I simply open up my advanced editor and in the first Source = step, I change my file path from "C:UsersjeaglesonDownloadsWeek 43.xlsx" to "C:UsersjeaglesonDownloadsWeek 44.xlsx"
-
Handle changes to the source file structureSomething that I, and likely you, run into all the time is when I want to update my Power BI file but changes have been made to my source file. For example, a column name was changed or a column was added or deleted. Power BI really doesn’t like it when this happens. There is not a way to handle this by pointing and clicking, but it is a breeze to adjust in M. Let’s say that my source file now has a "Region" column after the "Country" column. All I need to do is go into my advanced editor and use the same formatting as the other columns to add the Region column: Step2 = Table.TransformColumnTypes(Step1,{{"Country", type text}, {"Region", type text}, {"Year", Int64.Type}, {"Month", type text}, {"Exports (USD Millions)", Int64.Type}}),
Now, I want to be clear, M can do a LOT of really cool stuff. Especially when you want to start creating and reusing functions. But, when you are starting out, it can be detrimental to get too into the weeds of M and it will unnecessarily slow your progress. I encourage you to start off by doing point-and-click in Power Query and then looking at the advanced editor and making small adjustments to get comfortable with M. And then, once you are comfortable, the sky is the limit!
If you are wanting more information on how Power Query and M fit in with DAX and other Power BI concepts, you might find this helpful:
Some other helpful M tips:
- Every query begins with "let"
- Every query ends with "in" and LastStepName
- There is a comma after every step EXCEPT the last step, which does not have a comma at the end
-
I usually think of each step like this: NameOfStep = Function(NameOfPreviousStep, Function stuff),
- If your column contains numbers you are most likely going to use the data type "Int64.Type" – why they couldn’t just make this "number" is beyond me
Ready to put what you’ve learned to the test? Check out this Power BI weekly challenge:
Keep knocking your head against the wall, it gets so much better!

Jenna EaglesonMy background is in Industrial-Organizational Psychology and I have found my home in People Analytics. Data viz is what makes my work come to life. I mostly use Power BI but I occasionally foray into Tableau and other tools. I would love to hear more about your journey! Reach me by commenting here or on Linkedin or Twitter.