Forum Discussion
Sorting Problem - Chart of Accounts
I need to produce a report which involves displaying a column which is sorted by another column, this bit is easy and I've done it before with other datasets. However in this situation the columnn I wan to sort by does not have a 1:1 relationship with the one I want to display.
The data is a financial chart of accounts, each account has a number and a name. I want to display just the names without the numbers but I want them in their corresponsding number order and not alphabetic. However, in some cases the same name can correlate to two different account numbers (e.g. Staff Benefits can be both an Expense and a Liability, thus the name is used by two different Accounts). Because of this I cannot simply instruct Power BI to sort account name by account number.
I have seen example solutions to this problem which involve building a second manual table with the relevant sort order in it but that creates an ongoing manual overhead because adjustment to the table will be needed if/whenever a new account is added to the CoA.
Does anyone have an elagant solution to this problem that will not require future manual intervention?
Thank you
OK, so I solved it myself in the end. I made a copy of the Accounts table in Power Query and removed all the columns except Account Name and Number, then I removed the duplicate names. In the data model I related the duplicate table to the original using Account Name. Finaly, in the visual I used the Name field from the duplicate table in place of the Accounts table. Voila!
It wasn't plain sailing as my first attempt to remove duplicates failed. It seems that Power Query is case sensitive but Power BI is not (who knew that!? And more importantly why is it hat way?) To overcome this I used Transform in Power Query to change the case of the whole column before applying the remove duplicates step.
I'd still be interested to know if anyone knows another way to achieve the same result.
5 Replies
IntaBruce , As far as I know Sort column is the solution. The one for which you want an alternate
No that doesn't work. When I try to sort Account name by Account number I get an error.
OK, so I solved it myself in the end. I made a copy of the Accounts table in Power Query and removed all the columns except Account Name and Number, then I removed the duplicate names. In the data model I related the duplicate table to the original using Account Name. Finaly, in the visual I used the Name field from the duplicate table in place of the Accounts table. Voila!
It wasn't plain sailing as my first attempt to remove duplicates failed. It seems that Power Query is case sensitive but Power BI is not (who knew that!? And more importantly why is it hat way?) To overcome this I used Transform in Power Query to change the case of the whole column before applying the remove duplicates step.
I'd still be interested to know if anyone knows another way to achieve the same result.
Hello. This is what works for me when you have a hierarchy as follows:
700000
70000
7000
800000
80000
8000
8001
8002
8003In Excel, use this in a helper column and sort:
=LEFT([@[Account '#]],4).
In PowerQuery use this in a helper column and sort:#"Added Custom" = Table.AddColumn(#"Changed Type", "SortCol", each Number.From(Text.Start([#"Account #"], 4)), Int64.Type),
Hi IntaBruce,
Could you share your file, I would like to learn your method. Thanks in adavance.
