Forum Discussion
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
Hi Ohad,
In this scenario, you can also try the Append Queries option in Query Editor.:smileyhappy:
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
12 Replies
thangktAdvocate 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! :)
- rocky09
Solution Sage
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
Community Champion
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.
v-ljerr-msftMicrosoft Employee
Hi Ohad,
In this scenario, you can also try the Append Queries option in Query Editor.:smileyhappy:
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
ayami123Helper I
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?
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
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.
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?:smileyhappy:
Regards
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
