Table.ReorderColumns Not Working

[Anonymous]

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?

[denisthemenace2]

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]

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]

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

[lbendlin]

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

[Icey]

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.

[edhans]

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.

[lbendlin]

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

[lbendlin]

What are you eventually trying to accomplish?

[Anonymous]

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]

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.

[edhans · 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.

[Anonymous]

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.