Forum Discussion
Rolling Join in Power Query
Hi peterhui50
Something like this where all Transactions are in the same field? This keeps it at 1 record per Name, but you could also extract the data so that each Transaction is a separate record for that Name.
Regards
Phil
Hi peterhui50
In your description you say that people can have multiple transactions so I added some more dummy data to my file and then created a custom column that showed either the Transaction Date if it was after they joined, null otherwsie
You can then Group on Name retaining all other columns in Tables
Then use List.Max to get the Max Date from the Custom column in each Table created above. This will give null for transactions before the Join date
= Table.AddColumn(#"Grouped Rows", "Custom", each List.Max([All][Custom]))
Extract the Date_Joined from the Table in a similar way.
Delete the column with the tables, leaving you with a single row for each name, showing latest transaction date or null if their transaction was before their Date_Joined.
In my file check the Merge1 query for these steps.
Regards
Phil
Thanks for this Philip!
This will take their latest transaction, which is partly what I want but how about other transactions that fall after their join date?
Sorry if I wasn't clear, but if there is a way to tweak it so that all transactions that fall after their Date_Joined it would be perfect.
It's like employee number 1 joined on 1/2/2019, but I also want all their transactions that fall after this date, not just the latest, so I want the 1/1/2020 and the 1/13/2020 transactions.
I think maybe there is a way to tweak it so that it is a a List format and it uses List.Max and any transactions that fall below it will be kept. each List.Max{All][Transactions] <= List.Min[All][Date_Joined]
Hi peterhui50
Something like this where all Transactions are in the same field? This keeps it at 1 record per Name, but you could also extract the data so that each Transaction is a separate record for that Name.
Regards
Phil
Thank you! I think after I can just split those up and rejig them. Ideally I was hoping that they would be in two rows.
It's all good, you've done a lot on this already. Thank you!
Hi peterhui50
No worries.
To get the transactions on separate rows, right click the TRansactions column header -> Split Column -> By Delimiter.
Choose Comma as the delimiter, and open the Advanced options, select Split into Rows
Which gives you this
Regards
Phil