This week I decided to spare everyone a really, really long video and focused on the trickiest part of this week’s challenge: dynamically selecting trades and then providing a rolling average.
Nested tables with a nested let statement allowed me to dynamically select, but it was a little tough to figure out to be honest. I will try to explain the following code as best as I can. Here is what the step looked like when I got to it:
|Sector||Purchase Price||Trade Order|
After clicking “Add Column” then “Custom Column”, I added the following code in:
Table.AddColumn( // Select the table #"Buffer the Table", // Name the new column "Selected Trades", each // Nested let statement let trade = [Trade Order], min_trade = [Trade Order] - #"Previous Trades", sec = [Sector] in Table.SelectRows( #"Buffer the Table", // for each row, check the Trade Order column and make sure that the sector is the same each [Trade Order] <= trade and [Trade Order] > min_trade and [Sector] = sec ) )
This is a little complicated to explain, but remember last week’s solution involved a nested let statement. This allows you to set variables for the current row context and the switch to a table context to establish a nested table in that column that is filtered on whatever you want. In this case, I set my filter criteria upfront, which was based on the current row context and then used the criteria in Table.SelectRows(). The selected rows became a nested table in this new column.
Also note that #”Previous Trades” was the dynamic parameter called for in the challenge. If I changed this to 4, 5 or something else, the query would recalculate and I would see the right number of trades in the nested column.
In the very next step, I added another custom column to calculate the average of the purchase price.
There were many other steps after this, but it probably would be too long for a blog as well as a YouTube video, so I figured I would focus on this piece as it was the most pivotal part.
If you have any questions please reach out! Here is the explainer video: