Reply
Frequent Visitor
Posts: 12
Registered: ‎01-26-2017
Accepted Solution

Union two tables with different columns names

Hi,

 

I have two tables with different names for the columns but with the same meaning and I need to union the two tables to one table with the names of the columns in table A.

Do I need to create a new table (table C)? If so, how? Or is there a better way to do it?

 

Index

Opening_Date = Business_Date

Store_Name = Location

Store_Number = Location_Code

 

Table A

Opening_Date                  Store_Name                      Store_Number

1/1/2017                           New York                                  1

 

Table B

Business_Date                  Location                            Location_Code

2/1/2017                           Boston                                      2

 

Table C

Opening_Date                  Store_Name                      Store_Number

1/1/2017                           New York                                  1

2/1/2017                           Boston                                      2

 

 

Thank you,

Ohad


Accepted Solutions
Super Contributor
Posts: 1,791
Registered: ‎07-17-2016

Re: Union two tables with different columns names

Hi @Ohad,

 

In this scenario, you can also try the Append Queries option in Query Editor.Smiley Happy

 

1. Rename the three columns in Table B with the column names in Table A.

 

2. Select Table A, and choose "Append Queries as New" under Home tab in Query Editor.

 

a1.PNGa2.PNG

 

Regards

View solution in original post


All Replies
Regular Visitor
Posts: 39
Registered: ‎02-21-2017

Re: Union two tables with different columns names

[ Edited ]

use union query:

 

https://msdn.microsoft.com/en-us/library/dn802530.aspx
newTable = UNION(TableA,TableB) 

Store_NameStore_NummberOpening_Date

New York11/1/2017
Boston22/1/2017
Super Contributor
Posts: 763
Registered: ‎11-25-2016

Re: Union two tables with different columns names

That looks like an excellent solution to me.

 

If, for some reason, you prefer a Power Query solution: this video shows how you can create a rename list and combine the tables after renaming columns using the rename list. In the video it is done in Excel, but can likewise be applied in Power BI.

Frequent Visitor
Posts: 12
Registered: ‎01-26-2017

Union two tables with different columns names

@rocky09 @MarcelBeug 

Thanks for the reply.

 

I didn't mention that in each table I have a few more columns that are not relevant to table C (table A - 27 columns in total and table B - 13 columns in total) but the union can work only if the two tables are with the same number of columns, any idea?

 

Also, how do I set which column in table A to join with column in table B? (they are not in the same order).

 

 

Thanks.

Regular Visitor
Posts: 39
Registered: ‎02-21-2017

Re: Union two tables with different columns names

[ Edited ]

@Ohad

I see. But, you haven't even mentioned that you have different columns in each table.

 

If you have these tables in Excel. What I could do is  I will create a New Sheet in excel, Make the Column headings and paste the relevant columns accordingly.

 

If the data is fetching from Database. I think, you need to write a query while fetching the data.

 

Otherwise, Suppose, you want only some fields in both tables. What you can do it, Right Click on the Table --> Edit Query --> Delete the Columns which you don't want. So, Finally, you can use Union Query.

Check this LINK

 

Super Contributor
Posts: 763
Registered: ‎11-25-2016

Re: Union two tables with different columns names

It looks like you found yourself a good reaon for a Power Query solution: so rename the columns before combining the tables as outlined in my previous post.

Super Contributor
Posts: 1,791
Registered: ‎07-17-2016

Re: Union two tables with different columns names

Hi @Ohad,

 

In this scenario, you can also try the Append Queries option in Query Editor.Smiley Happy

 

1. Rename the three columns in Table B with the column names in Table A.

 

2. Select Table A, and choose "Append Queries as New" under Home tab in Query Editor.

 

a1.PNGa2.PNG

 

Regards

Frequent Visitor
Posts: 12
Registered: ‎01-26-2017

Re: Union two tables with different columns names

@v-ljerr-msft@MarcelBeug@rocky09

Thank you for the help, it's working.

Highlighted
Super Contributor
Posts: 1,791
Registered: ‎07-17-2016

Re: Union two tables with different columns names

Hi @Ohad,

 

Great to hear the problem got resolved! Could you accept the helpful replies as solution to help others who may have similar issue easily find the answer and close this thread?Smiley Happy

 

Regards

Frequent Visitor
Posts: 12
Registered: ‎01-26-2017

Union two tables with different columns names

@v-ljerr-msft

 

Done.