Dataflow - how to make lookup in power query editor

[asc002]

HI

I have the following two tables and I would like to add "contract No." for each case No. in table1 from table2:

as you can see there could be more contracts for the same installation but only one will be applicable on the date of each case. I would like to do it as part of my dataflow so in power query editor.

thanks for any inside.

Andrea

[v-ssriganesh]

Hi @asc002,
I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please Accept it as a solution and give a 'Kudos' so other members can easily find it.
Thank you.

[v-ssriganesh]

Hi @asc002.,

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[v-ssriganesh]

Hi @asc002 ,

Could you please confirm if your query have been resolved? If they have, kindly mark the helpful response and accept it as the solution. This will assist other community members in resolving similar issues more efficiently.

Thank you.

[dufoq3 · Accepted solution]

Hi @asc002, check this (two versions):

Output

v1

let
    T1 = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dc9BCsAgDATAv+Qs1E1itW8R//+NNhZBNIIH3Tm4WyuBAkVc3+HI+j3AQi1UYgOegEU7iIFMIJo62D3qBD1Nbnq7abb0dsqUFUaZZ4VRBnGVP7a9wBZz/8z5GXIUPUo6iu3G3sqGI3szbDnKKu0F", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"case no" = _t, #"case date" = _t, #"Installation no" = _t]),
    T1_ChT = Table.TransformColumnTypes(T1,{{"case no", Int64.Type}, {"case date", type date}, {"Installation no", Int64.Type}}),
    T2 = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyVtJRsgACIGVgqA9ERgZGBsgcY6VYHZhCSyCAyplB5HSUjA31DY1AHBOwQiNjE6CgCRAQMBFDIVYTYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Installation no" = _t, #"contract no" = _t, #"contract start date" = _t, #"contract end date" = _t]),
    T2_ChT = Table.TransformColumnTypes(T2,{{"Installation no", Int64.Type}, {"contract no", type text}, {"contract start date", type date}, {"contract end date", type date}}),
    T2_Buffer = Table.Buffer(T2_ChT),
    StepBack = T1_ChT,
    Ad_ContractNo = Table.AddColumn(StepBack, "Contract no.", each
        [ a = Table.SelectRows(T2_Buffer, (x)=> [Installation no] = x[Installation no] and [case date] >= x[contract start date] and [case date] <= x[contract end date])[contract no],
          b = if List.IsEmpty(a) then null else Text.Combine(a, ", ")
        ][b], type text)
in
    Ad_ContractNo

v2

let
    T1 = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dc9BCsAgDATAv+Qs1E1itW8R//+NNhZBNIIH3Tm4WyuBAkVc3+HI+j3AQi1UYgOegEU7iIFMIJo62D3qBD1Nbnq7abb0dsqUFUaZZ4VRBnGVP7a9wBZz/8z5GXIUPUo6iu3G3sqGI3szbDnKKu0F", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"case no" = _t, #"case date" = _t, #"Installation no" = _t]),
    T1_ChT = Table.TransformColumnTypes(T1,{{"case no", Int64.Type}, {"case date", type date}, {"Installation no", Int64.Type}}),
    T2 = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyVtJRsgACIGVgqA9ERgZGBsgcY6VYHZhCSyCAyplB5HSUjA31DY1AHBOwQiNjE6CgCRAQMBFDIVYTYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Installation no" = _t, #"contract no" = _t, #"contract start date" = _t, #"contract end date" = _t]),
    T2_ChT = Table.TransformColumnTypes(T2,{{"Installation no", Int64.Type}, {"contract no", type text}, {"contract start date", type date}, {"contract end date", type date}}),
    T2_Ad_Dates = Table.AddColumn(T2_ChT, "Dates", each List.Dates([contract start date], Duration.Days([contract end date]-[contract start date])+1, #duration(1,0,0,0)), type {date}),
    T2_ExpandedDates = Table.ExpandListColumn(T2_Ad_Dates, "Dates"),
    T2_SelectedColumns = Table.SelectColumns(T2_ExpandedDates,{"Installation no", "Dates", "contract no"}),
    StepBack = T1_ChT,
    MergedQueries = Table.NestedJoin(StepBack, {"Installation no", "case date"}, T2_SelectedColumns, {"Installation no", "Dates"}, "contract no", JoinKind.LeftOuter),
    Ad_ContractNo = Table.TransformColumns(MergedQueries, {{"contract no", each if List.IsEmpty([contract no]) then null else Text.Combine([contract no], ", "), type text}})
in
    Ad_ContractNo

[p45cal]

In the linked-to workbook below, the 2 blue tables are your data and the green table is the result with contract numbers. If more than one contract number applies, they'll be listed separated by a comma.

https://app.box.com/s/d1p9tgs3xet1kyr3455t7j3vzuikegdl