Forum Discussion

peterhui50's avatar
peterhui50
Helper III
5 years ago
Solved

Rolling Join in Power Query

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.   Pro...
General Comment
Need Help
Tutorial Requests
  • PhilipTreacy's avatar
    PhilipTreacy
    5 years ago

    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

PhilipTreacy's avatar
PhilipTreacy
Super User
5 years ago

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