When starting out in Power BI, I struggled to understand the difference between M and DAX and measures vs. calculated columns. M is the language used in Power Query. Power Query and M are amazing and a big part of what separates Power BI from other Business Intelligence tools.
When starting out in Power BI, I struggled to understand the difference between M and DAX and measures vs. calculated columns
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.
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.
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:
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 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.
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.
In analytics, Tableau is the leading visualisation tool. Its rich analytical features and attention to data details are the reason behind its popularity. Power BI, on the other hand, is preferred by professionals who are more comfortable with...
JIRA Software provides bug tracking, issue tracking, and project management capabilities for teams and organizations. The JIRA content pack for Power BI helps you quickly import JIRA data so you can get an instant dashboard to analyze workloads...
In a world where the sheer amount of data is often overwhelming, the ability to interrogate and organise data to make meaningful business decisions is more important than ever. Microsoft have created Power BI to enable every day users to use the...
This article provides a stepwise guide on how to work with Power BI data visualization and apply various types of formatting.
Every month, we bring you news, tips, and expert opinions on Power BI? Do you want to tap into the power of Power BI? Ask the Power BI experts at ArcherPoint. ![This is image...