Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Ohad
Frequent Visitor

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

1 ACCEPTED SOLUTION
v-ljerr-msft
Employee
Employee

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

12 REPLIES 12
Anonymous
Not applicable

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
Advocate I
Advocate I

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
Frequent Visitor

@v-ljerr-msft@MarcelBeug@rocky09

Thank you for the help, it's working.

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

@v-ljerr-msft

 

Done.

v-ljerr-msft
Employee
Employee

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

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
Frequent Visitor

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

@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

 

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.

Specializing in Power Query Formula Language (M)
rocky09
Solution Sage
Solution Sage

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

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.

Specializing in Power Query Formula Language (M)

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.