The CPA Revolution: M-language

Just joining the blog? Explore prior lessons here.

When using Power Query, steps are written using the M-language. Learning a little bit about the M-language can really help expand your Power Query automation skills.

The good news is that most M-language questions can be solved by knowing 3 things.

1. M formulas can be found on Microsoft's website.

2. Steps can be performed first using the tools on the ribbon, and then adding M-language to tweak the final result (see example below).

3. The M-language is case sensitive.

Illustrative example

In this example, the user has a Variance and a Threshold and is trying to determine when a row is above the threshold.

In the Power Query Editor, first create a Conditional Column.

Create a step using the Add Conditional Column prompt. Enter the parameters as shown below to create a column "$ above threshold" which will look at the Variance YOY column. If the value is less than or equal to the value in the Threshold $ column, then the column will have a value of "No" for that row; if not, then the value will be "Yes".

After the column is created, you can look at the M-language in the formula bar. If the bar is hidden, on the ribbon, go to the View menu and check the box for Formula Bar as shown below.

The formula for the conditional column is provided below. The formula can be read as "in the table, add a column, after the Changed Type step. Name the column "$ above threshold". For each row, if the value in the Variance YOY column is less than or equal to the value in the Threshold $, then the value in the new column should be No, else it should be Yes".

Adding to the M-language

What the end user really wants is a column which looks at the absolute value of the Variance YOY and then evaluates if it's above the dollar threshold.

Using the Add Conditional Column prompt, there is no way to add an absolute value evaluation to the formula. However, it can be done in two steps:

Search on Microsoft's website for the M-language for absolute value;
Add the M-language for absolute value to the formula generated using the "Added Conditional Column" prompt.

Search for M-language

On Microsoft's website, in the "filter by title" box starting to type "Absolute" yields a Power Query M function, Number.Abs.

Reading through the description, it is clear Number.Abs is the function we are looking for to modify our existing M-language to calculate the absolute value of the Variance YOY column.

Adding M-language

Now returning to the Power Query Editor, and selecting the step "Added Conditional Column", the formula bar will once again show the function. Modify the formula by typing in Number.Abs (case sensitive) before the column reference for [Variance YOY]. Also surround [Variance YOY] in parens.