Sorting visual by column with letter prefix

[BaltoLo]

Hello, i am trying to sort the following (Drill-Down) visual by the attribute UnterkapitelID of the following table

Since the ID contains letters, 10, 11, 12 etc. they sorted before 2,3,4 etc. To solve this problem I tried to create a new column, which in principle only removed the U. But I am not allowed to sort by that column, if I try to apply it to UnterkapitelID I create a circular dependency and on the other attributes there are duplicates.

Is there a simple way to solve this problem. I would like to avoid inserting a column in the database which is only for sorting. Furthermore, I would like to avoid having the column for sorting e.g. in the tooltip. The problem also occurs with other similarly structured tables and visuals.

Thanks in advance

[v-rzhou-msft]

Hi @BaltoLo 

If you want to sort text value, you may need to build a sort column.

You can build a sort column in Power Query Editor.

Here I have some advice.

1. You can duplicate UnterkapitelID and replace U by blank.

2. You can extract text after delimter U.

And you need to change column type to whole number.

Then you can sort the ID column by this rank column.

Best Regards,

Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. 

[BaltoLo]

For every table except the one in the post this works perfectly, thanks.
With the one in the example the Problem is that the Attribute Unterkapitel can not be sorted by the newly created row because of multiple values with the same name. I could sort the UnterkapitelID with vaule but that does nothing to the visual.
Can I somehow apply the sort by column Operator to the Attribute Unterkapitel.
It works if I put the sort row into the Tooltip, but I would like to avoid that. Since the table Unterkapitel is used in a Drill-Down-Visual I would also get the Tooltip on the upper level of the Drill-Down.
Can i somehow apply the sort by column Operator to the Attribute Unterkapitel.

[v-rzhou-msft]

Hi @BaltoLo 

Your sort by column option will show error, due to your Unterkapitel column has the same value.

You can try to merge KapitelID and Unterkapitel to get a new column to distinguish the same value.

My sample:

I duplicate  KapitelID and Unterkapitel columns and merge them to a new column.

We get MergedSort by extracting text after Delimiter U from UnterkapitelID and get KapitelIDSort by text after Delimiter K from KapitelID.

Then we can use sort by column function to sort KapitelID by KapitelIDSort column and sort Merged by MergedSort.

Drill-Down:

If this reply still couldn't help you solve your problem, please provide me a sample file. I need to know your drill down/up model and more details about your table.

You can download the pbix file from this link: Sorting visual by column with letter prefix

Best Regards,

Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. 

[amitchandak]

@BaltoLo , If you have create a column(B) from Column (A), then you can not sort Column A on B.

Do Like

A1 = [A]

B = sort using A

Then Sort A1 on B and use A1 in visual

Also A and B should have same granularity. Means 1-1 Mapping values 

[BaltoLo]

---

Do Like

A1 = [A]

B = sort using A

Then Sort A1 on B and use A1 in visual

---

 What exactly do you mean by do A1 = [A] ?