Looking for the 'CONCATENATE' formula in M-code for Power-Query-Editor.

[brief001]

I am looking for an M code for a calculated column.
The aim is for the values of column Type to be merged in alphabetical order, where the ID is the same.

On the left you see the two columns I have.
And the orange column is the calculated column I'm looking for.

[brief001]

Hi Pete,

Thank you for your extensive explanation. Your explanation is done step by step. And it works!

However, the intermediate step to get it sorted was missing. But luckily I had this knowledge myself, and have supplemented it myself.

Below my result:

let
Bron = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wci9KTc1T0lFyUYrVQfAcUXhOYF5QagqQHYTEjgKznXJSS4EcNzAnPCOzJBUuBeM5ofBClWJjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Type = _t]),
#"Type gewijzigd" = Table.TransformColumnTypes(Bron,{{"ID", type text}, {"Type", type text}}),
#"Rijen gesorteerd" = Table.Sort(#"Type gewijzigd",{{"Type", Order.Ascending}}),
#"Buffer table" = Table.Buffer(#"Rijen gesorteerd"),
#"Rijen gegroepeerd" = Table.Group(#"Buffer table", {"ID"}, {{"Measure", each Text.Combine([Type], "-"), type text}})
in
#"Rijen gegroepeerd"

[BA_Pete · Accepted solution]

Hi @brief001 ,

In Power Query, select your [ID] column then go to the Home tab > Group By.

In the lower part of the dialog box, create an aggregate column called 'Measure' and set the operator to SUM, and select [Type] as the column to sum.

If you want to expand your table back out into all original rows after this operation, then also add an aggregated column using the 'All Rows' operator.

Your new [Measure] column will have errors in it, but it's set up the code structure for us.

In the formula bar on your Group By step, you should see where the SUM function has been applied. We're going to change that to a Text.Combine function instead, like this:

// From this:
Table.Group(
    previousStepName,
    {"ID"}, 
    {{"Measure", each List.Sum([Type]), type text}}             // Change the List.Sum here
)

// To this:
Table.Group(
    previousStepName,
    {"ID"}, 
    {{"Measure", each Text.Combine([Type], "-"), type text}}  // To Text.Combine here
)

This should give you the listed text that you want.

If you previously created an 'All Rows' aggregated column, you can now expand that column to reinstate all original (ungrouped) rows.

Pete