Sorting Error: Can't be more than one value in 'x' for the same value in 'y'

[IanCockcroft]

Hi all,

I am trying to sort a text column by an index and get an error. i have tried all the solutions sggested but still the error.

I even tried to remove all  the columns and only had 1 row.

no luck 

any suggestions

[danextian]

Hi @IanCockcroft 

Sorting a column value by multiple sort values is not allowed in the GUI but is actually possible with Tabular Editor. Another workaround is to concatenate invisible a no-width space character which is repeated several times based on the sort column to the text.

Line Item2 = 
REPT ( UNICHAR ( 8203 ), 'Table'[Index] ) & 'Table'[Line Item]

For the sake of illustration, I duplicated "Profit / (loss) before indirect tax" and assigned multiple sort values to it. You can see in the image below that this text appears twice or so it seems. The first one has 27 invisible characters at the beginning while the other has 29

[bhanu_gautam · Accepted solution]

@IanCockcroft If it is not possible to ensure unique values in the 'Index' column, consider sorting the 'Line Item' column by a different column that does have unique values.

If you need to maintain the current structure, you can create a composite key by combining multiple columns to ensure uniqueness. For example, you can concatenate the 'Line Item' and 'Index' columns to create a new column with unique values.

CompositeKey = CONCATENATE([Line Item], [Index])

Then, you can sort the 'Line Item' column by this new 'CompositeKey' column.

[IanCockcroft]

Thanks Bhanu, that worked, but with a slight change

I got a circlar loop refernce initially if I use the column i want to sort in the CONCATE.

I used "a" 

this doesnt make sence

i think its a bug