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 otherwise known as the ‘Edit Query’ functionality in Power BI desktop. With a simple Graphical user interface (GUI), it can consume data from almost all data sources. It also offers options to clean, transform, and mashup data with just a few mouse clicks and no programming required.
So, how does Power Query work?
In the background, as a query language, it makes use of 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:
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.
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 that one can 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:
Let’s take an example, where we only have a date field in our dataset and want an additional field that 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.
open query editor > get data > blank query > type =#shared in formula bar > hit enter
As per our scenario, let’s take a look at how to find the required function, deriving the short month name:
after typing “#Shared” and getting a list of functions > convert this list to a table by clicking the “Into Table” icon as depicted above.
Then we’re left with a two-column table:
The ‘Name’ column contains the function names.
Filter the ‘Name’ column by typing “date” and selecting “Date.ToText” as shown below:
After this, click on ‘Function’ from the ‘Value’ column this will open the new window with the function detail and how to use it.
Furthermore, if you want to see the help article related to any function, you can simply type the name of the function like this:
As you can see, the associated information is displayed.
Now that we have an idea of which function to use, let’s get back to our objective of getting short month name:
Open your date table query and click “Add custom column” as per the steps below:
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:
– MMM for a short month
– MMMM for the full month name
– ddd for short weekday name
– dddd for full weekday name
There it is, we’ve got a new column with the short month name.
I previously mentioned 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!