I have a column that has 844 distinct, 836 unique called “Name”-text field. I have another column called Number, which has some null values and the rest distinct. I sorted the Number column asc order and added an index column. I then clicked on the “Name” column and am trying to sort by the newly created index column, ‘Name Sort Order’, but get the error: There can’t be more than one value in ‘Name Sort Order’ for the same value in ‘Name’. It is because of the Name column not being 100% unique I assume but how can I fix this? I have a visual with the 'Name" column and want the ‘Name’ column to sort by it’s related ‘Number’ column. Please advise.
If I understand the problem correctly, I would go back into Power Query, duplicate your main table, select the Name field, Remove Other Columns, then Remove Duplicates. What you should be left with is a single column table of names – all distinct, all unique. Sort these how you want, then add an index column and Close and Apply.
Now in your data model, connect that to your original fact table on the name field. Use the Name field from your newly created dimension table in your visuals, and you should have no problem sorting it by your index column.
I hope this is clear. If not please provide a sample PBIX file with supporting data file, and I can provide a more detailed solution.
I hope this is helpful.
- Brian
2 Likes
Thanks. That worked