Conditional Merge with a date range

[Anonymous]

Hi. 

First off- I did try to use these to solve my query but i do not understand this (magic) function so it really didnt work for me, though i think the answer may lie in there somewhere. (see below link) 

community.fabric.microsoft.com/t5/Desktop/Merging-info-with-a-date-in-a-first-table-and-a-range-of-d...

Here is my question, I have attached two sample query tables below. What I need to do is pull in which contract is relevant for the part number given the condition that the transaction date falls between the contract dates. If I was to do this in Excel I could use a formula like

"=IF(ISNA(XLOOKUP(partnumber,Table2[partnumber],Table2[partnumber])),"",LOOKUP(2,1/(Table2[StartDate]<=[Transactiondate])/(Table2[EndDate]>=[TransactionDate]),Table2[ContractName]))"

essentially I need to do a merge that first checks where the transaction range lies between the two dates in the contract table and then pull through the contract name based on the part number match. 

@v-xuding-msftmaybe you can help?

Table 1

Supplier code	Part Number	Transaction date	contract name
11111	A1	01/02/2018
11111	A1	29/10/2016
11111	A3	06/11/2019
11111	A4	11/01/2017
11111	A5	06/04/2016
22222	A1	11/08/2018
11111	A6	20/12/2018
22222	A2	18/10/2018
11111	A7	12/02/2019
22222	A3	06/03/2017
22222	A3	01/02/2017
22222	A1	19/06/2019
22222	A3	06/03/2017

Table 2

Contract reference	Part Number	StartDate	EndDate
Supplier 1 contract 1	A1	01/01/2016	31/12/2019
Supplier 1 contract 1	A2	01/01/2016	31/12/2019
Supplier 1 contract 1	A3	01/01/2016	31/12/2019
Supplier 1 contract 1	A4	01/01/2016	31/12/2019
Supplier 1 contract 1	A5	01/01/2016	31/12/2019
Supplier 1 contract 1	A6	01/01/2016	31/12/2019
Supplier 1 contract 1	A7	01/01/2016	31/12/2019
Supplier 2 Contract 1	A1	01/01/2020	31/12/2025
Supplier 2 Contract 1	A2	01/01/2020	31/12/2025
Supplier 2 Contract 1	A3	01/01/2020	31/12/2025

[Anonymous]

Thanks, that worked. Would still be interested to see a solution using the one step approach in the link if anyone knows how?

Regards

[NarenM · Accepted solution]

Hi kgiboin - To get the contract name based on the part number match and the condition that the transaction date falls between the contact dates.

Table 1 (Transactions)
Supplier code
Part Number
Transaction date
contract name
Table 2 (Contracts)
Contract reference
Part Number
StartDate
EndDate

Steps to Merge the Tables

Load the Tables into Power Query:
Select the data for both tables and load them into Power Query (in Excel, go to the "Data" tab > "From Table/Range").

Convert the Date Columns to Date Type:
Click on the "Transaction date," "StartDate," and "EndDate" columns.
In the "Transform" tab, select "Date" from the "Data Type" dropdown.

Merge the Queries:
With Table 1 selected, click on the "Home" tab > "Merge Queries."
Select Table 2, and match the "Part Number" columns in both tables.
Choose "Inner" (or "Full Outer" if you want to keep all rows), then click "OK."

Expand the New Column:
Click on the expand button in the "Table 2" column.
Select the columns "Contract reference," "StartDate," and "EndDate."

Add a Custom Column:
Go to the "Add Column" tab > "Custom Column."
Enter the following formula, replacing the column names if necessary:
= if [Transaction date] >= [StartDate] and [Transaction date] <= [EndDate] then [Contract reference] else null

Clean Up:
You may want to remove unnecessary columns or rename the custom column to "contract name."
If there are duplicate rows, you can use the "Remove Duplicates" option.

Apply and Close:
Click "Close & Apply" in Power BI or "Close & Load" in Excel.

You should now have a table that includes the relevant contract name for each transaction, based on the part number and transaction date. If the transaction date doesn't fall between the contract dates, the contract name will be null.