I have Power Query setup in the attached that has a sort on the dates within the query that shows the sort correctly as does the preview when you hover the mouse over the query pane, yet when the data is loaded to a table it seems to be in a different sort order until you sort the data in the output table as an additional step.
I know you can workaround this by sorting in the output and preserving the table sort, but is there something I'm missing?
What are you doing with the data in PowerQuery?
In your example workbook if I remove the Changed Type step the data is sorted correctly when it's returned to the sheet.
If posting code please use code tags, see here.
But when I do that I get a Date & Time in the output. I can see the sort is now correct, but it still needs me now to change the cell format outside of Power Query.
The idea is that all my formatting of cells and sorting is done with one step, ie. Power Query.
Like I said the preview output is correct, just not the actual output.
Move Changed Type after Sorted Rows
Still the same result for me.
I don't know what you have done - if I recreate the query from scratch, it works. See far right in the attached file.
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
That's strange.
When I open your file and try and edit your query I get this warning message.
Capture.JPG
And if I try and refresh my query in that same book I get this error message.
Capture2.JPG
Maybe it's my version of Power Query? My update button is greyed out.
That will be because I have Office 365 - sorry. Here's the M code:
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}}),
#"Sorted Rows" = Table.Sort(#"Changed Type",{{"Date", Order.Ascending}})
in
#"Sorted Rows"
Code looks exactly the same as mine.
Can you check that your copy of Excel 2016 is fully patched? A quick Google suggests there may have been a sorting bug in PQ and Power Pivot in Excel 2016, but bugs should be fixed in updates.
I'm using 2013 with PQ.
OK, well no idea, then - sorry. What happens if you recreate the query from scratch?
It's inherent in all of the queries I write. No matter how I do the sorting within the query, it is not reflected outside of the query without applying the sort to the output table and preserving the sort in the table properties.
Extra steps that I'm hoping to avoid.
Ah, OK - that explains it. In newer versions of PQ you don't need to do that, as it is done for you by default. In your older version you have to tick to preserve the sort order in table properties. This was fixed quite recently - I remember another thread about it - I think it was between myself and Dominic. Must have been 6-9 months ago - what you have just said has jogged my memory. Looks like the only way to avoid the extra step will be to upgrade.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks