Hello Forum. I have a trial balance that is not sorting correctly. The accounts should be sorted by account number however they are sorted as Text. Both tables (Chart of accounts and GL detail) are in powerquery and i created a simple data model in powerpivot. In the attached file, you can see how the pivot table it is currently sorting and to the side, its the pivot table that i have manually sorted which is how it should sort. Any help would be appreciated. Thanks in advance
Incorrect Sort.xlsx (633.2 KB)
You should be able to resolve this in the Manage option on the
power pivot ribbon, if you select the column and then choose sort by, and use the index number you created it will sort the column based on the index.
Hi Kylie.oconnell. Thanks for the solution however you are using the GLdetail Fullaccouunt to sort ( Many Side) however, it needs to sort from the lookup table in Powerquery ( From the one side) .
I am sorry, I am a little confused, you only have the index column in the DIM table, I sorted the full account in the DIM table by the index column, have you tried to sort by the column and got the wrong results?
Could you please give more details about the error if it still persists after sorting the DIM table column Full Account by the Index column in the same table, does the order change but is still wrong etc, it will help to troubleshoot.
Hi kylie.oconnell: Thank you again. Exactly: when i put the measure in the pivot table and i use the Accounts coming from the fact table I got the wrong result ( I got the same amount in every cell) I Need the column from the Dim Table (Full Account) to filter the accounts from the FactTable. Maybe a powerquery formula to allow me to sort from the DIM table (Chart of Accounts)? Thats why i created an index column inside the DIM table ( Chart of Accounts) thinking that it will automatically sort the accounts when i use the Accts column from the DIM table
Could I ask why you are putting the full account from the FACT table in the pivot?
You should just have to sort the column in the DIM table by the index column, nothing else changes, are you sorting the column in the DIM table, using the same columns you already have set in your pivot table coming from the DIM table, and using the same measure that sums from your FACT table and still getting wrong results?
I tried it on your sample data please see below, you can see the column in the DIM table is sorted, I am using the columns from the DIM table, and your measure is using the FACT table.
Kylie.oconnell. I am sorry i misunderstood. I got it now. I was looking at the wrong table. Yes, this is the solution. Thanks a lot, and sorry for the back and forth.!
1 Like
Kylie.oconnell: Appreciated your help very much! Regards and enjoy the rest of your weekend!
2 Likes
