Duplicate rows based on the values from a table

[Anthony_Nguyen]

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

[AlienSx · Accepted 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

[Anthony_Nguyen]

It works like Charm. Thank you so much.

I am trying now to test with more complex data table