Combining data in Power query

[nunnc01]

Hi,

I have a table in Power Query that has a Unique ID and a month end date. Each ID can have multiple rows, each with a different month end date similar to this:

Unique ID    Month End

ID 1            31/01/2021

ID 1            28/02/2021

ID 1            31/03/2021

ID 2            31/01/2021

ID 2            28/02/2021

What I want to do is pull in an additonal column of information from another table that I have which is structured like this where the ID matches and the Month End Date falls between the Start Date and End Date.

Unique ID      Start Date          End Date              Column I want to show in my original table

ID 1               07/01/2021        14/02/2021           Text

ID 1               14/02/2021        16/03/2021           Text

ID 2               11/12/2020        null                        Text

Please can someone advise how this can be done in Power Query please?

Thanks

[PhilipTreacy · Accepted solution]

Hi @nunnc01 

Download sample PBIX

I'd create a new column in Table2 with the month end date for the month in Start Date. 

You can then merge the tables on the ID and Month End Columns

Giving this

Regards

Phil