Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

How can I consolidate rows?

Hello

 

I have that data:

IDHeader1Header2
ID149
ID174
ID228
ID263

 

How can I consolidate the rows by ID and take the maximum of column2 and the maximum of column3?

It should look like:

IDHeader1Header2
ID179
ID268

 

7 = max of 4 and 7

9 = max of 9 and 4 etc

 

Thanks!

Need Help
  • MFelix's avatar
    MFelix
    6 years ago

    Hi Anonymous ,

     

    If you wnat to redo your table you need to use the Query Editor and group by ID and then MAX for both columns:

     

    check the code for the query editor:

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8nQxVNJRMgFiS6VYHRjfHCwG4RsB2SBsgcQ3A2JjpdhYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ID = _t, Header1 = _t, Header2 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", type text}, {"Header1", Int64.Type}, {"Header2", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"ID"}, {{"Header1", each List.Max([Header1]), type number}, {"Header2", each List.Max([Header2]), type number}})
in
    #"Grouped Rows"

     

    If want on the visualization you need to make a table visualization and then select the maximum values for each of the columns.

     

6 Replies

  • BA_Pete's avatar
    BA_Pete
    Super User
    6 years ago

    Hi Anonymous 

     

    I've completed this in Power Query. Paste the following code into a blank query using Advanced Editor to follow my steps:

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8nQxVNJRMgFiS6VYHRjfHCwG4RsB2SBsgcQ3A2JjpdhYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ID = _t, Header1 = _t, Header2 = _t]),
    groupID = Table.Group(Source, {"ID"}, {{"data", each _, type table [ID=text, Header1=text, Header2=text]}}),
    addMaxHeader1 = Table.AddColumn(groupID, "maxHeader1", each Table.Max([data], "Header1")),
    addMaxHeader2 = Table.AddColumn(addMaxHeader1, "maxHeader2", each Table.Max([data], "Header2")),
    expandMaxHeader1 = Table.ExpandRecordColumn(addMaxHeader2, "maxHeader1", {"Header1"}, {"Header1"}),
    expandMaxHeader2 = Table.ExpandRecordColumn(expandMaxHeader1, "maxHeader2", {"Header2"}, {"Header2"}),
    remDataCol = Table.RemoveColumns(expandMaxHeader2,{"data"}),
    chgAllTypes = Table.TransformColumnTypes(remDataCol,{{"Header1", Int64.Type}, {"Header2", Int64.Type}})
in
    chgAllTypes

     

    I get the following output:

     

    Pete

    • MFelix's avatar
      MFelix
      Super User
      6 years ago

      Hi BA_Pete ,

       

      Great solution but using the advance features of the grouping this is made autmatically by Power Query.

       

      :D:D

      • BA_Pete's avatar
        BA_Pete
        Super User
        6 years ago

        Hi MFelix 

         

        You're right, I may have overcomplicated it a bit!   ğŸ˜„

  • MFelix's avatar
    MFelix
    Super User
    6 years ago

    Hi Anonymous ,

     

    If you wnat to redo your table you need to use the Query Editor and group by ID and then MAX for both columns:

     

    check the code for the query editor:

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8nQxVNJRMgFiS6VYHRjfHCwG4RsB2SBsgcQ3A2JjpdhYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ID = _t, Header1 = _t, Header2 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", type text}, {"Header1", Int64.Type}, {"Header2", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"ID"}, {{"Header1", each List.Max([Header1]), type number}, {"Header2", each List.Max([Header2]), type number}})
in
    #"Grouped Rows"

     

    If want on the visualization you need to make a table visualization and then select the maximum values for each of the columns.

     

  • amitchandak's avatar
    amitchandak
    Super User
    6 years ago

    Anonymous , You can also try new measures like

    Header1= sumx(summarize(Table,Table[ID],"_1",max(Table[Header1])),[_1])
    Header2= sumx(summarize(Table,Table[ID],"_1",max(Table[Header2])),[_1])

  • v-alq-msft's avatar
    v-alq-msft
    Community Support
    6 years ago

    Hi, Anonymous 

     

    Based on your description, you may create two measures as below.

     

    Header1 Measure = 
var _id = SELECTEDVALUE('Table'[ID])
return
CALCULATE(
    MAX('Table'[Header1]),
    FILTER(
        ALLSELECTED('Table'),
        'Table'[ID] = _id
    )
)
Header2 Measure = 
var _id = SELECTEDVALUE('Table'[ID])
return
CALCULATE(
    MAX('Table'[Header2]),
    FILTER(
        ALLSELECTED('Table'),
        'Table'[ID] = _id
    )
)

     

     

    Result:

     

    Best Regards

    Allan

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.