Forum Discussion
Anthony_Nguyen
3 years agoNew Member
Duplicate rows based on the values from a table
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
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
2 Replies
- AlienSxSuper User
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- Anthony_NguyenNew Member
It works like Charm. Thank you so much.
I am trying now to test with more complex data table