Log In
Register
Home
Active Member
September 2, 2020
Offline
Hi,
In power query/pivot table, the ordering function seems sort values either alphabetically or ascendingly (descendingly). Is it possible to sort value in a specific order?
I have a census data which contains population counts across regions and types of qualification. I would like to order statistics in the following order in qualifcation.
No qualification
level 1 certificate
level 2 certificate
level 3 certificate
level 4 certificate
level 5 diploma
level 6 diploma
Bachelor and level 7 qualification
post-graduate and honours degrees
masters degree
doctorate degree
Any helps and tips are welcome! Thanks
Cheers
Guanyu
October 5, 2010
Offline
Hi Guanyu,
You can create a custom list and sort your pivot table by that.
File -> Options -> Advanced -> Edit Custom Lists
and create a new list in the desired order.
In your pivot table options -> Totals and Filters under 'Sorting', Use custom lists when sorting should be checked.
If you want a custom sort order in Power Query you'll need to assign numeric values to the qualification types and sort by those numbers. You can do this assignment either in your source data, or you can create a custom column within PQ.
But, if your data is going to end up in a pivot table then don't do the sorting in PQ, just use the custom lists.
If you need further assistance please supply a workbook with your data.
Thanks
Phil
Active Member
September 2, 2020
Offline
Hi Phil,
Thanks for your response. I believe it's easier to communicate with some data. However, I don't know how to share data in this platform. So I copy a very small subset of data here to illustrate
The first dataset contains six variables, shown below.
| area_code | area_name | year | highest_qual | counts |
| 100100 | North Cape | 2006 | No qualification | 405 |
| 100100 | North Cape | 2006 | Level 1 certificate | 147 |
| 100100 | North Cape | 2006 | Level 2 certificate | 84 |
| 100100 | North Cape | 2006 | Level 3 certificate | 45 |
| 100100 | North Cape | 2006 | Level 4 certificate | 99 |
| 100100 | North Cape | 2006 | Level 5 diploma | 21 |
| 100100 | North Cape | 2006 | Level 6 diploma | 33 |
| 100100 | North Cape | 2006 | Bachelor degree and Level 7 qualification | 24 |
| 100100 | North Cape | 2006 | Post-graduate and honours degrees | 6 |
| 100100 | North Cape | 2006 | Masters degree | 9 |
| 100100 | North Cape | 2006 | Doctorate degree | 0 |
| 100100 | North Cape | 2006 | Overseas secondary school qualification | 18 |
| 100200 | Rangaunu Harbour | 2006 | No qualification | 519 |
| 100200 | Rangaunu Harbour | 2006 | Level 1 certificate | 234 |
| 100200 | Rangaunu Harbour | 2006 | Level 2 certificate | 123 |
| 100200 | Rangaunu Harbour | 2006 | Level 3 certificate | 93 |
| 100200 | Rangaunu Harbour | 2006 | Level 4 certificate | 117 |
| 100200 | Rangaunu Harbour | 2006 | Level 5 diploma | 27 |
| 100200 | Rangaunu Harbour | 2006 | Level 6 diploma | 63 |
| 100200 | Rangaunu Harbour | 2006 | Bachelor degree and Level 7 qualification | 51 |
| 100200 | Rangaunu Harbour | 2006 | Post-graduate and honours degrees | 12 |
| 100200 | Rangaunu Harbour | 2006 | Masters degree | 3 |
| 100200 | Rangaunu Harbour | 2006 | Doctorate degree | 0 |
| 100200 | Rangaunu Harbour | 2006 | Overseas secondary school qualification | 39 |
The second dataset contains the qualification category and order values
| highest_qual | order |
| No qualification | 1 |
| Level 1 certificate | 2 |
| Level 2 certificate | 3 |
| Level 3 certificate | 4 |
| Level 4 certificate | 5 |
| Level 5 diploma | 6 |
| Level 6 diploma | 7 |
| Bachelor degree and Level 7 qualification | 8 |
| Post-graduate and honours degrees | 9 |
| Masters degree | 10 |
| Doctorate degree | 11 |
| Overseas secondary school qualification | 12 |
I'm able to update these two data in power pivot and to merge them together by the qualification (highest_qual) information. The thing I struggle is to find any function to tell excel to order qualification using the "order" value.
I really hope that make senses to you and helps you to understand my question.
Cheers
Guanyu
October 5, 2010
Offline
Hi Guanyu,
Use the Custom Lists as I described to sort your pivot table.
By the Way - to upload a file : Under the box where you type your replies there's a button named 'Attachments'. Click that and then select your file(s) and then click 'Start upload'.
Instructions on the process are here
https://www.myonlinetraininghu.....this-first
Regards
Phil
Active Member
September 2, 2020
Offline
Thanks Phil,
That's really helpful.
If I share the pivot table with other colleagues, is this custom list available?
Cheers,
Guanyu
October 5, 2010
Offline
Hi Guanyu,
Yes the custom list will be stored in the workbook so if it's opened elsewhere you (or someone else) can sort using that CL.
However, the CL will not be visible in the CL Dialog (File -> Options -> Advanced) on other computers. There are some other restrictions on using a CL on a computer other than the one it was created on:
Regards
Phil
1 Guest(s)
