Trying to Reorder Columns in Data View in Power BI

Question

Is there a way I can reorder columns in 'Data View' within Power BI? I tried doing it in Power Query first but when the data loads into the table, the columns automatically rearrange.

Spontaneous

Answer 1

Edit after comment. There is easy fix to enforce column order just as in Power Query:

1. In Power Query Editor > Disable Query Load. Close and Apply.
2. Open the Query Editor again, enable the Query Load. Refresh the Query. Then Close and Apply.

---

Answer to misunderstood question.

This may be interesting solution in M PowerQuery. The function below let you reorder columns by only stating few columns from the whole set of columns. Add this in blank query and rename it to FnReorderColumnsSubset.

(tbl as table, reorderedColumns as list, offset as number) as table =>
    Table.ReorderColumns
    (
        tbl,
        List.InsertRange
        (
            List.Difference
            (
                Table.ColumnNames(tbl),
                reorderedColumns
            ),
            offset,
            reorderedColumns
        )
    )

Use it as this:

= FnReorderColumnsSubset( Source, { "Region", "RegionManager", "HeadCount" }, 0 )

Found it here: https://datachant.com/2017/01/18/power-bi-pitfall-4/

Davies

The question relates to Table Data View, not to M. So you're not answering the correct question. – Kujawa

Edited answer according to your explanation. – Davies

NB! This solution works, BUT will completely destroy all your manual hierarchies for this table (implicit but very unpleasant). – Tenpenny

Answer 2

It is extremely stupid way, but it is working - i found it by accident:

1. In edit query view remove the column, then save changes. You will see that in data view that column was removed as well.
   
2. Now again in edit query view remove from applied steps the action that removed the column. Save it again.
   
3. You will see that removed previously column was added to the end of the table.
   
4. This way you can arrange your columns to have it the way you want it in data view.

Hope it helped.

Illogic

The only thing is that this doesn't work for tables created on the Table View – Kujawa

Another bad implicit thing - it will damage your hierarchies, if removed (step 2) field is included it – Tenpenny

Answer 3

I don't know that you can rearrange an existing table, but if you re-create it as a new table, you can pick the order you want.

NewTable =
SELECTCOLUMNS (
    OldTable,
    "Column1", OldTable[Column1],
    "Column2", OldTable[Column2],
    "Column3", OldTable[Column3]
)

Shelton

Answer 4

I think most here have misunderstood the problem, except @Jacko. So far as I know it is now possible to re-arrange columns in Power Query and load to the model and the table will load in the column order you specified in PQ. The problem is in dataview in the modelling layer of PBi. Here you can add many calculated columns, but, any new column you add is always placed at far right and can't be moved. Yes, I know about SELECTCOLUMNS but it isn't a solution as the new table does not have the editable formulae.

A solution is a drag and drop feature of some sort. PBi users are still waiting for it despite the problem being flagged in MS Forums some years ago. No progress TIKO other than the limp SELECTCOLUMNS solution.

Leafstalk

Incidentally, in Excel PowerPIVOT data model, which is essentially the same as the Power BI modelling layer (we're talking desktop here), you can move calculated columns to anywhere you need them. Why not in Power BI? So, why don't I work in Excel then, well, because in most other respects Power BI is better and certainly works better in a networked environment (except that you can't open a pbix file directly from sharepoint, well, at least not in my environment anyway.....) – Leafstalk