Hi All!
I have a table visual in Power BI that’s populated from data in an Excel table. I’m wanting to sort the table visual by two columns together at the same time but in different directions (such as [CustomerName] ASC, [ActivityDate] DESC).
I tried finding where you can do an advanced or composite sort similar to what you can do in Excel (first sort by X1 column in Y1 order, then by X2 column in Y2 order…etc.) However, I haven’t stumbled across that type of option within a visual, table, or query.
Looked at trying to do it in the Power Query editor, as well as the table/data view using the “Sort by Column” option, but can’t seem to get it to work. We tried converting the date to a sequence number in YYYYMMDD format, but then if we sort by that column, the customer names would be sorted DESC.
The only other thing I can think of is to create a sequence number that runs over each unique group of Customer Names…and the sequence number starts at 0001 for the MAX (ActivityDate) and counts up until it hits the oldest ActivityDate. My inclination would be to do that as an expression in Excel, but maybe there’s a way to do it in Power Query / M Code. But the problem becomes – how would I then use that sort expression in the table visual without having to add the field to the table display?
Am I missing something and making this too hard or is there some trickery involved to get the table to sort this way?
Thanks!
Kael
