Power Query Append GroupBy problem

[haas67]

Hi, I'm trying to Append three tables and use GroupBy to combine rows.
- the tables each contain a column "Company". This is the GroupBy column
- Each table contains a different second column
- Not every row exists in each table.
How can I achieve the goal of creating the desired table in Power Query?

Append.xlsx
	A	B	C	D	E	F	G	H	I
1	Company	Country		Company	Type		Company	Website
2	Apple	USA		Intel	Technology		Microsoft	www.microsoft.com
3	Intel	USA		ASML	Technology		Intel	www.intel.com
4	Shell	Netherlands		McDonalds	Food		Siemens	www.siemens.de
5	ASML	Netherlands		Exxon	Oil		Abn-Amro	www.abnamro.nl
6	Siemens	Germany		BMW	Auto		BMW	www.bmw.de
7
8
9	Append					Desired
10
11	Company	Country	Type	Website		Company	Country	Type	Website
12	Apple	USA				Apple	USA
13	Intel	USA				Intel	USA	Technology	www.intel.com
14	Shell	Netherlands				Shell	Netherlands
15	Kongsberg	Norway				ASML	Netherlands	Technology
16	Siemens	Germany				Siemens	Germany		www.siemens.de
17	Intel		Technology			McDonalds		Food
18	ASML		Technology			Exxon		Oil
19	McDonalds		Food			BMW		Auto	www.bmw.de
20	Exxon		Oil			Microsoft			www.microsoft.com
21	BMW		Auto			Abn-Amro			www.abnamro.nl
22	Microsoft			www.microsoft.com
23	Intel			www.intel.com
24	Siemens			www.siemens.de
25	Abn-Amro			www.abnamro.nl
26	BMW			www.bmw.de
Sheet1

 

[Alex Blakenburg]

I am sure that there are better ways of doing this but this is how I did it.

1. Load the 3 tables as Connection only
2. Append all 3 queries
3. Remove all columns except Company
4. Home > Remove Rows > Remove Duplicates - You know have a table of unique companies across all 3 tables.
   
   
5. Using that the query in 3, merge that query to the other 3 tables using a Left outer join.
6. Expand the 3 new columns (that will have the word Table in the column) and select only the additional column from each eg Country, Type & Website
7. Close & Load

Let me know if you need more detail.

 

[haas67]

I can follow the first 4 steps.
But don't understand what you do in steps 5 and 6.

 

[Alex Blakenburg]

Right click on the final append query, then merge Table1 left join on company.
Then go to merge again and link in table2, then merge again and link in table3

 

[haas67]

Yes, it works! Thanks a lot.

 

[Alex Blakenburg]

Thanks for letting me know. Glad I could help.