Please help a new Power Query user!

Try getting The row with the Max Scheme Years where the Actual years are >= Scheme years, like

Table.AddColumn(PriorStepOrTableName, "Result", each Table.Max(Table.SelectRows(_, each [Actual Years] >= [Scheme Years]), {"Scheme Years"}))[Scheme Years]{0}

--Nate

Hi, 

Thanks for the soluton Anonymous  provided and i want to offer some more information for user to refer to.

hello LaineyTC71 , you can create a custom column.

List.Max(Table.SelectRows(#"Changed Type"(your last step name),(x)=>x[Scheme Years]<=[Actual Years Service])[Days Entitlement])

Output

And you can refer to the following m code

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8k0sqlTSUTLVMwaShkBsoBSrgyZsBMRmmMLG2IVNgNgCU9gUu7AZyFpTTHFzHOIWUPFYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Ee ref" = _t, #"Actual Years Service" = _t, #"Scheme Years" = _t, #"Days Entitlement" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Ee ref", type text}, {"Actual Years Service", type number}, {"Scheme Years", Int64.Type}, {"Days Entitlement", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each List.Max(Table.SelectRows(#"Changed Type",(x)=>x[Scheme Years]<=[Actual Years Service])[Days Entitlement]))
in
    #"Added Custom"

Best Regards!

Yolo Zhu

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