Grouping data and then merging cells

Question

Hi all,&nbsp;I'm facing a problem which Im struggling to find a solution for and hope I can get some help here:&nbsp;I have a table with cat1, cat2, action name and action date.&nbsp; What I want to do is group by cat1 &amp; cat 2 and then merge the contents of the action name into a single cell (same applies to action date).&nbsp;E.g:&nbsp;Cat1 | Cat2 | ActionName | DateA&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; AA&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Hello&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 01/01/2018A&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; AA&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;World&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;02/01/2018&nbsp;Should transform to:&nbsp;Cat1 | Cat2 | ActionName | DateA&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; AA&nbsp;&nbsp;&nbsp;&nbsp;Hello World&nbsp;&nbsp;&nbsp;01/01/2018 02/01/2018&nbsp;Thanks in advance!

imkef · Accepted Answer

Please paste the following code into the advanced editor and follow the steps:&nbsp;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&nbsp;

Forum Discussion

Grouping data and then merging cells

1 Reply

Related content

Merge Cells in Matrix

Merge row cells

Merged cell

Merged Cells in Source

Merge cells in tabular visualization