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
Anonymous
Not applicable

Table.ReorderColumns Not Working

Hello,

 

I tried using the Table.ReorderColumns in Power Query and can see that it works in the Query Editor window. But when the table is loaded, the columns in "Data View" are in some other order and not the one I specified.

How does this reorder columns work then?

1 ACCEPTED SOLUTION

It is even worse than that. If you load the data, then in the data view they are in alphabetical order. But if you go back into Power Query and add new columns, they are added to the end, not in alphabetical order.

 

The only place the Reorder Columns feature is useful is when you are using Power Query in Excel and loading it to a worksheet as a table. That will retain the order.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

View solution in original post

12 REPLIES 12
denisthemenace2
Regular Visitor

Try this:

 

  • Disable load of the table in PowerQuery Editor
  • Apply Changes
  • Go back to PowerQuery Editor and enable load for the table again

Now you columns should be in the order you put them in with 'Table.ReorderColumns'

 

Best, Denis

Wilhelm
Advocate II
Advocate II

I have two large (read: more than 15 columns) tables that I need to run EXCEPT on to find out what has been added. EXCEPT (like UNION and INTERSECT) requires the columns to be in the same order.

 

Yes, I know I can use SELECTCOLUMNS, but what is Table.ReorderColumns for if not to re-order the columns?

Anonymous
Not applicable

This also disrupts a UNION function when trying to combine two tables. The order matters and re-ordering in power query does not resolve. 

That's not really a problem. You can use SELECTCOLUMNS() before the UNION().

Icey
Community Support
Community Support

Hi @Anonymous ,

 

Please try to unload the table and re-sort the columns. Then, load the table again. Please check if this works.

 

 

Best Regards,

Icey

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Be aware that if you do this @Anonymous it will wreck existing measures, visuals, relationships, and other settings like Sort By Columns.


I would do this only as a last resort if you really cannot stand the columns to be in a different order than you want. I would recommend just leaving it be. Intellisense fills out the names, it is in alphabetical order in the Field Listing in the report and model view, and you shouldn't be spending a lot of time in the Data view because creating Calculated Columns generally isn't a best practice, and for 1-2 per model, column order shoudn't be that impactful.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting
lbendlin
Super User
Super User

Raise an idea or an issue for this request.  I see your point and will vote for it.

lbendlin
Super User
Super User

What are you eventually trying to accomplish?

Anonymous
Not applicable

In Data View, I want the columns to appear in a specific order that i mention in Table.ReorderColumns.

This order is important because some of the columns make sense only when they're together.

It also makes it easier for me to work with the data.

lbendlin
Super User
Super User

It is only (mildly) useful within the scope of the Power Query steps.  In general it is pretty useless. 

 

In Power BI columns are sorted alphabetically, for better or worse.  You can influence that by renaming columns.

It is even worse than that. If you load the data, then in the data view they are in alphabetical order. But if you go back into Power Query and add new columns, they are added to the end, not in alphabetical order.

 

The only place the Reorder Columns feature is useful is when you are using Power Query in Excel and loading it to a worksheet as a table. That will retain the order.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting
Anonymous
Not applicable

It is not in alphabetical order in Data View. Just some random order.

I think you meant the list of columns we see in the fields section - it is alphabetical there.

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.

Top Solution Authors
Top Kudoed Authors