Sort rows with duplicated values in matrix

[samrere]

My task is to concatenate tables from different excel files into one big csv file, then recreate those tables in Power BI Matrix with different slicers. However, rows in each table must preserve its order in the original table, if i simply create another column with row order and sort with it, power BI doesn't allow this because there is duplicated rows and their row order is different. Here is a sample csv data concatenated from two excel files, one records product from company A and the other records product from company B:

company,product,sold_location,value,row_order
A,carrot,Mars,100,0
A,apple,Mars,200,1
B,banana,Earth,300,2
B,apple,Earth,400,3
B,carrot,Earth,500,4

I need to preserve the order of product within each company. I've been following this solution: https://community.fabric.microsoft.com/t5/Desktop/Sorting-matrix-table/m-p/4356730#M1361290

i.e create an invisible column with empty space char, repeated with the values in row_order:

and put company, invisible column, and product in Rows field so that product can be sorted by invisible column:

All good now, but the drawback is this invisible column still shows and can be clicked:

 Is there a way to remove this level of hierarchy?

Finally, in general what is the best way to preserve original excel table row order? I'm very new to Power BI and didn't expect it to be this complicated. Thank you for any help!

[WishAskedSooner]

@samrere ,

I feel your frustration because I was in the same place as you not too long ago.

IMHO, this is one of the BIG failings of PowerBI because the way it handles sorting is not well documented AND buggy. Therefore, it really should be a sticky HOW TO at the top of this forum, so everyone can be aware of these issues and the workaround(s) needed.

I have made a solution to this problem a while ago. You can handle sorting duplicative data either using Calulated Columns in DAX or PowerQuery. There are pros and cons to each method especially when using DAX. My preference is PQ, and knowledge is power which is why this forum needs a sticky.

[v-prasare]

Hi @samrere,  As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided for your issue worked? or let us know if you need any further assistance here?

@Deku, Thanks for your promt response.

Thanks,

Prashanth Are

MS Fabric community support

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly and give Kudos if helped you resolve your query

[Deku · Accepted solution]

Please see this solution to get around the sort by duplication blocker

[samrere]

Thanks @Deku, that works! For anyone who is new to TMDL, watch the first 5 mins of this video: https://www.youtube.com/live/cosXZw0GcRU?si=um4ONrjODA5kkFHB