The column order in Data table isn't the same as in Power Query!

[current_thing]

Hello friends,

I've been googling for a solution to no avail. I want the order of columns in data table view to be the same as it is in Power Query Editor. How can I force Data Table to display columns in the same order? It seems that the data table view is sorting columns by alphabet.

The data source is a csv file.

Thanks!

[Banana5kin]

Resurrecting an old query which I have found the solution for (as I was also impacted)

Using the scenario that you have 2 tables with the values Name, Age and City (in different orders)

Create a new table, using the below DAX will reorder the columns.  Repeat for the 2nd table changing the table name.  And then using the union function - New_Sorted_Tickets = UNION('Table1_Aligned','Table2_Aligned') you will have expected output. 

Table1_Aligned =
SELECTCOLUMNS(
Table1,
"Name", [Name],
"Age", [Age],
"City", [City]
)

[robindlc]

I found really disappointing being able to move columns in Power Query to the left or right... and then having no effect in Power BI... not allowing something as simple as UNION of 2 tables because the columns are not in the same order...

Since this is absolutely insane, I assume the problem should be mine... any ideas?

[Banana5kin]

See my post to the original message with the solution

[ToddChitt]

What problem are you trying to solve with the specific ordering of columns in Power BI?

[robindlc]

I was triying to add one table to another... finally as suggested above I used APPEND in query instead of UNION.
(this brings to me into a nice problem with incremental refresh, but it is another hiostory, solved already)
Thanks

[DarkMoonTiger]

Resurrecting an older topic, I ran into this today.  Trying to figure out if this is still an issue or I have an outdated version?

[ToddChitt]

In reading through some of the post answers, they seem to have strayed a bit from the original topic, that the column order in Power Query is different than that of the Desktop designer. The former sets column order based on the discovery of the source data, while the latter is alphabetical. 

Always has been, probably always will be. 

Get used to it.

You would have better luck asking traffic engineers to make stop signs six sided instead of eight sided. I consider it a lost cause, unfortunately.

[paulfromhouston]

if your table in Power Query is the right order, then you may use Append query to merge tables instead of using UNION. 

[jefferson_rand]

Same issue EXCEPT my columns in Data view in PBIX are not sorted alphabetically.  Rearranging the columns in Power Query has ZERO effect on the order displayed in Data view in the Data Model.  Worse, this is creating HAVOC in my Tabular Editor scripts, which assume a controllable and consistent colum order and are ordered as in the Data View, not as in the Power Query output.  This seems a BUG.  Any other ideas why this is happening?  I've not tried to delete and resource the table, since I've built measures, relationship etc. and I don't want to lose that.

[jefferson_rand]

The only solution I found is to disable load to model, then re-enable the load then repair and recreate the measures, relationships, visuals, etc!  ☹️

[NBurnat]

This is insanity - just spent 30 mins reorganising columns in the editor and it didn't translate to the table in the data load... I have about 50 columns so really need them organised correctly.  I can't disable and re-enable load, there are multiple calculated tables running of the table... ugh

[Anonymous]

Hi @current_thing,

In fact, the current power bi data model table does not include row and column index.
If you want to keep the query table sorting orders, you can add an index to your table to remark the current order and use it as 'sort order column' to help keep table structure in the data model.

Regards,

Xiaoxin Sheng

[ortizde]

Hi @v-shex-msft,

Preview

Can you please elaborate on how "you can add an index to your table to remark the current order and use it as 'sort order column' to help keep table structure in the data model"?  

Thank you,

D

[ToddChitt]

You cannot change the order of columns in the Field well in Power BI Desktop. Yes, it is alphabetical.

I agree with you, I hate it. 

[current_thing]

Once again $msft is unable to live up to the even low expectation 🙄

[ToddChitt]

Have you tried voting on the idea at the Ideas forum?

Microsoft Idea (powerbi.com)

This item has all of 10 votes. There are ideas with upwards of 7,000 that are still "Under Review".

[jnixon]

Thank you, OK, I voted, but this is really a bug! I'm going to put in a support ticket and suggest that they fix it. I can't imagine what the benefit is to the current system, but there are plenty of problems with it.

[ooptennoort]

Can I ask, what was the response? Status so far?