Sort order in Power Query does not carry over to Power Pivot view

generalledger

New member
Joined
Oct 15, 2016
Messages
10
Reaction score
0
Points
0
Location
New Jersey, USA
Excel Version(s)
2016
Has anyone else experienced that records sorted in Power Query do not appear in the same order as when loaded and viewed in Power Pivot? Where does Power Pivot get it's sort order from? There does seem to be a pattern with many levels but I cannot decipher it.
 

p45cal

Super Moderator
Staff member
Joined
Dec 16, 2012
Messages
2,200
Reaction score
16
Points
38
Excel Version(s)
365
records sorted in Power Query do not appear in the same order as when loaded and viewed in Power Pivot?
As viewed in a pivot table or as viewed in the data model?
Could you upload an example file?
 

JoePublic

Super Moderator
Staff member
Joined
Sep 16, 2011
Messages
234
Reaction score
3
Points
18
Location
UK
Excel Version(s)
2016
That setting is for the field list, not the pivot table results.
 

AgingRapidly

New member
Joined
Mar 11, 2022
Messages
15
Reaction score
0
Points
1
Location
Tennessee
Excel Version(s)
Excel365
Joe,
You are correct. My mistake on the setting. Below is the setting I should have shown. I am unable to sort the ProductLine column in the pivot table. Any suggestions on how to fix the problem? Thanks

1666182745725.png
 

JoePublic

Super Moderator
Staff member
Joined
Sep 16, 2011
Messages
234
Reaction score
3
Points
18
Location
UK
Excel Version(s)
2016
Is your pivot table built directly from the query, or did you load that to the data model and then use Power Pivot? If the latter, what does the table in the data model look like?

FYI sorting Product Line in that pivot will only sort it within the ItemCode field since that is its parent.
 

AgingRapidly

New member
Joined
Mar 11, 2022
Messages
15
Reaction score
0
Points
1
Location
Tennessee
Excel Version(s)
Excel365
Query was loaded into the data model and used to create the Power Pivot. Below is a screen capture from the Data View. Items not in the same order as in the sorted query.

I'm trying to get the ItemCode in order by its ProductLine.

1666202855586.png
 

p45cal

Super Moderator
Staff member
Joined
Dec 16, 2012
Messages
2,200
Reaction score
16
Points
38
Excel Version(s)
365
FYI sorting Product Line in that pivot will only sort it within the ItemCode field since that is its parent.
Exactly. ProductLine seems to be below ItemCode in the pivot, so that ProductLine column in the pivot, if there were more than one ProductLine for a given ItemCode then I would expect them to be sorted in data source order, and I think that data source will be the table in the Data Model rather than the data order of whatever is supplying the data model (they could, of course, be the same).
 

AgingRapidly

New member
Joined
Mar 11, 2022
Messages
15
Reaction score
0
Points
1
Location
Tennessee
Excel Version(s)
Excel365
ProductLine seems to be below ItemCode in the pivot, ...
p45cal,

Thank you for your response. Your explanation triggered an ah hah moment. I realized that I needed to have the ProductLine before the ItemCode in the pivot table for the sort to work as expected. I think JoePublic said the same thing, just in a different way, and I didn't get it.

Thanks to both of you.
 
Top