Variables in M Power Query
M code as generated by the ribbon and buttons relies heavily on variables. You could say that it’s variables all the way down.
But sometimes you need a bit more power than what’s available in the user interface.
Understanding variables in key to understanding M, the language of Power Query. You can do LOTS without understanding M. You can edit formulas generated by the buttons in the ribbons and use functions not in the user interface. This post is about understanding M a bit more while also including a couple of nifty things you can do with it as a result of understanding it better.
According to the M Language Let page in the Power Query documentation, Let is the keyword for creating variables, and its syntax looks like this:
let variable-list in expression
The variable list is a set of expressions separated by commas.
A second keyword, in, defines the scope of the variable, or where it is applied.
let
x = 1 + 1,
y = 2 + 2,
z = y + 1
in
x + y + z
Looking at this expression, you can see that it looks a lot like the steps in the Advanced Editor. The steps are a list of variables, and typically each step refers to the preceding one. Variables can either be one word, or multiple words enclosed in #””. The final step is typically defined as the scope for the variable list, which loads it to the query. This is the same practice as writing DAX using variables and then having one variable which is the result. It breaks up the formula and it makes it easier to troubleshoot.
let
Source = Table.FromRows( Json.Document(Binary.Decompress(Binary.FromText("i45WMtc31DcyMDJS0lEKSk0Bkn6+wT5KsTogGSMDmJRTTmkqSC4cKmeB0OVelJqah6zP0AghGZmak5NfjqwTKIsw1r8oMS89FU2zkSlMOqC0qCAHVRpitDFQzLGqtAjFTUA5U5hkUH4xij4jfWOYlE9mLkJbLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"End Date" = _t, Name = _t, Type = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"End Date", type date}})
in
#"Changed Type”
Fun with M Variables
There’s a couple of techniques I’ve learned along the way to use this variable structure to get results that would be challenging to do otherwise: 1. isolated steps and 2. inline variables.
Isolated steps
I create isolated steps in the Advanced Editor before the Source step, so that I don’t disrupt the flow between steps.
let
NMSL = Table.SelectRows(Events, each ([Type] = "NMSL")),
NWSL = Table.SelectRows(Events, each ([Type] = "NWSL")),
Source = …
In this case, I’ve created two steps. Each step filters an external query (Events) in a different way. They’re isolated, because no later step takes them as a starting point. These variables, which are table variables, incidentally, make the later steps simpler.
Inline variables
When I have a complex step, I may use an inline variable to capture a value at one level for use at another level. In the example below, I have a column called TypeList, which is a structured column which has a table in it. I need to filter that table [End Date] column by the [ImpactDate] column in the main table of the query. The first each keyword accesses the rows of the main query. The second each accesses the rows of the table in the structured column.
= Table.AddColumn(#"Added Custom", "FilteredEvents",
each let _dt = [ImpactDate] in Table.SelectRows([TypeList],
each [End Date] >= _dt ))
Here’s what I started with:
= Table.AddColumn(#"Added Custom", "FilteredEvents",
each Table.SelectRows([TypeList],
each [End Date] >= #date(2022,8,15) ))
First line: add a column to the table in the previous step #”Added Custom” named “FilteredEvents”
Second line: for each row of the main table, select rows for the column named [TypeList]
Third line: filter [End Date] column of [TypeList] structured column by the specific value 8/15/2022.
The variable _dt captures the date so that it can be used to filter the structured column. Putting the variable after the first each ensures that it’s at the right level to get the value.
The problem
For context, the original problem was to look at a short list of Impacts with dates and to find the first event in the list of events following that date. The wrinkle is that the second list is also filtered by a type in the original list: either NMSL, NWSL, or Both. This can be solved in the model with two tables— not joined by a relationship. I’ve done that in my file as a bonus.
My solution
My approach was to use use table functions in custom columns to get results. Then I removed these structured columns after I was done. I use the user interface to do most of the query, but the advanced editor to add the variables described.
The full file is below. And after that, I have the full M code for both queries.
blog-files/First event after impact.pbix (github.com)
// Events
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtc31DcyMDJS0lEKSk0Bkn6+wT5KsTogGSMDmJRTTmkqSC4cKmeB0OVelJqah6zP0AghGZmak5NfjqwTKIsw1r8oMS89FU2zkSlMOqC0qCAHVRpitDFQzLGqtAjFTUA5U5hkUH4xij4jfWOYlE9mLkJbLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"End Date" = _t, Name = _t, Type = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"End Date", type date}})
in
#"Changed Type"
// Impacts
let
NMSL = Table.SelectRows(Events, each ([Type] = "NMSL")),
NWSL = Table.SelectRows(Events, each ([Type] = "NWSL")),
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtc3NNU3MjAyUtJRCkpNAZJ+vsE+SrE60UqGRkhyTjmlqSDJcISkkQFM0r0oNTUPpCi/JAMsa6FvZAiT9C9KzEtH6I0FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ImpactDate = _t, Name = _t, Type = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"ImpactDate", type date}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "TypeList", each if [Type] = "Both" then Events else if [Type] = "NMSL" then NMSL else if [Type]= "NWSL" then NWSL else null),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "FilteredEvents", each let _dt = [ImpactDate] in Table.SelectRows([TypeList], each [End Date] >= _dt )),
#"Added Custom2" = Table.AddColumn(#"Added Custom1", "Custom", each Table.PromoteHeaders(Table.Transpose(Record.ToTable(Table.First(Table.Sort([FilteredEvents], {"End Date", Order.Ascending} )))))),
#"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom2", "Custom", {"End Date", "Name", "Type"}, {"Event End Date", "Event Name", "Event Type"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Expanded Custom",{{"Event End Date", type date}, {"Event Type", type text}, {"Event Name", type text}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"TypeList", "FilteredEvents"})
in
#"Removed Columns"