Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Sorting visual by column with letter prefix

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...
Need Help
Anonymous's avatar
Anonymous
Not applicable
5 years ago

Hi Anonymous 

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. 

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    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.

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Hi Anonymous 

      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.