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

G

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.
 
generalledger said:
records sorted in Power Query do not appear in the same order as when loaded and viewed in Power Pivot?
Click to expand...
As viewed in a pivot table or as viewed in the data model?
Could you upload an example file?
 
That setting is for the field list, not the pivot table results.
 
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
 
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.
 
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
 
JoePublic said:
FYI sorting Product Line in that pivot will only sort it within the ItemCode field since that is its parent.
Click to expand...
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).
 
p45cal said:
ProductLine seems to be below ItemCode in the pivot, ...
Click to expand...
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.
 
You must log in or register to reply here.
Back
Top