Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
TeeGee
Helper II
Helper II

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.

 

 

14 REPLIES 14
garythomannCoGC
Impactful Individual
Impactful Individual

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 

I did this and all my measures for that table are gone, cant even undo the process, so beware!

Exactly! But luckily i did copy all the measures every time i created one

this did the trick. such a hassle though!

kagy100
Advocate II
Advocate II

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.

 

Marvellous! (Y) thanks a lot

You're an absolute legend.  Saved the day!

amitchandak
Super User
Super User

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:

CBF1-PowerQuery.png

 

Datasheet:

CBF2 Datasheet.png

 

Model:

CBF3 Model.png

 

 

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.

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.

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

 

I think you must misunderstand what I'm saying, because this simply is not true.

 

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

 

> The only view in Power BI Desktop where you can see the correct column order is the Data view.

 

Dong, I see your name contains "Community Support Team", do you work for Microsoft?  If so, I think you should forward this thread to your manager, because this is clearly a bug, perhaps it only occurs rarely, but it's a pretty big deal so I think the developers would like to have access to a situation where it is happening to help them diagnose the underlying problem.

Hi @TeeGee ,

 

I apologize for misunderstanding your situation, Now I have resized my answer:

 

Firstly I think you have konw that the column order in Model View and File Pane will be in alphabitbetical order and can not be effected by order in Power Query Editor.

 

So the left problem is the order in Data View and Power Query Editor, The article you provided in the first mentioned that:

 

You can control the order of columns in a table in the Power Query Editor but it’s not completely straightforward to get any changes to column order you make in your queries to be applied to the order of the columns in a table after it has already been loaded into a dataset.

 

The workaround in this article works fine on my side.

 

1. Change the column order in Power Query Editor

2. Apply changes

3. Disclick "enable load" and Continue
(Notice that will break any visuals that use this table any lost all measures or calculated columns associated with this table.)

4. Apply changes

5. Click "enable load"

6. Apply changes

7. Refresh the data.

 

I guess this workaround have no effect because you miss the step 4 ?

 

The reason why data view cannot read the order defined in Power Query Editor automatically seems hard to understand , If the workaround still useless on your side, We suggest you to open a ticket here for help if you are a pro user: https://powerbi.microsoft.com/en-us/support/

 

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.

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.

> I guess this workaround have no effect because you miss the step 4 ?

 

Well yes, because that will break any visuals that use this table any lost all measures or calculated columns associated with this table.

 

It's fairly useless to be able to reorder columns in PQ if it isn't reflected anywhere else in the model.  It's mind boggling that this standard behavior isn't available in Power BI.

Hi @TeeGee ,

 

The previous workaround has some problem, you can try the following.

 

1. Open Power Query Editor

2. Edit the column order

3. change the table name to such as table1-copy (DO NOT APPLY CHANGE)

4. create a blank query, name as the previous table name (and make query = table1-copy)

5. apply the changes

6. check the visual about this table, if anything loss, do not save the file

7. each time change the column order, do the 1-6 again. keep the origin table and delete the blank query

 

25.PNG

 

Best regards,

 

Community Support Team _ DongLi
If this post helps, then please consider Accept it as the solution to help the other members find it more

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.