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.

Solution 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/

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

Solution 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]
)

Comments

[Jacko]

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

[Jacko]

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

[Przemyslaw Remin]

Edited answer according to your explanation.

[Denis]

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

[Denis]

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