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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Cyclops1717
New Member

Merge 2 tables with same column name and duplicated products

Hi everyone,

 

I am struggling merging 2 tables (from 2 different files) into 1. Despite digging through this forum and a few others I still cannot find a solution.

Here is my case:

I have 2 files, each file has a Table with the same column headers. The data is a set of products and their characteristics.

My issues is that the same product ID might be in both table. For those duplicated products, there might be data points missing.

(See example below, where Product 5 is missing the Color info in Table 1, but is in Table 2, while Price Range is in Table 1 but not in Table 2.)

 

I did a simple "Merge Queries as New", picked both Tables, selected my Product Name Column as my common column, and Full Outer, assuming it would bring all products from both tables and merge the rows those tables have in commun... but I was wrong.

The result is:

  • Only the rows from the 1st table selected are in the Merged Table. None of the products from Table 2 that are not in Table 1 were merged
  • If I had a missing data point in Table 1, but I have it in Table 2, it was not merged either

So that is pretty much a major fail. LOL

 

I went through https://radacad.com/append-vs-merge-in-power-bi-and-power-query 

I also tried to Append both tables, but I am not finding a solution to "merge" to characteristics of the products common to both Tables.

 

Any help would be really appreciated.

 

Example:

 

Table 1

ProductColorSizePrice Range
Product 1RedSmall1
Product 2BlueLarge3
Product 3RedXL4
Product 4YellowLarge4
Product 5 XL2
Product 6BlackXL2
Product 7 Medium2
Product 8OrangeMedium1
Product 9PurpleMedium1

 

Table 2

ProductColorSizePrice Range
Product 5YellowXL 
Product 6BlackXL 
Product 7White 2
Product 8OrangeMedium1
Product 10PurpleMedium3
Product 11WhiteSmall1
Product 12WhiteXL2
Product 13PurpleMedium2
Product 14OrangeMedium1

 

The goal is to fill in the blanks and combine all products in a single table:

Merged Table from Table 1 and Table 2

ProductColorSizePrice Range
Product 1RedSmall1
Product 2BlueLarge3
Product 3RedXL4
Product 4YellowLarge4
Product 5YellowXL2
Product 6BlackXL2
Product 7WhiteMedium2
Product 8OrangeMedium1
Product 9PurpleMedium1
Product 10PurpleMedium3
Product 11WhiteSmall1
Product 12WhiteXL2
Product 13PurpleMedium2
Product 14OrangeMedium1

 

 

1 ACCEPTED SOLUTION
Cyclops1717
New Member

@Imrans123 & @jbwtp Thank you both for your help! I ended up appending and filling the gaps in Excel with a "if empty, fill down" function, which solved 80% of my gaps. Then manually filling the remaing gaps. 😞

Probably the ugliest process ever, but it was only 500 rows of data.

I don't know why I thought there was an easy-button for that LOL

View solution in original post

4 REPLIES 4
Cyclops1717
New Member

@Imrans123 & @jbwtp Thank you both for your help! I ended up appending and filling the gaps in Excel with a "if empty, fill down" function, which solved 80% of my gaps. Then manually filling the remaing gaps. 😞

Probably the ugliest process ever, but it was only 500 rows of data.

I don't know why I thought there was an easy-button for that LOL

Imrans123
Advocate V
Advocate V

Firstly, I would start with a base table. I would append query as new, select the two tables and then remove duplicates from the Product column. This would ensure all the products from both tables are there but no duplicate products. 

 

MY next challenge would be addressing the blank attributes. (i.e. Color, Size, Price and Range.)

 

On appended table, I would merge query with table1 (left outer) on product and then expand Color as Color1, Size as Size1 and so on. 

Do a conditional column called color2, if Color is blank then Color1 else Color. 

another column size2, if Size is blank then Size1 else size 

and so on

 

This ensures all the attributes from table 1 are included in the columns color2, size2 ad so on. Now I want to bring in attributes from Table 2

 

 

I would merge query with table 2 (left outer) on product and then expand Color as Color3, Size as Size3 and so on. 

Do a conditional column called color4, if Color2 is blank then Color3 else Color2. 

another column size4, if Size2 is blank then Size3 else size2 

and so on

 

After doing this Color4, Size4 and so on should have attributes from both table 1 and 2 while the appended table will have all unique products from both tables.

 

Rename Color4, Size4... to Color, Size and so on. 

 

jbwtp
Memorable Member
Memorable Member

Hi @Cyclops1717,

 

If you do not have duplicates except where Price Range it set n one table, but not in another than Table.Combine those tbales and then filter off blanks in the Price Range field.

 If you do have the duplicates, than still Table.Combine and than Table.Group applying your decision logic to the Price Range field (which can be as simple as List.Max).

 

P.S.

Sorry, just ralised that you may have gaps in the other fields too, except the Product field. In this case use the later approach, group by Product and use List.Max on other columns. I think this would still be quicker than merging tables and creating new/deleting redundant columns.

 

 

Cheers,

John

does this mean merging actually does work?

Could someone refer to this and tell me what's wrong with merging?
https://community.fabric.microsoft.com/t5/Desktop/The-sequence-of-events-to-create-the-final-table-d...

 

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors
Top Kudoed Authors