Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

How can I consolidate rows?

Hello   I have that data: ID Header1 Header2 ID1 4 9 ID1 7 4 ID2 2 8 ID2 6 3   How can I consolidate the rows by ID and take the maximum of column2 and the maximum of...
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.

     

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.