Reordering Table Columns in DAX/ Outside of Query Manager

[MatthewMcL]

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.

Thanks,

Matt

[v-chuncz-msft · Accepted solution]

@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…)…)

[LC_Health]

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.

[androo235]

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.

[Khadas]

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?