This week was uh, complicated. Anytime you have to take out Table.Buffer, then you know that it was an advanced query.
The challenge involved banking data from two countries, the UK and Ireland. You were tasked with summarizing the data based on reporting days and oh by the way each country had different holidays.
The video is posted below, but I wanted to write about some nested “let” syntax that I used.
In the Power Query documentation, the let expression is defined as:
“The let expression encapsulates a set of values to be computed, assigned names, and then used in a subsequent expression that follows the in statement.”
You can think of it as saying “let this equal something in that.” In the context of a step, you can use a let statement to define a variable, which is what I did.
I tried to explain it as best as I could in the video, but here is some commented code to further detail an example of how I used it:
#"Choose Next Date" =
Table.Buffer(
Table.AddColumn(
#"Added Nested Table",
"Reporting Day",
each let outer_date = [Date]
in
List.First(
List.Select(
Table.SelectRows(
[Whole],
each _[Reporting Day Flag] = 1)[Date],
each _ >= outer_date
)
), type date
)
)The nested let statement in there is referencing the context of the date column in the parent table. I then used that to set a variable, which then let me evaluate against it in a nested table. It was tricky stuff to get right, but once I did, it let me filter and get the next business day for the UK.
If you are still confused, then it’s probably my explanation and not you lol. I learned a lot about nested let statements from Goodly and specifically this video. It is a great explanation and walkthrough.
Below is my video walkthrough. It is a longer one, but hopefully it provides you with some ideas as to how to go about using some of these techniques.
