How to combine tables and create a new one

[Anonymous]

I have two tables that I want to combine in power BI. 

table 1:

ID	Color1		 Color2
1	red	    blue
2	black   brown	
3	yellow	pink

table 2:

ID	Car Model		Color
1	BMW				Black	
2	Honda			Brown
3	Mazda			White

Combined Table:

ID	   Color1	   Color2   ID		    Car Model    Color		Data Source
1	    red	        blue    Blank		Blank	     Blank 		table 1
2	   black		brown	Blank		Blank		 Blank		table 1
3	   yellow		pink	Blank		Blank		 Blank		table 1
Blank   Blank		Blank	1			BMW			 Black		table 2
Blank	Blank		Blank	2			Honda		 Brown		table 2
Blank	Blank		Blank	3			Mazda		 White		table 2

Can anyone please help me if we can merge two tables and create a combined table like above? I could do this with out PBI environment but I my data needs to be automated.

[Anonymous · Accepted solution]

Hi @Anonymous ,

Step1:

Since there can't be duplicate column name in the table, you will need to rename the "ID" in table2 as "ID2".

Step2:

Add custom column for both tables to mark then as table1 and table2.

Stpe3:

Append two tables as new.

https://docs.microsoft.com/en-us/power-query/append-queries 

Best Regards,

Jay

[Anand24]

Hi @Anonymous ,

Here's what I did:

In Power Query Editor:

Step 1: Add column "Custom1" in Table1 with static value = 1

Step 2: Add column "Custom2" in Table2 with static value = 2

Step 3: Perform "Merge As New" operation for (Full Outer Join) both tables on Custom1 = Custom2

Step 4: Add an additional conditional column for identifying data source

Step 5(Optional): Remove Custom1 and Custom2

PFA pbix. Go to Power Query and view the steps.

Give a Thumbs Up if this post helped you in any way and Mark This Post as Solution if it solved your query !!! Proud To Be a Super User !!! LinkedIn