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
WillianLopes
New Member

Merge rows from different tables using Power Query

I have 2 separate tables to store products. Although it is 2 different tables, their structure is exactly the same.

 

In these tables, each line has an item and the indication to which departments it should be made available.

 

However, because there are 2 tables, the same item can appear in the tables and in each one marking different departments.

 

I would like to make a table unifying the 2 previous ones and making sure that the item that appears repeated in the tables does not come out in 2 lines but that its line is merged, pointing in the new table the departments of the 2 tables.

 

Obviously this concept of departments is not real, I just used it to try to better explain my situation.

 

Please can you help me to resolve this?

 

APAGAR.png

1 ACCEPTED SOLUTION
jennratten
Super User
Super User

Hello - here is how you can accomplish this.

SCRIPT

let
    Source = Table.Combine({Table01, Table02}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(Source, {"Product"}, "Attribute", "Value"),
    #"Filtered Rows" = Table.SelectRows(#"Unpivoted Columns", each ([Value] = "x")),
    #"Pivoted Column" = Table.Pivot(#"Filtered Rows", List.Distinct(#"Filtered Rows"[Attribute]), "Attribute", "Value")
in
    #"Pivoted Column"

jennratten_0-1679318928329.png

 

View solution in original post

2 REPLIES 2
WillianLopes
New Member

That's perfect!

jennratten
Super User
Super User

Hello - here is how you can accomplish this.

SCRIPT

let
    Source = Table.Combine({Table01, Table02}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(Source, {"Product"}, "Attribute", "Value"),
    #"Filtered Rows" = Table.SelectRows(#"Unpivoted Columns", each ([Value] = "x")),
    #"Pivoted Column" = Table.Pivot(#"Filtered Rows", List.Distinct(#"Filtered Rows"[Attribute]), "Attribute", "Value")
in
    #"Pivoted Column"

jennratten_0-1679318928329.png

 

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