Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Fia123
Helper II
Helper II

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.

1 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 🙂

 

View solution in original post

7 REPLIES 7
Ballard-Atkins
Frequent Visitor

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
Super User
Super User

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





If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!




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 🙂

 

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

Hi, 

 

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

great





If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!




Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.