Rolling Join in Power Query

[peterhui50]

I have two tables here,  A table of when individuals joined the program and when individuals have transactions. I want a conditional join by date but I don't know if it exists in Power Query.

Program Table

Transaction Table

Joined Table

The problem is this -- I joined the two tables by NAME and included a T/F column to indicate whether the transaction date is after when the individual joined the program. I just want to link it to the transactions AFTER they have joined the program. (Individuals can have multiple transactions, even before they joined the program).

I just want something like this - a conditional join, excluding the records that are before the Date_Joined Date.

I can't use the T and F column I had created earlier because if I filter on TRUE, it'll exlude all the folks that have transactions prior to when they have joined the program. I still need to know their names.

In SQL it would have been...

SELECT * FROM [Program Table]

LEFT JOIN [Transaction Table] ON NAME

AND [Program Table][Date Joined] <= [Transaction Table][Transactions]

Really hope someone out there would know this ! 

[PhilipTreacy]

Hi @peterhui50 

Download example PBIX file

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

[peterhui50]

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] 

[PhilipTreacy · Accepted solution]

Hi @peterhui50 

Download sample PBIX file

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

[peterhui50]

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!

[PhilipTreacy]

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

[selimovd]

Hey @peterhui50 ,

why don't you just use the SQL join as data source for Power BI?

There is nothing wrong with it and it seems to be a lot easier than with Power Query.

If you need any help please let me know.

If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards

Denis

Blog: WhatTheFact.bi

[peterhui50]

I would love to, but it's just not allowed. Where I work we basically work off flat csv files. That would have been a great solution.