• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Power Query Sorting

aggie81

Member
When I do a refresh on Power Query the Sort I had in Excel goes away. If I do the Sort in Power Query it doesn't import that Sort to Excel. I don't think that Power Query imports or loads the information the same way twice.
Is this just the way things are or am I missing something simple?
Thanks,
Lee
 

teylyn

New Member
Hello,

If you load a Power Query into Excel and then sort the result, refreshing the query will revert to the sort order that the query returns.

You can edit to query and set the desired sort order in Power Query.

If that does not work for you, please post the M code of the query for inspection.

cheers, teylyn
 

aggie81

Member
I am not sure if this is what you need but here it is.
I know just enough to get the information I want but my knowledge ends quickly on how to format. Should the "sorts" be the last thing done to the data? If I do the refresh in Power Query it holds and is the same each time but when I load it to Excel the data is unsorted.
This one is from an Excel file but I have the same problem when extracting data from SQL server files.
I use the information for sales analysis and production forecasting.
Thank you for looking at this.
Lee

let
Source = Excel.Workbook(File.Contents("J:\2015\2015 Production Schedule.xlsx")),
SPRG15_Table = Source{[Item="SPRG15",Kind="Table"]}[Data],
#"Removed Other Columns" = Table.SelectColumns(SPRG15_Table,{"ItemNo", "Description", "# of Crops", "Finish Units", "SP Orders BOOKINGS", "Planted", "Sell Date", "Finish Plant Date", "Crop #", "Planted Date"}),
#"Sorted Rows" = Table.Sort(#"Removed Other Columns",{{"Description", Order.Ascending}}),
#"Changed Type" = Table.TransformColumnTypes(#"Sorted Rows",{{"Sell Date", type date}}),
#"Sorted Rows1" = Table.Sort(#"Changed Type",{{"ItemNo", Order.Ascending}, {"Sell Date", Order.Ascending}})
in
#"Sorted Rows1"
 

teylyn

New Member
The last step in your query is a sort by ItemNo and Sell Date as a secondary sort criterion. So, the data will be sorted by ItemNo and for identical ItemNo the data will be sorted ascending by sell date. Is that what you want to achieve?
 

teylyn

New Member
I've run this query a few times with some dummy data I made up. I get the correct sort order when the query refreshes. What do you get? When you look at the data preview in the Power Query editor with highlight on the last action, does the data have a different order than when loaded to the workbook?
 

teylyn

New Member
The M code in the Power Query file you uploaded differs from the one you posted above. The last step is a sort by the Description and Sell Date. But it does not seem to do that either. It looks like the sheet may be corrupt. That has happened to me before.

Edit the query, go to the Advanced Editor and copy the M code. Then create a new Power Query > from Other Sources > Blank Query. Go to the Advanced Editor and paste the copied M code, replacing anything in the blank query.

Load this and you will find that the sort is as defined in the query (still using Description and sell date, but you can change that to ItemNo and sell date and it will work.

Code:
let
    Source = Excel.Workbook(File.Contents("C:\Users\lee.JJLGREENHOUSES\OneDrive\Documents\2015\Spring 2015 Power Query Source.xlsx")),
    SPRG15_Table = Source{[Item="SPRG15",Kind="Table"]}[Data],
    #"Removed Other Columns" = Table.SelectColumns(SPRG15_Table,{"ItemNo", "Description", "# of Crops", "Finish Units", "SP Orders BOOKINGS", "Planted", "Sell Date", "Crop #", "Planted Date"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Removed Other Columns",{{"Crop #", Int64.Type}, {"Planted", Int64.Type}, {"SP Orders BOOKINGS", Int64.Type}, {"Finish Units", Int64.Type}, {"Planted Date", type date}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type",{{"ItemNo", Order.Ascending}, {"Sell Date", Order.Ascending}})
in
    #"Sorted Rows"
 

aggie81

Member
I did as you asked and in the preview the data is returned in the sorted order but when it is loaded to the worksheet it is unsorted. All the data appears to be there when I compare the number of rows but it isn't sorted like I want it to be.
When I created the files and uploaded them, it was just the essentials as the workbooks are large and I didn't do the exact PQ that I first did. I am sorry for not being consistent.
I don't think any of the PQ I have made have ever sorted correctly into my spreadsheets.
Thank you for trying and sharing your knowledge with me. I have included some screen shots on what I get.
Lee

upload_2015-6-9_14-19-27.png
upload_2015-6-9_14-20-5.png
 

teylyn

New Member
I saw this behaviour in the file you attached. When I started a new file that used the data source you attached, the sort behaved as expected, both in the PQ editor and in the resulting sheet table. So, starting a new query in a new workbook worked out fine for me. I'm aware that may not be an option for you, but that's the best I can advise.
 

aggie81

Member
I tried starting a new file as you outlined but no luck.
I am using Excel 2013 Standalone to get the BI tools. Is it possible that the PQ install is corrupt? Grasping for answers but thank you for helping me.
Lee
 

teylyn

New Member
When I opened your attached PQ file, my system told me that it had been created with a newer version. So I downloaded the latest version and that produced the results I describe. I run Win7 enterprise with 2013 Professional Plus (not 365) and PQ version is now 1.23.4035.242
 

aggie81

Member
I am using version PQ 2.22.4007.242 with 64 bit XL.
I sent a frown to the PQ group, they sent an email and I included the screenshots.
Thanks again for helping and I will let you know what I hear from the PQ group.
 

teylyn

New Member
I find the QP team is very good with following up on feedback. Please update us how you get on.
 

aggie81

Member
I heard back for the PQ team. I have the query loading to the Data Model and Worksheet and there is some sort problems when this is done. I load it only to worksheet and it sorts just fine. I don't have any relationships or reasons that I need to load to Data Model in this case so all it well.
Thanks for helping and I wish you well.
Lee
 

DenzilXL

New Member
This tip was very helpful - I had the same problem - a sort order in PQ was not being respected when data loaded to the w/s, but as soon as I unticked Add to Data Model (and just loaded to w/s) then the sort order carried through.

Seems as though if you want to load to w/s and also keep the PQ output data in the model, you will need to reference the same query twice (fork two paths from the same query) and load one to the model and the other to the w/s.
 
Top