Forum Discussion

Paolo_lito's avatar
Paolo_lito
Frequent Visitor
4 years ago
Solved

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 Ta...
Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    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's avatar
Paolo_lito
Frequent Visitor
4 years ago

Hi Anonymous , what kind of Merge ? 

what should this new table look like .

sorry I 'm new in the power query world .

Anonymous's avatar
Anonymous
Not applicable
4 years ago

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's avatar
    Paolo_lito
    Frequent Visitor
    4 years ago

    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