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

Power BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.

Reply
LaineyTC71
New Member

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?

2 ACCEPTED SOLUTIONS
Anonymous
Not applicable

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

View solution in original post

Anonymous
Not applicable

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

vxinruzhumsft_0-1713753477305.png

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.

 

View solution in original post

2 REPLIES 2
Anonymous
Not applicable

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

vxinruzhumsft_0-1713753477305.png

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.

 

Anonymous
Not applicable

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

Helpful resources

Announcements
June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.

Top Solution Authors