Forum Discussion
UNION dax function "merges" the column ?
Hi all,
I have a strange working of UNION() dax function.
I have two identical tables with the same columns in the same order. The only difference is about the period :
- table #1 : sales on 2024
- table #2 : sales on 2023
For example:
| employee | sales |
| employee1 | sales1 |
| employee2 | sales2 |
For each of these tables, the column "sales" is in a "decimal number" format.
I need to merge these tables, so I do :
GlobaTable = UNION (Table1, Table2)
But when I did that :
- the column "sales" is transformed in a Text format
- when I want change the format to "decimal number", I have a error message that says it's impossible to change "employee1" to a decimal format...
Strange, no ?
Do you have a tip to help me ?
Thank you in advance.
Charlie.
Thanks for this Information.
Here is a Tip for this problem.
In case of Union ( In Power BI or in SQL), the tables selected must have columns in same Order.
For Example: If you are Appending two Tables Table A and Table B with columns
Table A: Column A, Column B, Column C....
Table B: Column A, Column C, Column B....
Then before union Table A and Table B you must reorder Table B columns as per Table A or Vice-Versa.
I hope this will resolve your error.
Please let me know in case it doesn't resolve your problem.
Thanks and Regards
Shalabh Kushwaha
10 Replies
- lbendlin
Super User
UNION is not a merge, it is an append. Most likely the column order does not match between the two tables.
lbendlin Thanks for your contribution on this thread.
Hi Charlie42 ,
The UNION() function in DAX combines two or more tables with the same structure into a single table. When you use UNION(), the resulting table will have the same column names and data types as the first table in the argument list. Are you trying to change the data type of field [Sales] from Text type to Decimal Number and it got the above error message? If yes, please ensure that the field [Sales] in both tables only contains numeric values. If there are any non-numeric values (like “employee1”), you’ll need to clean your data.
In addition, you can provide some sample data in Table1 and Table2 (exclude the sensitive data) and the expected result. Later we will provide you the solution. Thank you.
Best Regards
Hi to both,
lbendlin , thank for your message. Sorry, it's a mistake in my words... my need is well to get a table with the data of Table#1 and the data of Table#2
Anonymous ,
My both tables get the information in a common database. So, I'm sure that the field [Sales] only contains numeric values.
For limiting the consumption of the memories during the auto-refresh of my report:
- I created a request to get information from 01.01.2024 to 31.03.2024 and I exclude that in the autorefresh
- I copied this request and I changed the date (from 01.04.2024 to 31.05.2024) and I keep that in the autorefresh.
- I used UNION() to get a common table.
But here, I notice that the data type of the field [Sales] in the new table is not Decimal Numeric.
If I want change, I have this error message :
I check my values in the field [Sales] and indeed, the name of the employee is inside...
I don't understand...
Hi Charlie42 : Can you please check for the value which is shown in Error message into Columns and tell us in which column does it reside.
Thanks and Regards
Shalabh Kushwaha
(For Power BI help connect: [email protected])
Hi Anonymous ,
Here the entirety of tables (3 tables are the same column in the same order) :
Regarding the value which is shown in the Error message, it can be in [Employee], [Employee (priv)], [Sales Employee], [MD Sales Employee].
I have this issue with others columns. For example, inside [Employee] I have the values of [Product Search]
Thanks for this Information.
Here is a Tip for this problem.
In case of Union ( In Power BI or in SQL), the tables selected must have columns in same Order.
For Example: If you are Appending two Tables Table A and Table B with columns
Table A: Column A, Column B, Column C....
Table B: Column A, Column C, Column B....
Then before union Table A and Table B you must reorder Table B columns as per Table A or Vice-Versa.
I hope this will resolve your error.
Please let me know in case it doesn't resolve your problem.
Thanks and Regards
Shalabh Kushwaha
