Forum Discussion

Anthony_Nguyen's avatar
Anthony_Nguyen
New Member
3 years ago
Solved

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...
  • AlienSx's avatar
    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