Forum Discussion

LaineyTC71's avatar
LaineyTC71
New Member
2 years ago
Solved

Please help a new Power Query user!

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 refActual Years ServiceScheme YearsDays Entitlement
Mary5.310
Mary5.326
Mary5.336

Mary

5.348
Mary5.358
Mary5.3615
Mary5.3715
Mary5.3815

 

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?

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    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

  • Anonymous's avatar
    Anonymous
    2 years ago

    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.

     

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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.