Please help a new Power Query user!

[LaineyTC71]

I am new to Power Query and I am really liking it but I am stuck on this issue:

I have merged two tables and I want to complete return a value from one column based on the comparison of two other columns.  There are multiple rows for each employee and what I need to do is pull out the number of days leave an employee is entitled to based on their length of service match with the scheme.  My table looks like this currently:

Ee ref	Actual Years Service	Scheme Years	Days Entitlement
Mary	5.3	1	0
Mary	5.3	2	6
Mary	5.3	3	6
Mary	5.3	4	8
Mary	5.3	5	8
Mary	5.3	6	15
Mary	5.3	7	15
Mary	5.3	8	15

I need to return the days value of 8 in a custom column as this corresponds with Mary's length of service being greater then 5 years but less than 6.  I think I need to do something with lists or using custom functions but it is a bit beyond me right now.

Can this be done simply?

[watkinnc · Accepted solution]

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

[v-xinruzhu-msft · Accepted solution]

Hi, 

Thanks for the soluton @watkinnc  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.

[watkinnc · Accepted solution]

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