Forum Discussion
Column order within Power Query is not reflected in Model or Datasheet views (bug?)
I think this just has to be a bug.
Regardless of what I do, the column order that I define within Power Query is not reflected in either the Datasheet or the Model view.
Not only that, but the column orders in the Datasheet view and the Model view don't even match, which essentially leaves me with three different versions: that which is defined within Power Query, the Model version, and the datasheet version (four, if you count the field browser which is always sorted alphabetically).
In case it's relevant, my data source is an Excel spreadsheet.
I have googled this and even came across this article:
https://blog.crossjoin.co.uk/2018/12/18/power-bi-and-column-order-in-m-queries-and-dataset-tables/
....which suggest disabling and then re-enabling the "Enable Data Load" option in Power Query, but that didn't work either.
15 Replies
Hi,
I can confirm that I fixed the issue via the following steps :
1) Diasble Query Load. Ensure Query is no longer showing in the Data Model.
2) Re-enter the Query Editor, Enable the Query Load and Refresh the Query before Closing and Applying Changes.
This will load the query into the data model with column re-ordering preferences intact.
Hope this helps.
You're an absolute legend. Saved the day!
Marvellous! (Y) thanks a lot
Thank you soo much! I have been struggling with this for a long time. Microsoft should fix this though. Load times can be higher with bigger datasets.
Just to make a note for future reference where a static table source is a spreadsheet ...
. change spreadsheet
. refresh dataset on the Service
. take a copy of the dataset
. in the transform (Power Query Editor), uncheck the query 'Enable load' (right mouse menu)
. apply, close & apply the transform (Power Query Editor)
. the table should now be removed/missing from the data model
. open the Power Query Editor
. against the table query, check the query 'Enable load' (right mouse menu)
. refresh the query
. apply, close & apply
. refresh> the static table in designer should now reflect both the original spreadsheet and in Power Query
Can you share some sample data.
Here are screenshots of the 3 different versions, sorry some are a bit small but if you zoom in you should be able to see none are the same:
Final step of query:
Datasheet:
Model:
Hi TeeGee ,
Based on my research, data fields are always in alphabetical order in Dataset. It's default behavior by design. But you can find a workaroud using [Sort by other column] feature in this post: https://community.powerbi.com/t5/Desktop/Order-in-fields-pane/td-p/45442
The column order can be controlled in Power Query Editor using "ReorderColumn" function or drag the column. But the order of column is only meaningful in the Power Query Editor and will not effect the order in dataset or the field.
Best regards,
Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
