Combine Rows in PowerQuery

[lotus22]

I could use GroupBy but I only want to Combine data with column that has particular value? I want to only combine rows with value B. Off course there are other columns as well.

Value A

Value A

Value B

Value B

Value B

Value B

Value C

Value C

Value C

[mahoneypat]

Here's one way to do it in the query editor.  To see how it works, just create a blank query, open the Advanced Editor and replace the text there with the M code below.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCkvMKU1VcFSK1YlWAhM4BJyoIOBMjEAsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Value = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Value", type text}}),
    Split = Table.SelectRows(#"Changed Type", each ([Value] <> "")),
    #"Filtered Rows" = Table.SelectRows(Split, each ([Value] = "Value B")),
    #"Grouped Rows" = Table.Group(#"Filtered Rows", {"Value"}, {{"Count", each Table.RowCount(_), Int64.Type}}),
    Custom1 = Split,
    #"Filtered Rows1" = Table.SelectRows(Custom1, each ([Value] <> "Value B")),
    #"Appended Query" = Table.Combine({#"Filtered Rows1", #"Grouped Rows"})
in
    #"Appended Query"

Pat

[bcdobbs · Accepted solution]

Could you:

Make 2 reference copies of the initial query.

Filter 1 to just include B rows and the other to not include B rows.

Apply group by to the query with just B rows.

Append the queries back together.

Mark all the helper stages as do not load.

[lotus22]

Great clever IDEA!! thank you!!