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.