Preppin’ Data 2023 Week 13 – With Power Query

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:

SectorPurchase PriceTrade Order
Basic Industries33893.471
Basic Industries27640.912
Basic Industries69034.083

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:

Bitnami