Getting Max Value to then calculate overall percentage completion

[Reino66]

I have a data set that roughly looks like this:

State	Date	Percent Complete
CT	1/1/2024	25
CT	1/8/2024	40
MA	1/1/2024	50
RI	1/1/2024	10
RI	1/8/2024	30
RI	1/15/2024	70
NY	1/1/2024	100
NJ	1/8/2024	20
NJ	1/15/2024	50
DE	1/15/2024	75

It is on a sharepoint list being filled via a MS Form.  The states don't always make a report so I need to be able to calculate the overall percent complete of the project based on their highest reported percentage.  The calculation I have now seems to be adding them together (for example CT is added to 65 instead of just using the highest reported 40).  I thought about using latest date, but some states stop reporting once they hit 100.  Is there a way to do this in Power Query Editor vice a DAX command. Thank you in advance.

[ronrsnfld]

DAX: 

SummaryTable = SUMMARIZE('Table','Table'[State ],"Max Percent Complete",max('Table'[Percent Complete]))

Power Query

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wcg5RUNJRMtQ31DcyMDIBMo1MlWJ1EOIWMHETA7C4ryOaelOIeJAnmrghqjjcHGM09aYwCXOIhF8khkFQCS80k4xQxREmQZ3k4ophBdBvsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [State = _t, Date = _t, #"Percent Complete" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"State", type text}, {"Date", type date}, {"Percent Complete", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"State"}, {
        {"Max", (t)=>Table.SelectRows(t, each [Percent Complete] = List.Max(t[Percent Complete])),
        type table[State=text, Date=date, Percent Complete=Int64.Type]}}),
    #"Expanded Max" = Table.ExpandTableColumn(#"Grouped Rows", "Max", {"Date", "Percent Complete"}, {"Date", "Percent Complete"})
in
    #"Expanded Max"

[watkinnc]

You can't do it in DAX via the power query editor, but you can group by state and choose the max aggregation for percent complete.

--Nate