Union two tables with different columns names

[Ohad]

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

[Anonymous]

Hi @Ohad

The simplest way I found was to use the DAX - Union (Selectcolumns(), Selectcolumns())

Here you can select the tables, individual columns as well as rename those columns on the fly for the Union funciton to combine the dataset together.

Cheers,

M

[thangkt]

The solution for your problem can be released by using Union and Selectcolumns Function: 

Union(
selectcolumns(Table A,"Opening_Date", Table A[Opening_Date], "Store_Name", Table A[Store_Name],"Store_Number", Table A[Store_Number]),
selectcolumns(Table B,"Business_Date", Table B[Business_Date], "Location", Table B[Location],"Location_Code", Table B[Location_Code])
)

You should try this way.

Good luck! 🙂

[Ohad]

@v-ljerr-msft@MarcelBeug@rocky09

Thank you for the help, it's working.

[v-ljerr-msft]

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?

Regards

[Ohad]

@v-ljerr-msft

Done.

[v-ljerr-msft · Accepted solution]

Hi @Ohad,

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

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.

Regards

[ayami123]

I want to use union because I'm not allow to edit and create new excel file.  =_=
that's why i want to use union
is there anyway I can use Union in PowerBI? 

[Ohad]

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

[rocky09]

@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

[MarcelBeug]

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.

[rocky09]

use union query:

https://msdn.microsoft.com/en-us/library/dn802530.aspx

newTable = UNION(TableA,TableB) 

Store_NameStore_NummberOpening_Date

New York	1	1/1/2017
Boston	2	2/1/2017

[MarcelBeug]

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.