Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anthony_Nguyen
New 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 

 

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

1 ACCEPTED SOLUTION
AlienSx
Super User
Super 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

View solution in original post

2 REPLIES 2
AlienSx
Super User
Super 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

It works like Charm. Thank you so much.

I am trying now to test with more complex data table

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors