Forum Discussion
Lookup value in Power Query between two dates
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
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"
5 Replies
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}?)Hi Anonymous , what kind of Merge ?
what should this new table look like .
sorry I 'm new in the power query world .
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"
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 .
