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

[Anonymous]

Dear all,

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.

Can someone help me with it?

I have prepared dummy data to show what is required in this scenario:

Table 1:

Export date        ÍD       Company       Product     QTY

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

Table2:

Export date    ID      Company   Product   Unit Price

01/08/2021	1	Philips	Lamps	€                     1,5
01/08/2021	2	Dell	Laptop	€             1.500,0
01/08/2021	3	Honeywell	HD	€                100,0
01/08/2021	4	Benz	A-Class	€          40.000,0
01/08/2021	5	BMW	2-Serie	€          65.000,0
01/07/2021	1	Philips	Lamps	€                     3,0
01/07/2021	2	Dell	Laptop	€             1.000,0
01/07/2021	3	Honeywell	HD	€                120,0
01/07/2021	4	Benz	A-Class	€          35.000,0
01/07/2021	5	BMW	2-Serie	€          80.000,0
01/06/2021	1	Philips	Lamps	€                     5,0
01/06/2021	2	Dell	Laptop	€             3.000,0
01/06/2021	3	Honeywell	HD	€                   50,0
01/06/2021	4	Benz	A-Class	€          75.000,0
01/06/2021	5	BMW	2-Serie	€        100.000,0

I need to merge based on ID to find the right unit price. but:

For all ID's with export date (Table2) <= 1-6-2021 find unit price with export date (Table1) = 1-6-2021, 

else, find a unit price where export date  month/Year (Table2) =  export date month/year (Table1)

I hope this is clear 🙂

[Vera_33 · Accepted solution]

Hi @Anonymous 

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

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)=> Table.SelectRows(TableB, each [ÍD]=OT[ÍD] and [Company]=OT[Company] and [Product]=OT[Product])),
    #"Added Custom" = Table.AddColumn(AddTableB, "Unit Price", (OT)=> if OT[Export date] <= #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"