Forum Discussion
Sorting Problem - Chart of Accounts
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.
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
8003
In 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),