Forum Discussion
Solved
Dataflow - how to make lookup in power query editor
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
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_ContractNov2
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
5 Replies
- p45cal
Solution Supplier
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
- dufoq3
Community Champion
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_ContractNov2
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 
v-ssriganeshCommunity Support
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.
- 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,
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.
