Forum Discussion

TakeoffGoatMigo's avatar
4 years ago
Solved

Merging rows by new field name

I have a table which has a list of properties.  Each of those properties can have one or more report types represented in a separate column, with a row for each report type present (3 different types...
  • MisterFry's avatar
    MisterFry
    4 years ago

    The first step would be to sort your data by the report type, so the outcome fields are all the same. 

    Second, use group by, and concatenate all of the report type values in the resulting group: 

     

    let
        Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCijKL0gtKqlUMFTSUQqpLEhVcAIyDA0MgKSxqYFSrA42NY6oamIB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Property = _t, Type = _t, #"Value 1" = _t, #"Value 2" = _t]),
        #"Changed Type" = Table.TransformColumnTypes(Source,{{"Property", type text}, {"Type", type text}, {"Value 1", Int64.Type}, {"Value 2", Int64.Type}}),
        #"Sorted Rows" = Table.Sort(#"Changed Type",{{"Type", Order.Ascending}}),
        #"Grouped Rows" = Table.Group(#"Sorted Rows", {"Property"}, {{"All Rows", each _, type table [Property=nullable text, Type=nullable text, Value 1=nullable number, Value 2=nullable number]}, {"New Type", each Text.Combine([Type], " - "), type nullable text}})
    in
        #"Grouped Rows"

     

    Oof, that posts ugly. Click the dropdown on your type field. Sort it ascending. 
    Right click your Property field, and choose 'Group By'. 
    Select advanced, and create one field for 'All Rows' (assuming you want to preserve the rows) 

    Create a second one of type 'Sum' on your 'Type' field. - This will return errors, because you can't sum text. 

    Go into the generated code of the step, and replace the sum bit with: 

    {"New Type", each Text.Combine([Type], " - "), type nullable text}