How to filter by the maximum value in a field?

[PowerWhy]

Hello,

Say I had a table of transactions, and there was a field with fiscal year. Please can you tell me how to only return records who's fiscal year is equal to the maximum in that column (i.e. the latest fiscal year)

Thank you,

PW

[v-stephen-msft]

Hi @PowerWhy ,

You could try the Group By in Power Query.

Here're the steps.

1.Load the table of transactions into Power Query.

2.Group the table by the fiscal year field.

3.Add an aggregation step to find the maximum value of the fiscal year field.

4.Merge the maximum value back into the original table using a left outer join.

5.Filter the table to only include rows where the fiscal year field is equal to the maximum value.

Reference: How to GROUP BY or summarize rows - Power Query | Microsoft Learn

Best Regards,

Stephen Tao

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

[Payeras_BI · Accepted solution]

Hi @PowerWhy ,

A possible solution:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwMlCK1UFlGMIYRjCGMRIjFgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Fiscal Year" = _t]),
    #"Filtered Rows" = Table.SelectRows(Source, each ([Fiscal Year] = List.Max(Source[Fiscal Year])))
in
    #"Filtered Rows"