How do i combine multiple columns based on a group by of another column ?

[EaglesTony]

Hi,

  I have the following data:

Ruleid               RoleName              FullName

123                   Teacher                   John Doe

123                   Principal                 Jane Doe

123                   Assistant                Frank Drain

123                   Counsler                Mary Dodge

456                   Teacher                  Mark Adams

I need it to be something like this

Ruleid             AllAlike

123                 Teacher:John Doe, Principal:Jane Doe, Assistant:Frank Drain, Counsler:Mary Dodge

456                 Teacher:Mark Adams

Thanks

[dufoq3]

Hi @EaglesTony, another solution:

Add as a new step:

= Table.Group(Source, {"Ruleid"}, {{"AllAlike", each Text.Combine(Table.CombineColumns(_,{"RoleName", "FullName"},Combiner.CombineTextByDelimiter(":", QuoteStyle.None),"Merged")[Merged], ", "), type text}})

[wdx223_Daniel · Accepted solution]

NewStep=Table.Group(YourSource,"Ruleid",{"AllAlike",each Text.Combine(Table.ToList(_,each Text.Combine(List.Skip(_),":")),",")})

[m_dekorte]

Hi @EaglesTony 

Here are the steps to achieve that. Hold down CTRL and select the RoleName and FullName columns, go to the Transform tab use Merge Columns to combine them into a single AllAlike column.

Now you can use the Group By option (can also be found on the Transform tab), setting RuleID as the key column, and setting an aggregation for the AllAlike column - any aggregation will work, since you’ll be adjusting the formula manually in the formula bar afterwards, I've set it to Sum.

In the formula bar look for the list that looks like this:

{{"AllAlike", each List.Sum([AllAlike]), type text}}

Replace the List.Sum function with Text.Combine. Then, before the closing parenthesis, add a separator (for example, ", "). The updated code will look like this:

{{"AllAlike", each Text.Combine([AllAlike], ", "), type text}}

All done!

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyVtJRCklNTM5ILQKyvPIz8hRc8lOVYnVgkgFFmXnJmQWJOSDpxLxUNGnH4uLM4pLEvBIg260oMS9bwaUoMTMPSYVzfmlecQ7YeN/Eokqg/pR0iAkmpmYotgOlsxUcUxJzi5ViYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Ruleid = _t, RoleName = _t, FullName = _t]),
    #"Merged Columns" = Table.CombineColumns(Source,{"RoleName", "FullName"},Combiner.CombineTextByDelimiter(": ", QuoteStyle.None),"AllAlike"),
    #"Grouped Rows" = Table.Group(#"Merged Columns", {"Ruleid"}, {{"AllAlike", each Text.Combine([AllAlike], ", "), type text}})
in
    #"Grouped Rows"

I hope this is helpful.