Home

Sorting a power pivot table

%3CLINGO-SUB%20id%3D%22lingo-sub-506658%22%20slang%3D%22en-US%22%3ERe%3A%20Sorting%20a%20power%20pivot%20table%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-506658%22%20slang%3D%22en-US%22%3EHi%20Frederik%3CBR%20%2F%3E%3CBR%20%2F%3ECan%20you%20add%20a%20screenshot%3F%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-510700%22%20slang%3D%22en-US%22%3ERe%3A%20Sorting%20a%20power%20pivot%20table%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-510700%22%20slang%3D%22en-US%22%3E%3CP%3EThis%20is%20what%20the%20file%20looks%20like.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'd%20like%20to%20sort%20it%20by%20any%20of%20the%20columns%20that%20I%20have%20marked%20with%20yellow%20and%20a%20red%20line.%3C%2FP%3E%3CP%3EIf%20you%20look%20at%20the%20small%20arrow%20in%20the%202nd%20column%20called%20description%20you'll%20see%20that%20it's%20sorted%20by%20this%20column%2C%20but%20if%20you%20check%20the%20products%20below%20you%20can%20see%20they're%20not%20in%20alphabetical%20order.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F9832%22%20target%3D%22_blank%22%3E%40Wyn%20Hopkins%3C%2FA%3E%26nbsp%3B%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F111705i9B93405926782F36%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22Screen%20shot%20power%20pivot.PNG%22%20title%3D%22Screen%20shot%20power%20pivot.PNG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-511467%22%20slang%3D%22en-US%22%3ERe%3A%20Sorting%20a%20power%20pivot%20table%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-511467%22%20slang%3D%22en-US%22%3ESo%20the%20sorting%20is%20hierarchical%20from%20left%20to%20right%2C%20so%20Item%20No_%20is%20the%20first%20sort%20level%2C%20then%20WITHIN%20that%20the%20description%20is%20sorted%20and%20so%20on%20as%20you%20move%20to%20the%20right.%3CBR%20%2F%3E%3CBR%20%2F%3EThere's%20not%20really%20a%20way%20around%20it%20that%20I'm%20aware%20of%20without%20%3CBR%20%2F%3Ea)%20changing%20the%20order%20of%20columns%3CBR%20%2F%3Eb)%20adding%20a%20Sort%20Number%20column%20to%20the%20far%20left%20column%3CBR%20%2F%3Ec)%20Using%20Power%20Query%20to%20create%20the%20table%20rather%20than%20Power%20Pivot%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-511478%22%20slang%3D%22en-US%22%3ERe%3A%20Sorting%20a%20power%20pivot%20table%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-511478%22%20slang%3D%22en-US%22%3E%3CP%3EOK%2C%20thank%20you%2C%20I'll%20have%20to%20look%20in%20to%20power%20query%20then%20%3CLI-EMOJI%20id%3D%22lia_slightly-smiling-face%22%20title%3D%22%3Aslightly_smiling_face%3A%22%3E%3C%2FLI-EMOJI%3E%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F9832%22%20target%3D%22_blank%22%3E%40Wyn%20Hopkins%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-498026%22%20slang%3D%22en-US%22%3ESorting%20a%20power%20pivot%20table%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-498026%22%20slang%3D%22en-US%22%3E%3CP%3EHI.%20we%20are%20working%20with%20a%20number%20of%20Power%20pivot%20files%20in%20our%20office.%20We%20extract%20the%20content%20from%20Microsoft%20NAV.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBut%20I%20can't%20get%20the%20power%20pivot%20table%20to%20sort%20the%20way%20I%20want.%3C%2FP%3E%3CP%3EIf%20I%20use%20sort%20option%20for%20each%20column%20that%20is%20shown%20when%20you%20click%20the%20little%20arrow%20in%20the%20top%20of%20each%20column%20nothing%20happens.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAND%20I%20cant%20rearrange%20the%20columns%20because%20in%20a%20secondary%20step%2C%20we%20use%20this%20Power%20pivot%20table%20to%20create%20an%20import%20file%20for%20Rapid%20start%20in%20NAV%20and%20if%20I%20rearrange%20the%20columns%20the%20import%20file%20will%20not%20work.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBest%20regards%20Fredrik%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-498026%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EBI%20%26amp%3B%20Data%20Analytics%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ENeed%20Help%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
fresope70
New Contributor

HI. we are working with a number of Power pivot files in our office. We extract the content from Microsoft NAV.

 

But I can't get the power pivot table to sort the way I want.

If I use sort option for each column that is shown when you click the little arrow in the top of each column nothing happens.

 

AND I cant rearrange the columns because in a secondary step, we use this Power pivot table to create an import file for Rapid start in NAV and if I rearrange the columns the import file will not work.

 

Best regards Fredrik

 

4 Replies
Hi Frederik

Can you add a screenshot?

This is what the file looks like.

 

I'd like to sort it by any of the columns that I have marked with yellow and a red line.

If you look at the small arrow in the 2nd column called description you'll see that it's sorted by this column, but if you check the products below you can see they're not in alphabetical order.

 

@Wyn Hopkins Screen shot power pivot.PNG

So the sorting is hierarchical from left to right, so Item No_ is the first sort level, then WITHIN that the description is sorted and so on as you move to the right.

There's not really a way around it that I'm aware of without
a) changing the order of columns
b) adding a Sort Number column to the far left column
c) Using Power Query to create the table rather than Power Pivot

OK, thank you, I'll have to look in to power query then :) @Wyn Hopkins 

Related Conversations
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
22 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies
PacketMon Components are not loading in WAC 1909
HotCakeX in Windows Admin Center on
2 Replies