Sort Chart of Accounts

J

jazzista

Member
Joined
Jan 4, 2017
Messages
50
Reaction score
0
Points
6
Excel Version(s)
Office 365
Hello Forum! I am trying to create a pivot table with expense accounts however its not sorting correctly. Is it possible to sort it correclty in powerquery? See attached file. Thanks in advcance for any help. Stay safe.
 

Attachments

  • acct sort.xlsx
    339.1 KB · Views: 21
Have looked at your file. No indication as to how you wish to sort the PT. Perhaps a little indication as to what is not correct about the PT would be helpful. What is the correct sort you are looking to achieve.
 
There was no query, so I recreated yours (group Main and Sub and Description to get total amount), and could sort by Description fine. So what is the problem?
 
Alansidman: Good morning. I need this table to be sorted by Acct number. Since the account number is text, is sorting out of sequence. It should sort like the list below:

Main And SubAccount DescriptionSum of Net Amount
220 10Contract Labor 668,463.40
220 15Catering 7,928.46
220 20Trans - Air Travel 1,496.00

This is how is currently sorting.
Main And SubAccount DescriptionSum of Net Amount
215 10Capitalized Interest 115,997.00
220 10Contract Labor 668,463.40
220 100Drilling Unit 16,421.47
220 110Directional Drilling Services 2,176,608.69
220 115Cutting Handling Services 299,504.68
220 130Drill Pipe (Rentals) 163,172.72
220 145Drilling Jars 30,544.40
220 15Catering 7,928.46
220 165Casing Spears 2,110.77
220 175Fugro Sonar 18,166.54
220 195Electric Line Service 31,149.81
220 20Trans - Air Travel 1,496.00

I went into PQ and try to sort it from A to Z and it did not sort the way I needed to sort it. Is there any function to sort the numbers as text correctly? Thanks in advance for your help
 
Hi Bob: Good morning. I neeed to sort by Acct number and my table is currently sorting out of sequence. It should sort like this. Any help is greatly appreciate it. Thank you so much

Main And SubAccount DescriptionSum of Net Amount
220 10Contract Labor 668,463.40
220 15Catering 7,928.46
220 20Trans - Air Travel 1,496.00
 
You should have table with all of your accounts (in right order).
Then you need to add an index column to this table.
Then just merge these tables and sort by index column
 
Thanks Mr Bill! I will try that approach.
 
jazzista said:
Hi Bob: Good morning. I neeed to sort by Acct number and my table is currently sorting out of sequence. It should sort like this. Any help is greatly appreciate it. Thank you so much

Main And SubAccount DescriptionSum of Net Amount
220 10Contract Labor668,463.40
220 15Catering7,928.46
220 20Trans - Air Travel1,496.00
Click to expand...

If you sort by Main and then by sub before you delete these columns, you should be okay.
 
Alternatively, you can add a column to sort by this column
Code: 
= Table.AddColumn(#"Changed Type", "ToSort", each Text.Combine(List.Transform(Text.Split([Main And Sub], " "), each Text.PadStart(_, 3, "0"))), type text)

@alansidman thanks :)
 
Last edited:
BIll. THANK YOU VERY MUCHHH.. Finally, i got it sorted with your custom column: I tried the Text.PadStart function days ago but i was getting an error ( Never used that function before). Question for learning: I am noticing that you are adding " _," before the number 3 and you are adding a "0" . What is the purpose of both _, and the "0"? I was using " " last week . Again . Thank you
 
I am assuming that "Tosort" column is a helper column so that the pivot table automatically sorted my accounts in chronological order.
 
You must log in or register to reply here.
Back
Top