Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateJoin us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.
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
Solved! Go to 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"
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.
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"
Check out the July 2025 Power BI update to learn about new features.
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
User | Count |
---|---|
10 | |
8 | |
6 | |
6 | |
6 |