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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Anonymous
Not applicable

Union column order

I'm trying to use the UNION function with two summarized tables and a third table.

 

The first two tables are summarized in the exact same way, and their resulting column order is also the same. The third table, however, which has the same columns as the summarized tables, has a different column order and the values are going into the wrong places.

 

I tried changing the order of the third table in the query editor, and it did nothing. I would have tried using the append function in the query editor, but two of my tables are calculated tables and they don't show up in the query editor. I also tried specifying the column order with the SELECTCOLUMNS function, but somehow they still ended up in the wrong order.

 

How can I merge these tables properly?

10 REPLIES 10
v-ljerr-msft
Microsoft Employee
Microsoft Employee

Hi @Anonymous,

I tried changing the order of the third table in the query editor, and it did nothing. 

Based on my test, after changing the order of the table in the query editor, we still need to reload the query/table to data mode to reflect the column order changes to Data mode.

 

To reload the data mode, you can uncheck the "Enable Load" option, then recheck the option for the query/table in query editor, then Refresh your data mode after "Close&Apply". Smiley Happy

 

enableload.PNG

 

Regards

Anonymous
Not applicable

Reloding the data as you suggested did change the column position in the data view, however it is still in the wrong order after the UNION

 

just_why.PNG

 

 

Hi @Anonymous,




Reloding the data as you suggested did change the column position in the data view, however it is still in the wrong order after the UNION


According to your description above, I have tested it on my environment with the latest version of Power BI Desktop(2.48.4792.721 64-bit (July 2017)). And the UNION works all fine the the right order after reordered the column in Query Editor.

 

Could you share a sample pbix file which can reproduce this issue? So that we can help further investigate on it. You can upload it to OneDrive or Dropbox and post the link here. Do mask sensitive data before uploading.

 

In addition, I just wondering if it is possible for you to reorder the columns for the summarize table by modifying it's DAX formula. Smiley Happy

 

Regards

Anonymous
Not applicable

I don't think I can reorder using DAX, because the SUMMARIZE function always puts the summarized columns first.

Hi @Anonymous,



So could you share a sample pbix file(with just some sample/mock data) which can reproduce this issue? So that we can help further investigate on it. You can upload it to OneDrive or Dropbox and post the link here. Do mask sensitive data before uploading. Smiley Happy

 

Regards

Anonymous
Not applicable

I am sorry for the long delay. I had needed to re-work my data, and the tables were unioning properly. However, when I had need to change it again, I am getting the same issue again.

 

Here is a pbix file in which I replicated the problem (TableF)

 

https://drive.google.com/open?id=0B_6Tqj6N3i6HZnR6TG5qQ2lmLWM

From the documentation, regarding UNION, it says: Columns are combined by position in their respective tables.

 

So you could use SUMMARIZE with TableB like:

 

 

Table F =
UNION (
    SUMMARIZE ( TableA, TableA[a6], TableA[a8x], "hits", COUNT ( TableA[a5] ) ),
    SUMMARIZE ( TableB, TableB[b2], TableB[b1], TableB[b3x] )
)

 

At least the columns order is predictable.

 

Tom

 

www.powerpivotpro.com

 

 

Anonymous
Not applicable

I tried this, but similar to using SELECTCOLUMNS, the calculated column still wants to become the first column.

Interesting!

 

I tried my solution before posting and had no difficulty creating a desired column order.

 

Tom

Anonymous
Not applicable

I wish we could UNION on something less arbirary than column order. I believe APPEND in query editor uses column names.\

 

*edit* Unfortunately, I can't use append or model my data in the query editor. I had to use UNION to join my source data which is separated by month. I don't need the past months' data to refresh. If I were to APPEND those tables, even if they were flagged not to refresh they would refresh.

Helpful resources

Announcements
Sept PBI Carousel

Power BI Monthly Update - September 2024

Check out the September 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

Sept NL Carousel

Fabric Community Update - September 2024

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