Hi all,
I am looking for Power Query solution for the task below (Duplicate rows based on the values from a table)
Row to be duplicated
No | Type | Cost |
1 | E1 | 1 |
2 | E2 | 2 |
Look up table
Type | Code |
E1 | A |
E1 | B |
E2 | C |
E2 | D |
E2 | E |
Expected results
No | Type | Cost | Code |
1 | E1 | 1 | A |
1 | E1 | 1 | B |
2 | E2 | 2 | C |
2 | E2 | 2 | D |
2 | E2 | 2 | E |
Thank you in advance for your help
Solved! Go to Solution.
Hello, @Anthony_Nguyen
let
data = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXIFEYZKsTrRSkYgLogwUoqNBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [No = _t, Type = _t, Cost = _t]),
lookup_table = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcjVU0lFyVIrVgTKdIEwjINMZwXRBMF2VYmMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Type = _t, Code = _t]),
lookup_gr = Table.Group(lookup_table, {"Type"}, {{"Value", each _}}),
lookup_record = Record.FromTable(Table.RenameColumns(lookup_gr, {"Type", "Name"})),
join = Table.AddColumn( data, "lookup", each Record.FieldOrDefault( lookup_record, [Type], #table({"Code"}, {{"not found"}}))),
expand = Table.ExpandTableColumn(join, "lookup", {"Code"}, {"Code"})
in
expand
Hello, @Anthony_Nguyen
let
data = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXIFEYZKsTrRSkYgLogwUoqNBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [No = _t, Type = _t, Cost = _t]),
lookup_table = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcjVU0lFyVIrVgTKdIEwjINMZwXRBMF2VYmMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Type = _t, Code = _t]),
lookup_gr = Table.Group(lookup_table, {"Type"}, {{"Value", each _}}),
lookup_record = Record.FromTable(Table.RenameColumns(lookup_gr, {"Type", "Name"})),
join = Table.AddColumn( data, "lookup", each Record.FieldOrDefault( lookup_record, [Type], #table({"Code"}, {{"not found"}}))),
expand = Table.ExpandTableColumn(join, "lookup", {"Code"}, {"Code"})
in
expand
It works like Charm. Thank you so much.
I am trying now to test with more complex data table