Forum Discussion
UNION function mixes columns
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.
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 :-)
7 Replies
can you use selectcolumns dax function to list the order it should go in?
Do you mean, first making two new tables by use of Selectcolumns - and afterwards combined those two with use of UNION?
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 :-)
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.
