Group rows, concatenate then sort concatenated value alphabetically

[Mike282]

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/2118...

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

[parry2k]

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

[Mike282]

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]

@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?

[Mike282]

Hi @parry2k ,

Thanks for the fast response! Still no luck. The concatenated values all concatenate together in random order.

Kind regards,

Mike

[parry2k · Accepted solution]

@Mike282 I think you need to use table.buffer and then group it. Similar post here.

[Mike282]

Thanks @parry2k ,

That seems to do the trick! Although the table buffer function seems to also at the same time slow query performance.