Grouping data and then merging cells

[Anonymous]

Hi all,

I'm facing a problem which Im struggling to find a solution for and hope I can get some help here:

I have a table with cat1, cat2, action name and action date.  What I want to do is group by cat1 & cat 2 and then merge the contents of the action name into a single cell (same applies to action date).

E.g:

Cat1 | Cat2 | ActionName | Date

A          AA          Hello            01/01/2018

A          AA          World           02/01/2018

Should transform to:

Cat1 | Cat2 | ActionName | Date

A          AA    Hello World   01/01/2018 02/01/2018

Thanks in advance!

[ImkeF · Accepted solution]

Please paste the following code into the advanced editor and follow the steps:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUXIEER6pOTn5QNrAUB+IjAwMLZRidZDkw/OLclJA8kYI+VgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Cat1 = _t, Cat2 = _t, ActionName = _t, Date = _t]),
    #"Grouped Rows" = Table.Group(Source, {"Cat1", "Cat2"}, {{"All", each _, type table}}),
    ActionName = Table.AddColumn(#"Grouped Rows", "ActionName", each Text.Combine([All][ActionName], " ")),
    Date = Table.AddColumn(ActionName, "Date", each Text.Combine([All][Date], " "))
in
    Date