Merging Specific Columns of a Table

[Elliot137]

I am trying to merge colums based on duplicate block numbers so I would like all the information on 2 (or more) seperate lines to merge together for all the rows that have the same block number ('Block #' Column).

The 'Accept/Reject' column will always be 'Major' for this table. For the 'Date Raised' column I would like the earliest date. All the other columns I would like contatenated together with commas seperating them.

I would greatly appreciate any help!

Below I have shown an example of the data set I have and what I would like it to look like: 

[Elliot137]

Thanks a lot @BA_Pete 

[BA_Pete · Accepted solution]

Hi @Elliot137 ,

The following example query turns this:

...into this:

Example Query:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyNvFR0lEyNDU0VYrViVYyMTYyDAcJmFgagAWQVJhDBSwNXMEqLEzQBMyNlGJjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Block #" = _t, #"M/C" = _t]),
    
    groupRows = Table.Group(Source, {"Block #"}, {{"M/C", each Text.Combine([#"M/C"], ", "), type nullable text}})
in
    groupRows

Summary:

Group By whichever columns you want to keep.

Create aggregate columns for each of the columns you want to combine using the SUM (or any statistical) operator.

Edit the resulting code in the formula bar to change List.Sum(...) to Text.Combine(..., ", ") for each of the columns to be combined.

So this:

...goes to this:

For your date column, you can just choose the MIN operator during the Group By process.

Pete