Calc_columns_plus_dax_equals_easier_transformations

 

Calc_columns_plus_dax_equals_easier_transformationsHumans 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 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 [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.