skip to Main Content

 

Calc_columns_plus_dax_equals_easier_transformationsfluffleHumans 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)

Custom Column Formula

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

Pro tip#1

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

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

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

Power Query M Reference

Google it

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

Google it

Try using “Column From Examples”

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

Column from examples

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.

Add Column from examples

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

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:

  • Change date to week — Date.WeekOfYear                                                               Added Custom
  • Change week to text — Number.ToText
  • Add “Wk_” prefix — text + ‘&’ (concatenation)

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:

  • Change date to month — Date.MonthExample 3
  • Change month to text — NumberToText
  • If month is 1 – 9, add a leading zero — Text.PadStart
  • If month is 10 – 12, do not add a leading zero — Text.PadStart
  • Add “Cycle__” prefix — text + ‘&’ (concatenation)

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 createending bunny 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 [email protected] 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.

Doug Burke

Doug is an independent consultant (www.dougburkedata.com) with 20+ years of experience creating Financial data and analytics systems at Fortune 500 companies.

He believes in the ability of Power Query and Power BI to
• Encourage your data dreams
• Fix your data challenges
• Lead you to ongoing data success

This Post Has 7 Comments
  1. Doug,

    In my opinion, you bring up a good idea but users should keep it in perspective. Don’t overdo combining steps. It’s may be better to leave well enough alone. I, too, tried minimizing steps by combining them; thinking less would be better and faster. I did not test the execution times but I did not notice a difference with my few hundred-thousand row data sets.

    There is another reason less may not be better. Combining steps like expanding tables and renaming columns may result in Power Query no longer displaying the gear icon next to the applied step. That means PQ no longer recognizes the function and will not offer dialog boxes to aid in editing the code. I often revisit steps and make changes. Having PQ display dialog boxes upon selection of the gear icon for the applied step is very helpful to me. Therefore, I stopped “cramming ten pounds of potatoes into a five pound bag.” Besides, no one but me ever knows the difference.

    1. Additionally, if you are pulling from a data source that allows query folding, by combining the steps, it can often make it too complex for the Power Query engine to generate the SQL statement, but if you keep them separate, you can fold more or perhaps all of the transformations. I rarely combine steps until I am past the point of folding.

        1. “I rarely combine steps until I am past the point of folding.”

          This is a good example of the kind of information that’s important to know about PQ/M, but is beyond the comprehension of a non-programmer user who is GUI-only. An important issue raised in the article and comments is that a query should be developed such that a non-programmer could maintain it later. I applaud the sentiment but am not convinced that this is achievable.

          I believe that exposing all users to at least some M is essential to an understanding, or at least awareness, of the possibilities of this technology.

          I am also struggling myself to find a balance that leverages the strengths of the GUI interspersed with raw M code. IME MS needs to (and will?) provide some help in this regard (eventually), but in the interim we are left to devise our own techniques for this as best we can.

    2. There is really no need to combine steps as it won’t execute any faster. In my opinion, don’t worry about the number of steps and give each step a descriptive name (and perhaps a comment)

Leave a Comment or Question