Power BI Query Editor: How to merge two tables with a condition (Date)

Question

Dear all,&nbsp;I am trying to perform a merge between two tables in Power BI but I need to use conditional merging in one way or another.&nbsp;Can someone help me with it?&nbsp;I have prepared dummy data to show what is required in this scenario:&nbsp;Table 1:Export date&nbsp; &nbsp; &nbsp; &nbsp; ÍD&nbsp; &nbsp; &nbsp; &nbsp;Company&nbsp; &nbsp; &nbsp; &nbsp;Product&nbsp; &nbsp; &nbsp;QTY01/08/20211PhilipsLamps5002/08/20212DellLaptop1003/08/20213HoneywellHD15004/08/20214BenzA-Class201/08/20215BMW2-Serie201/07/20211PhilipsLamps6002/07/20212DellLaptop5203/07/20213HoneywellHD10004/07/20214BenzA-Class501/07/20215BMW2-Serie5801/06/20211PhilipsLamps4502/06/20212DellLaptop3003/06/20213HoneywellHD2204/06/20214BenzA-Class45501/06/20215BMW2-Serie801/05/20211PhilipsLamps65402/05/20212DellLaptop62203/05/20213HoneywellHD504/05/20214BenzA-Class2401/05/20215BMW2-Serie1201/04/20211PhilipsLamps5902/04/20212DellLaptop2403/04/20213HoneywellHD5804/04/20214BenzA-Class9901/04/20215BMW2-Serie321&nbsp;Table2:Export date&nbsp; &nbsp; ID&nbsp; &nbsp; &nbsp; Company&nbsp; &nbsp;Product&nbsp; &nbsp;Unit Price01/08/20211PhilipsLamps&nbsp;€&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1,501/08/20212DellLaptop&nbsp;€&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1.500,001/08/20213HoneywellHD&nbsp;€&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 100,001/08/20214BenzA-Class&nbsp;€&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 40.000,001/08/20215BMW2-Serie&nbsp;€&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 65.000,001/07/20211PhilipsLamps&nbsp;€&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 3,001/07/20212DellLaptop&nbsp;€&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1.000,001/07/20213HoneywellHD&nbsp;€&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 120,001/07/20214BenzA-Class&nbsp;€&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 35.000,001/07/20215BMW2-Serie&nbsp;€&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 80.000,001/06/20211PhilipsLamps&nbsp;€&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 5,001/06/20212DellLaptop&nbsp;€&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 3.000,001/06/20213HoneywellHD&nbsp;€&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 50,001/06/20214BenzA-Class&nbsp;€&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 75.000,001/06/20215BMW2-Serie&nbsp;€&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 100.000,0&nbsp;I need to merge based on ID to find the right unit price. but:For all ID's with export date (Table2) &lt;= 1-6-2021 find unit price with export date (Table1) = 1-6-2021,&nbsp;else, find a unit price where&nbsp;export date&nbsp; month/Year (Table2) =&nbsp; export date month/year (Table1)&nbsp;I hope this is clear 🙂&nbsp;

anonymous · Accepted Answer

Hi&nbsp;Anonymous&nbsp;&nbsp;Is it something you are looking for? I changed the Table names as TableA, TableB. TableB has the Unit Price, TableA to find the price&nbsp;let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("hdNBC4IwFAfw7+LZcG7vTT1WHjwUBB06iIcOgwQrySDq07dZbgvH6/CYsL/u/XizriOWJixPOONpFEemdqe2a/tBP22O53FFFjWxTnIvyXWVquvGWH+/9ubtb054OaGrul7U8/EJV6UJTl8ELwm6Vury0styse6OwzAeM+b8HtHktgezudirW6t+YhlNkY6SURTklpLRFOYoGUnBeY8hCuY2J2kLoLVIyiLcWCRp4dxSJEkBxHmTIYyz4J+5IFgMUhjJ3WSQ1KDFIH3FYN5kyJK6SwZ//pfCWoCyTCcLLxem5NYCpKUo5j2GLELvNM0b", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Export date" = _t, ÍD = _t, Company = _t, Product = _t, QTY = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Export date", type date}, {"ÍD", Int64.Type}, {"Company", type text}, {"Product", type text}, {"QTY", Int64.Type}}),
    AddTableB = Table.AddColumn( #"Changed Type","TableB", (OT)=&gt; Table.SelectRows(TableB, each [ÍD]=OT[ÍD] and [Company]=OT[Company] and [Product]=OT[Product])),
    #"Added Custom" = Table.AddColumn(AddTableB, "Unit Price", (OT)=&gt; if OT[Export date] &lt;= #date(2021,6,1) 
then Table.SelectRows(OT[TableB],each [Export date]=#date(2021,6,1))[Unit Price]{0}? 
else Table.SelectRows(OT[TableB],each [Export date]=Date.StartOfMonth( OT[Export date]))[Unit Price]{0}?),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"TableB"})
in
    #"Removed Columns"&nbsp;

Forum Discussion

Power BI Query Editor: How to merge two tables with a condition (Date)

1 Reply

Related content

Conditional Formatting with Matrix Table

Conditional Column PowerBi Editor

Conditional table combine

[Advanced Editor] Conditional Concatenate

Conditional Replace Using Outside table

01/08/2021	1	Philips	Lamps	50
02/08/2021	2	Dell	Laptop	10
03/08/2021	3	Honeywell	HD	150
04/08/2021	4	Benz	A-Class	2
01/08/2021	5	BMW	2-Serie	2
01/07/2021	1	Philips	Lamps	60
02/07/2021	2	Dell	Laptop	52
03/07/2021	3	Honeywell	HD	100
04/07/2021	4	Benz	A-Class	5
01/07/2021	5	BMW	2-Serie	58
01/06/2021	1	Philips	Lamps	45
02/06/2021	2	Dell	Laptop	30
03/06/2021	3	Honeywell	HD	22
04/06/2021	4	Benz	A-Class	455
01/06/2021	5	BMW	2-Serie	8
01/05/2021	1	Philips	Lamps	654
02/05/2021	2	Dell	Laptop	622
03/05/2021	3	Honeywell	HD	5
04/05/2021	4	Benz	A-Class	24
01/05/2021	5	BMW	2-Serie	12
01/04/2021	1	Philips	Lamps	59
02/04/2021	2	Dell	Laptop	24
03/04/2021	3	Honeywell	HD	58
04/04/2021	4	Benz	A-Class	99
01/04/2021	5	BMW	2-Serie	321