61 - Creating variables in Power Query, using inner queries
A neat trick I discovered when I read code my coworker had made. He used the "is latest" filter on a date column, which let me down a rabbit hole.
It turns out I don't have to make intermediary steps and use these like variables. I can actually make variables.
I was going to cover inner queries in video 59 (and I still will), but those are more complicated. You can use them to eg. get around the Formula.Firewall-error, the bane of my existence.
The variable syntax is simple: In DAX, you write "var Name = value return".
In M, you write "let Name = value in".
You can define a variable outside and inside a function, and I show what happens if you do.
00:00 Intro
00:13 OG method - sort + first row
00:49 Intermediary method - find most recent date (List.Max)
03:05 Local variables method
03:32 Filter date-column on "is latest"
03:52 "Let variable-name equal value in"-syntax
04:27 DAX uses "var return", M uses "let in"
04:36 Adding more steps to an inner query (I fail...)
06:17 Defining a variable before vs inside a function
07:08 What does "let in" mean?
07:43 All steps before the last, are actually variables
07:59 Power Query is a lazy evaluator
08:28 What the Advanced Editor looks like with inner queries
09:00 The "let let in in" syntax is seen in user-defined functions.