Forum Discussion

lotus22's avatar
lotus22
Icon for Helper III rankHelper III
4 years ago
Solved

Combine Rows in PowerQuery

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

Need Help
  • bcdobbs's avatar
    bcdobbs
    4 years ago

    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.

3 Replies

  • bcdobbs's avatar
    bcdobbs
    Icon for Community Champion rankCommunity Champion
    4 years ago

    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.

  • mahoneypat's avatar
    mahoneypat
    Icon for Microsoft Employee rankMicrosoft Employee
    4 years ago

    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