UNION dax function "merges" the column ?

[Charlie42]

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.

[ShalabhKush]

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: gbuedu@gmail.com)

[Charlie42]

Hi @ShalabhKush ,

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]

[ShalabhKush]

Here is a screenshot after using union: 

Screenshot for Sales1 and Sales2 tables with same order columns.

[ShalabhKush]

@Charlie42  please ensure that Columns are in Same Order in all the tables included in union. Otherwise result will not be as expected.

[ShalabhKush · Accepted solution]

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

gbuedu@gmail.com

[Charlie42]

Hi,

I just checked and indeed with this view (see below) I can get that the order of the columns are not the same even if the the requests to get data from source are the same.

So, I should use SELECTCOLUMNS() in my UNION(). Right ? Maybe is there easier way to do that ?

[EDIT] I can use Table.ReorderColumns in my query. I'll try this. Thank.

Thank you for your messages.

[ShalabhKush]

Yes, if you are a pro DAX user. Then you can achieve by using SELECTEDCOLUMNS. In the below format. CombinedTable = UNION ( SELECTCOLUMNS ( Table1, "Column1", [ColumnA], "Column2", [ColumnB] ), SELECTCOLUMNS ( Table2, "Column1", [ColumnX], "Column2", [ColumnY] ) ) Thanks and Regards Shalabh Kushwaha

[Charlie42]

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

@v-yiruan-msft ,

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

[v-yiruan-msft]

@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

[lbendlin]

UNION is not a merge, it is an append.  Most likely the column order does not match between the two tables.