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 

 

NoTypeCost
1E11
2E22

 

Look up table 

 

TypeCode
E1A
E1B
E2C
E2D
E2

E

 

Expected results

 

 

NoTypeCostCode
1E11A
1E11B
2E22C
2E22D
2E22E

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

  • 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_Nguyen's avatar
      Anthony_Nguyen
      New Member

      It works like Charm. Thank you so much.

      I am trying now to test with more complex data table