Power BI Tip: Discovering Power Query ‘M’ functions

Power BI Tip: Discovering Power Query ‘M’ functions

A major part of the ‘power’ which Power BI offers, can be attributed to a powerful self-service ETL tool in Power BI, called Power Query or the otherwise known as the ‘Edit Query’ functionality in Power BI desktop. With a simple GUI, it can consume data from almost all data sources, offers options to clean and mashup data, and with a few mouse clicks, and no knowledge about programming required, your data can be transformed as required.

So how does Power Query work? In the background, as query language, it uses a programming language informally known as “M”. When a user clicks any button in the Power Query GUI, we can actually see the resulting M code generated. Let’s take a look at how this can be done:

Steps: Power BI Desktop > Home tab > Edit Queries > select one of your queries > View tab > Advance Editor > a new popup window will open with the M-code of the selected query.

2016-05-10_02h14_13

Power query has an easy to use and powerful GUI tool, however, what if we have a custom requirement in our data preparation? In my previous two posts, Sort by Month Name and Calculating Year to Date Values, I showed various ways how to build a calendar table in Power BI Desktop using DAX code. I also hinted on my preferred way to do this, via Power Query. Let’s take a look at how we can find out more about, and use, the Power Query M language.

For example, if we only have a date field in our dataset and want an additional field which displays the short month name (Jan, Feb…Dec) derived from the date column. As there’s no direct button to click for achieving this, in this scenario, we will add a custom column and use some M Code.

Before we do this, let’s take a look at some of the M functions. Create a blank query and type =#shared in the formula bar. This will provide a list of all the M code functions available.

Steps: open query editor > get data > blank query > type =#shared in formula bar > hit enter

2016-05-10_02h56_36

As per out scenario, let’s take a look at how to find the required function, deriving the short month name.
Steps: after typing “#Shared” and getting list of functions, convert this list to table by clicking the “Into Table” icon as depicted above, and we’re left with a two column table:

2016-05-10_03h09_32

the ‘Name’ column contains the function names. Filter the ‘Name’ column by typing “date” and selecting “Date.ToText” as shown below:

2016-05-10_03h11_31

After this, click on ‘Function’ from the ‘Value’ column this will open the new window with the function detail and how to use it.

2016-05-10_03h19_50

Furthermore, if you want to see the help article of any function, you can simply type the name of the function like this:

=Date.ToText

As you can see, the associated information is displayed.

Date.ToText

Now that we have an idea of which function to use, coming back to our objective, getting short month name, open your date table query and click add custom column as per the steps below:
Step: open your table query > select date column (data type – date only)  > add column tab > add custom column > type column name and write custom column M-code
=Date.ToText(

[datecolumn],”MMM”) 
– MMM for short month
– MMMM for full month name

– ddd for short weekday name

– dddd for full weekday name

2016-05-10_03h31_48

There it is, we’ve got a new column with the short month name…

2016-05-10_03h39_21

I previously mention that it is currently quicker to build a calendar table in Power Query than it is using DAX, however, doing each additional column this way does not seem quicker. The beauty here is, once you have built a calendar table the way you want it, the next time you need one, you can simply copy the query code and paste it into the advanced editor window and ‘BANG’ the complete calendar table will be generated. In a previous post, I showed how to create a dynamic calendar table using the ‘CALENDARAUTO’ function, and mentioned that this could be done in Power Query too.

This post serves as a foretaste of the mechanisms which allow this to happen…

For more insights into getting the most out of Power BI, stay tuned for our next Power BI Tip… Until then happy reporting!

By | 2017-11-24T13:14:14+00:00 May 12th, 2016|Data Bear, Power BI, Power BI Tips|0 Comments

About the Author:

Johann is the founder of Data Bear and specializes in getting the right data into the right hands, in a format that is quick and easy to understand. Revolutionizing your reporting experience from a time consuming frustration into dynamic interactive visualizations is what he takes pleasure in.

Leave A Comment