UNION function mixes columns

[Fia123]

Hi, I know that this subject has been discussed earlier in this forum, but I still don't know how to solve my issue.

I am making a PWBI model based on data from two different ERP systems.  I have many examples where I have to combine two tables with different number of columns.

I tried to solved this problem by manipulating the tables before I am making the new table with used of the UNION function, ie add a new column to the table where it is missing and sets the values to "0" or blank (New column = 0). BUT it is not working, in the new table the columns are mixed.

I  have used the UNION function (Power BI DAX) before with big success - so why does it not work this way?

I have few millions of row in my tables - and I used Sql queries to get the data.

[Ballard-Atkins]

I found the following helpful in how JOINs work: https://stackoverflow.com/questions/38549/what-is-the-difference-between-inner-join-and-outer-join

I had a similar problem, I think.

I join two tables (TableA, TableB) that both have unique timestamps, the relationships/JOIN in PWBI didn't connect them as I wanted, so I created a new table (TableAll) with ALL the values from both TimeStamp columns, then linked TableA:TimeStamp to TableAll:TimeStamp, and TableB:TimeStamp to TableAll:TimeStamp. 

This way, when I use TableAll:TimeStamp as an axis, I can then reference data from both TableA and TableB.

[vanessafvg]

can you use selectcolumns dax function to list the order it should go in?

[Fia123]

Do you mean, first making two new tables by use of Selectcolumns - and afterwards combined those two with use of UNION?

[Fia123 · Accepted solution]

I used Selectcolumns first  and then the UNION function - and it worked great!!!!!! 

This is a much more tidy way to do it - it provides a clear distinction between extrated and transformed data.

Thank you very much - a learn something new 🙂

[ctedesco3307]

Can you supply the syntax for this? Thnk you @Fia123 

[luxpbi]

Hi, 

I have the same issue, can you please explain how you solved it?

[vanessafvg]

great