Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
luvagoldenk9
Helper II
Helper II

Delete rows in M Query Based on Max Year

Hello!

 

I have a table that has Year / Customer Name recorded for a transaction:

 

2022     Customer A

2022     Customer B

2022     Customer C

2021     Customer A

2020     Customer B

 

I only need the 3 2022 records as that is the max year and do not want to hardcode it for a specific year. In M query, is there a way to find the max Year and only keep those current transactions?

 

Thank you in advance.

1 ACCEPTED SOLUTION
VahidDM
Super User
Super User

Hi @luvagoldenk9 

 

You can use List.Max to find the MAX value in the Year column, so the M code for your question can be as follow:

 

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwMlLSUXIuLS7Jz00tUnBUitXBFHXCKuoMEzXEaoIBmgmxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Year = _t, Customer = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Year", Int64.Type}, {"Customer", type text}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([Year] = List.Max(#"Changed Type"[Year])))
in
    #"Filtered Rows"

 

 

#"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([Year] = List.Max(#"Changed Type"[Year])))

 

Sample file attached.

 

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn: 
www.linkedin.com/in/vahid-dm/

 

 

View solution in original post

3 REPLIES 3
Ashish_Mathur
Super User
Super User

Hi,

This M code works as well

let
    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Year", Int64.Type}, {"Customer", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Customer"}, {{"Max Year", each List.Max([Year]), type text}}),
    Joined = Table.Join(Source, "Customer", #"Grouped Rows", "Customer"),
    #"Added Custom" = Table.AddColumn(Joined, "Test", each [Year]=[Max Year]),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([Test] = true)),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Max Year", "Test"})
in
    #"Removed Columns"

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
VahidDM
Super User
Super User

Hi @luvagoldenk9 

 

You can use List.Max to find the MAX value in the Year column, so the M code for your question can be as follow:

 

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwMlLSUXIuLS7Jz00tUnBUitXBFHXCKuoMEzXEaoIBmgmxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Year = _t, Customer = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Year", Int64.Type}, {"Customer", type text}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([Year] = List.Max(#"Changed Type"[Year])))
in
    #"Filtered Rows"

 

 

#"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([Year] = List.Max(#"Changed Type"[Year])))

 

Sample file attached.

 

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn: 
www.linkedin.com/in/vahid-dm/

 

 

Thank you VahidDM! Adding that one statement worked. Have a great day!

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.