Union column order

[Anonymous]

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?

[v-ljerr-msft]

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

Regards

[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

[v-ljerr-msft]

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. 

Regards

[Anonymous]

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

[v-ljerr-msft]

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. 

Regards

[Anonymous]

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

[P3Tom]

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]

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

[P3Tom]

Interesting!

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

Tom

[Anonymous]

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.