Lookup value in Power Query between two dates

[Paolo_lito]

Hello ,

I need your help to create a custom column in Power Query .

I would to have a custom column in the second table with the correct Ref ID of the first table .

The Custom Ref ID  of the Table 2 is depending of the date range  (From and To)  and the Name of the Table 1.

For Example : the custom Ref ID for the Product A and the Date 15/03/2020 is 001

Table 1

Product		Date From		Date To		Ref ID
A		01/01/2020		30/06/2020		001
A		01/07/2020		31/12/2020		002
B		01/01/2021		30/06/2021		003
B		01/07/2021		31/12/2021		004

Table 2

Product		Date		RefID ( Custom Column)
A		15/03/2020		001
B		15/09/2021		004

Can you please help me to transpose this in power Query ?

Thanks a lot .

Regards

Paolo_lito

[Paolo_lito]

Hello Unfortunatelly the solution proposed  by @Anonymous doesn't work .  perhaps a problem with the merge , do you need to expand the Table1 after the merged Queries .

[Anonymous]

Hi @Paolo_lito 

One way to do, merge two tables on Prdocut column, then add a custom column

= Table.AddColumn(#"Merged Queries", "RefID", (x)=>Table.SelectRows(x[Table1],each x[Date] >= [Date From] and x[Date]<=[Date To])[Ref ID]{0}?)

[Paolo_lito]

Hi @Anonymous , what kind of Merge ? 

what should this new table look like .

sorry I 'm new in the power query world .

[Anonymous · Accepted solution]

Hi @Paolo_lito 

The step #"Merged Queries" is join this Table 2 with Table 1 on Product column, 

It is through GUI, highlighted here

Below is the result of Table 2

And the M code for Table 2, I am using locale as the date format in my machine is different from yours

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTI01Tcw1jcyMDJQitWJVnKCClmChAyVYmMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Product = _t, Date = _t]),
    #"Changed Type with Locale" = Table.TransformColumnTypes(Source, {{"Date", type date}}, "en-GB"),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type with Locale", {"Product"}, Table1, {"Product"}, "Table1", JoinKind.LeftOuter),
    #"Added Custom" = Table.AddColumn(#"Merged Queries", "RefID", (x)=>Table.SelectRows(x[Table1],each x[Date] >= [Date From] and x[Date]<=[Date To])[Ref ID]{0}?)
in
    #"Added Custom"

[Paolo_lito]

@Anonymous  it works as indicated !  it was not necessary to expand the table 1 into the table 2 .

Thank you very much for your Help