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

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
MatthewMcL
Frequent Visitor

Reordering Table Columns in DAX/ Outside of Query Manager

Hello,

 

I have three tables that I had created outside of the Query Editor, through "New Table" (Calculated Tables). I have calculated columns here that I created using DAX. Now that I have these three tables created, I want to union them all. Even though these tables have the same names, when I do UNION=(State1,State2,State3). The data does not Union into the correct columns. While I would assume that the system would recognize the name match and union them correctly, I think the UNION is just aligning the columns by order. The tables themselves do not have the correct column orders from table to table which you can see below. Is there anyway I can easily reorder these columns within these tables? I would prefer to avoid created new "alt" tables that just reorder the columns before the union.

 

Change Cat.PNG

 

Thanks,

Matt

 

1 ACCEPTED SOLUTION
v-chuncz-msft
Community Support
Community Support

@MatthewMcL,

 

To get the table as requested, we can use SELECTCOLUMNS Function (DAX) to reorder columns. For example:

Table =  UNION(SELECTCOLUMNS(State1, Name1, Field1, Name2 , Field2…), SELECTCOLUMNS(State2, Name1, Field1, Name2, Field2…)…)

Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

4 REPLIES 4
v-chuncz-msft
Community Support
Community Support

@MatthewMcL,

 

To get the table as requested, we can use SELECTCOLUMNS Function (DAX) to reorder columns. For example:

Table =  UNION(SELECTCOLUMNS(State1, Name1, Field1, Name2 , Field2…), SELECTCOLUMNS(State2, Name1, Field1, Name2, Field2…)…)

Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

This is a terrible way to do things, especially when I'm calculating tables with lots of columns.
It would be a nice interim fix if you could add an argument to UNION() that lets you match on column name instead of just column order. Considering that you cannot realisticly change column order a lot of the time, you could at least make sure the column names match and go from there.

This is not a solution. It is at best a workaround. The solution is some kind of drag and drop feature, perhaps leveraging this function, but, it should be under the hood. I and I suspect everyone who's come across this surprising limitation in Power BI model layer, want to drag and drop. Maybe it's a trickier programmng challenge that it seems, but, this is Miscrosoft, they should have fixed something this basic and important by now. Having columns in an order that makes sense to the modeller is very helpful and having to repeatedly breakoff to write a new SELECTCOLUMNS to put them in a better order isn't satisfactory.

I've just tried it out, it works but not in a way that helps. Yes you can return a table with all the columns re-arrranged in any order you like. But, to edit formulae you still have to go back to the original table. Basically it's not much use as a solution for the working modeller who wants to put columns as they are working with editable formulae into an order that assists thinking and makes sense to them. This non-solution does not do that. Please tell me I've missed something and it does, I'd be very happy.

The problem is that the ordering only works once. If you add another columne in the middle or rearange the existing columns afterwards, the first given order will remain and new columns are created at the end. For the right order you have to copy the code, delete the table and create a "new" table evertime.

The order of the data table is important for me beacause it is the table you will get if you use "show data point as table". our users want to use this feature to have more details if needed, without killing the whole layout of the report.

any tips?

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

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

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

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