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 u...
- 3 years ago
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
AlienSx
3 years agoSuper 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
expandAnthony_Nguyen
3 years agoNew Member
It works like Charm. Thank you so much.
I am trying now to test with more complex data table