Getting max value of each block and keeping the rest of the columns in the table

[kranthi82]

Hello everyone,

I am new to Power BI and have been trying to learn many things everyday. But now I feel like asking the community for some answers and hope someone can help me. Thank you in advance for everyone.

I got a table as shown in the picture and I want to modify the table by getting the max value of count. I want to also keep the rest of the values respectively for the other columns.

I want the return table to look like this:

BlockID Count Time Column 4 Column 5

1000        1

1001        2 

1002        3

1003        1

1004        1

1005        2

I've tried many ways to do it and came up with wrong answers. The closest was group by but it's only returning two columns. 

Kranthi.

[Zubair_Muhammad · Accepted solution]

@kranthi82 

Try this calculated table.

New Table =
FILTER (
    Table1,
    Table1[Count]
        = CALCULATE ( MAX ( Table1[Count] ), ALLEXCEPT ( TableName, TableName[BlockID] ) )
)

[Zubair_Muhammad · Accepted solution]

@kranthi82 

Try this calculated table.

New Table =
FILTER (
    Table1,
    Table1[Count]
        = CALCULATE ( MAX ( Table1[Count] ), ALLEXCEPT ( TableName, TableName[BlockID] ) )
)

[kranthi82]

@Zubair_Muhammad 

Thanks a lot Zubair. It works perfectly fine and I got another following question. Is there any possibility to modify existing table instead of creating a new one?

[Zubair_Muhammad · Accepted solution]

Hi @kranthi82 

To modify the existing table, we can use Query Editor

Please see attached file for steps

Go to Query Editor>>Select Column BlockID>>>Go to Transform Tab>>Click Group BY

Now we can add a new custom column from "Add Column" tab as follows

=Table.Max([ALL],"Count")

Then Expand this Custom Column

[kranthi82]

@Zubair_Muhammad 

Thank you once again. It seems to be working where as the only problem is when I apply changes, the window gets stuck after all rows is loaded as shown in the picture. What do you think the reason is and is there any other way of changing the existing table?