Substitution Variables In Power Query

Doug Burke gives us an example of here substitution variables make our lives easier in Power Query:

A substitution variable substitutes a variable (a changing value) to get a different result

    a + b = c (where ‘a’ and ‘b’ are substitution variables that define value ‘c’)

  •         If a = 5 and b = 2 then c = 7
  •         If a = 25 and b = 9 then c = 34
 SubVars are especially helpful when
  • you need to move Power Query files from one file path, folder and file name to something completely different
  • you want to use the same PQ reports for different time periods (ie changing months)

It involves looking for changeable items.  If something may or will change in the future, it’s a candidate for a substitution variable.

  • file paths are good for subVars

  • so are time elements such as Year or Month

  • you can also combine subVars where both the file path and month may change

Click through for several good examples.

Related Posts

Getting Month Names Given Numbers In DAX

Philip Seamark shows how to convert month numbers into names in Power BI: The most common solution I see offered is along the lines of a SWITCH statement that lists 12 conditions (one for each month).   This works, but can also be done using existing functions. While DAX lacks a dedicated function to convert a […]

Read More

Overlaying Visuals In Power BI

Annie Xu gives us two methods for being able to jump between two visuals in the same space: Disconnected Table method: This method is more towards PowerBI modelers. Basically, the idea is to have a Field in a independent table (no relationship to other tables) as Slicer with your measure choice and then create a […]

Read More

Categories

April 2018
MTWTFSS
« Mar May »
 1
2345678
9101112131415
16171819202122
23242526272829
30