Power Query Sort Order Not Working (for me)

[Ericadyson]

Hi
This has to be so basic, yet I can’t understand why my sort order in PQ is not adhered to when I change the sort order in my source.

Attached is a sample file.

In PQ - sort order is Exchange then Ticker.
But when I change the sort order in the source, the result isn’t updated.
I can add or change records, no problem, but can’t the sort order to work.

This must be one of the most basic things… I’m sort of embarrassed to post this.

PowerQuerySortNotWorking.xlsx (19.7 KB)

[AntrikshSharma]

@Ericadyson Looks like some kind of bug, I duplicated the query and loaded as a new table and the sort order is as expected.

[Ericadyson]

Yes. I duplicated the query and now PQ is obeying my instructions! I wonder what caused the ‘bug’. Just shows how you have to check EVERYTHING. It was a worry because this was only an example. Thanks a lot for helping me out!

[Greg]

It’s not a bug … Power BI adds the columns to the model as they’re available in PQ, so new PQ columns are added to the end in the Model view; columns that already exist in the Model view are not reordered. If the same column order is desired in Model view as is shown in PQ, I posted a tip a last year on how to achieve this, but, in the end, it’s only a developer thing as the report consumers will see columns in alphabetical order anyway.
Greg

Tip: How to Change Column Order in Data View

Hi All. I tripped over a small nugget while refactoring a data model and wanted to share. Have you every changed a data source in Power BI and had any new columns display at the end in Data View, rather than in the order you set in the Query Editor? Well, here’s a hack I came across that seems to work: In Query Editor remove the incorrectly ordered column(s), then save changes. You will see in Data View that column(s) was/were removed as well. Now again in Query Editor remove from applied …

[Ericadyson]

Thanks Greg but I’m not sure that we’re talking about the same thing. The issue was column order. The issue was the sort order of data according to the code in PQ.

The original query was OK. But then when I changed the order of source data (say sorted by another column), the data was not reordered according to the sort instruction in PQ.

Regards Erica

[Greg]

I’m missing the issue, then. Can you attach sample PBIX and visual in question for what is currently happening as well as a mockup of the desired outcome you’re looking for?
Greg

[Ericadyson]

Hi Greg… it’s a PQ issue and I attached the spreadsheet. @AntrikshSharma kindly replied. He duplicated the query and somehow when I changed the source sort order, PQ got into action and restored the order as in the PQ.

[Heather]

@Ericadyson ,

I had a similar issue last year when I changed my source data in a few reports. It appears that once the source is changed, you have to re-establish the connection to the sort-order columns. Even though the column names aren’t changing, PQ seems to assume that the new source makes the columns different.

[Ericadyson]

Hi Everyone

I’m closing this. I deleted by query result table and re-ran the query and everything seems to be fine. So I don’t what the initial problem was but I’m up and running. Thanks everyone.