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
Highlighted
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
Highlighted
Hi Frederik

Can you add a screenshot?
Highlighted

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

Highlighted
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

Highlighted

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