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
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
Fabcon_Europe_Social_Bogo

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.