Merge 2 tables with same column name and duplicated products

[Cyclops1717]

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

Product	Color	Size	Price Range
Product 1	Red	Small	1
Product 2	Blue	Large	3
Product 3	Red	XL	4
Product 4	Yellow	Large	4
Product 5		XL	2
Product 6	Black	XL	2
Product 7		Medium	2
Product 8	Orange	Medium	1
Product 9	Purple	Medium	1

Table 2

Product	Color	Size	Price Range
Product 5	Yellow	XL
Product 6	Black	XL
Product 7	White		2
Product 8	Orange	Medium	1
Product 10	Purple	Medium	3
Product 11	White	Small	1
Product 12	White	XL	2
Product 13	Purple	Medium	2
Product 14	Orange	Medium	1

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

Merged Table from Table 1 and Table 2

Product	Color	Size	Price Range
Product 1	Red	Small	1
Product 2	Blue	Large	3
Product 3	Red	XL	4
Product 4	Yellow	Large	4
Product 5	Yellow	XL	2
Product 6	Black	XL	2
Product 7	White	Medium	2
Product 8	Orange	Medium	1
Product 9	Purple	Medium	1
Product 10	Purple	Medium	3
Product 11	White	Small	1
Product 12	White	XL	2
Product 13	Purple	Medium	2
Product 14	Orange	Medium	1

[Cyclops1717 · Accepted solution]

@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]

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]

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

[YoungLearning]

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...