PowerQuery (M)agic: Combine M Functions and Formulas In Custom Columns

Humans like turning a few things into many things. Investing $1 to get $10 . . . growing saplings into oaks . . . taking two rabbits and creating a fluffle.

This post will show you how to do the exact opposite. How to move

• from many to a few
• from 10 to 1
• from a herd to a stag

The M language lets us create more efficient data transformations using fewer applied steps.

The secret lies in using different “M” functions to create “combined formulas” within custom columns (plus a sprinkling of Power Query (M)agic).

It’s a three-step process:

1. Determine what the end result should look like (“Begin with the end in mind“)
2. Find the M functions that will get you there
3. Combine the M functions into one combined formula within a custom column

Example 1 – change “Date” to “Year” in one step

Determine what the end result should look like:

• transform 2/1/2017 to CY2017

Find the M functions that will get you there:

• reference the “Date” column — Date.Year
• show the date value as a year value with a “CY” prefix — text + ‘&’ (concatenation)

Combine the M code into one combined formula within a custom column:

• =”CY”& Text.From(Date.Year([Date]))

– – – – – – – – – – – – – – – – – – – – – – – – – – – – – – –

The (M)agic happens by combining multiple M functions into one formula.  The benefit: you can do more in fewer steps!

We combined two functions and added the “CY” prefix to the result:

• Text.From                          where the variable is the function Date.Year
• Date.Year                           where the variable is the [Date] column
• Concatenation                   text + ‘&’ (concatenation)

In plain English, formula =”CY”& Text.From(Date.Year([Date])) says to

1. take the value in the [Date] column
2. convert it into a Year value
3. then convert it into a Text value
4. then add a ‘CY’ prefix with the text result of Steps 1 – 3

– – – – – – – – – – – – – – – – – – – – – – – – – – – – – – –

Where do you find the “M” functions? Here are three methods I use:

Search the “Power Query M Reference” (https://goo.gl/aBM74J)

Google it

For example, search “power query change date to year,” and you’ll get several useful results.

Try using “Column From Examples”

Works well for simple date transformations. Look for the formula generated.

Note: I really like the “Column From Examples” feature. I tried doing the combined formula using this feature, but it could not create the transformation.  Sometimes this method works, sometimes it does not.  Good to know it’s available and usually worth a try.

– – – – – – – – – – – – – – – – – – – – – – – – – – – – – – –

Example 2 – Go from “2/1/2017” to “Wk_5” in one step

Determine what the end result should look like: transform 2/1/2017 to Wk_5

Find the M functions that will get you there:

Combine the M code into one combined formula within a custom column:

• =”Wk_”&Number.ToText(Date.WeekOfYear([Date]))

– – – – – – – – – – – – – – – – – – – – – – – – – – – – – – –

Example 3 – Go from “2/1/2017” to “Cycle_02” in one step

Determine what the end result should look like: transform 2/1/2017 to Cycle_02

Find the M functions that will get you there:

Combine the M code into one combined formula within a custom column:

• =”Cycle_”&Text.PadStart(Number.ToText(Date.Month([Day of Period])),2,”0″)

Who0h – we just combined multiple M functions (date, text, concatenation) with an inferred “if-then” statement (ie text padding) in a one-pass formula.  Magical.

– – – – – – – – – – – – – – – – – – – – – – – – – – – – – – –

Notes

• Don’t overthink this (combining M functions and formulas in custom columns).  Keep it simple and use where appropriate.
• It’s sometimes better to have more applied steps (especially if it helps with debugging).  Don’t create a rabbit’s nest of embedded functions that no one could ever decipher.  Remember that “Everything should be made as simple as possible, but not simpler.”
• If you’re copying formulas from this post into Power Query, make sure to copy in an un-formatted manner.   This means removing any formatting from the text (especially for the double-quotes).  Otherwise, Power Query has a nasty habit of giving a formula error message.  It’s because “ (squiggly) is not the same as “ (no squiggle).   Trust me on this.  Here’s how I do it:
  • copy the formula =”Cycle_”&Text.PadStart(Number.ToText(Date.Month([Day of Period])),2,”0″) into Notepad
  • copy from Notepad into the Power Query custom column formula box

– – – – – – – – – – – – – – – – – – – – – – – – – – – – – – –

Summary

I hope you found value in this post.  We unlocked a new method to create
efficient data transformations using fewer applied steps.  It’s like a magician pulling a rabbit from a hat.  The secret lies in using different M functions to create “combined formulas” within custom columns.  I encourage you to explore the possibilities. Try it out with your files and share your results in the comments section.

A sample file is provided so you can explore further.  You can download it here.

If you have questions, please email me at doug@dougburkedata.com and I will happily assist.

Microsoft’s platform is the world’s most fluid & powerful data toolset.  Get the most out of it.

No one knows the Power BI ecosystem better than the folks who started the whole thing – us.

Let us guide your organization through the change required to become a data-oriented culture while squeezing every last drop* of value out of your Microsoft platform investment.

* – we reserve the right to substitute Olympic-sized swimming pools of value in place of “drops” at our discretion.