Power BI: Understanding M

Power BI: Understanding M

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.

  1. Conceptual understanding
  2. Steps to jump-start your learning
  3. 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:

  1. Using the “Record Macro” functionality by pointing and clicking your way through the process
  2. 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.

Transform data icon on the home ribbon

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:

Zoomed in view of the Applied Steps pane on the right in Power Query.

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:

  1. Point-and-click (that’s right, you heard me!)
  2. Annotate the steps
  3. Make tweaks

power-bi data-visualization developer

Bootstrap 5 Complete Course with Examples

Bootstrap 5 Tutorial - Bootstrap 5 Crash Course for Beginners

Nest.JS Tutorial for Beginners

Hello Vue 3: A First Look at Vue 3 and the Composition API

Building a simple Applications with Vue 3

Deno Crash Course: Explore Deno and Create a full REST API with Deno

How to Build a Real-time Chat App with Deno and WebSockets

Convert HTML to Markdown Online

HTML entity encoder decoder Online

Tableau vs Power BI: Comparing the Data Visualization Tools

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...

Explore your JIRA Data with Power BI

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...

Managing your Data with Microsoft’s Power BI

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...

Formatting Data in Power BI Desktop Visualizations

This article provides a stepwise guide on how to work with Power BI data visualization and apply various types of formatting.

Power BI In Brief – 2020

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...