Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin 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"
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
15 | |
12 | |
8 | |
8 | |
7 |
User | Count |
---|---|
15 | |
13 | |
7 | |
7 | |
6 |