Forum Discussion

Mike282's avatar
Mike282
Icon for Helper III rankHelper III
6 years ago
Solved

Group rows, concatenate then sort concatenated value alphabetically

Hi all,

 

So I've got a table with rows containing item line numbers and a group ID.

 

 

I've then managed to group them together and concatenate by a delimiter following the below post:

https://community.powerbi.com/t5/Desktop/Agroup-and-concatenate-column-with-a-common-value/td-p/211860

 

This is technically what it suppose to look like

 

But when I go to add the concatenated values into a bar chart I noticed that the concatenated items are concantenated in an alphabetical order so even though technicall two grouped values have the same concatenated item value, BI treats them as two different things due to the order of the concatenated value. Is it possible to sort them alphabetically first then group them?

 

 

Kind regards,

Mike

Need Help

6 Replies

  • parry2k's avatar
    parry2k
    Icon for Super User rankSuper User
    6 years ago

    Mike282 may be add a sort step before concatenate step, sort by Group and Item

    • Mike282's avatar
      Mike282
      Icon for Helper III rankHelper III
      6 years ago

      Thanks parry2k . I tried using the order.ascending function but it doesn't seem to sort correctly. Could you let me know what I need to sort? Below is the M script snippet for when I grouped the value:

       

          #"Sorted Rows" = Table.Sort(#"Expanded Items",{{"Items.Item", Order.Ascending}}),
    #"Grouped Rows" = Table.Group(#"Sorted Rows", {"Group"}, {{"Item Value", each Text.Combine([Items.Item],"|"), type text}}),

       

      • parry2k's avatar
        parry2k
        Icon for Super User rankSuper User
        6 years ago

        Mike282 sort both on group and item, you are sorting only on item, which logically should work thou. How does your data looks like after sorting step?