Grouping data and use an IF clause to calculate a new column

[Richard_Halsall]

Hi,

Can anybody help resolve this issue I am facing.

I have data structured as below and I need to calculate the Hours and Overtime values (I have completed these columns with the correct values in the data)

Sample Data

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("nZhLj9MwEMe/Csp55drj9xEJkJCAEwek1R73BmKvfHvccVqPPX5tKyWpE/1/8Xgenvb5+fj85+3333+vrx/U8XTomE6gLjJeQIJJg68f00kKqRx+/Gp0vDx1mbBk6vOzz9QtU8FN6M+RTdeQDkOIA5ppaU6oUOakgoAyqp8NZ2jnVl+Zdt9ex+yV1F5ItEK5Wg1rqz23ms5FGwGBWL2Yp5E4j5Zpq9WajYbM2DK9cNQ7IEKZWf1sxNSyZeqa6YS17O6IJtVFycZqqgOg8WP3mNAyTeUBcDl+zJZfpG5pQXhHfG1pNAWMy/J8wDQtUykahcbR+Am4mtBnQtfXPz6hRfToqypvoiqQY6SqvHZXKbeSQSvzwmJJwkseDMW6906thF291XSFcSTU3dr25dt8RYvKPqRyD6l8q0pxRGv5ZNRh8jqEzDpPZqMhMzLrWsruPnZnVpGLzFjvEjrvY3GLVkU00my758gysntMaJlsH5PhnVZXWZA9rqlXUz4odnMIMwxWtQJGC0VKm4hYiFR/czRHrxH6+Yubtt8IFSYsmbsBVJi6ZfYbofrukGZaWqg3HUkDKOzN0M6tvjI3A6gwHbO6aoe0zAEkt4z2c5iRNIAiBpCcBRCvQJn6aFUrzNgyPcvFEkB+tZS8AiGzrmOpdyEub2rcgFnVoZ57gLp86SVeh27zpN2qQ5dPS3mB6fkEU+tCXK7R5W7p8qoSdaZoU9TQxZv8NLHjOhTylk/OcqTl9YZoJjpeUySKVCzfh2JWQk4B5F5lJWfVIs9Xh7W5vCjkvsrkNwNmrx2peRXIaqfO5cIxEw+S3ZNmsNsSFi1Lakk0Yx1PXJcX2Ny/jqTd/MQlMgsfDVLxFOgdcS/18KnFCx7DheYpdvWdNffVirXQdfPoO/6weHQ/L0xYMnf388LULdMcbdk1M72Zz8nkdmBzMnYJc+9eNNcyXY4Bssu4md63etxDNNV39ibXTVXUK7id8N+ikS62uhyvKmeq7QX9XVzlan4pKahTaZWrRApnSZ6LoRWz3suqfQfyBB4w3xH1PK2zkdmwa6dQYPk/hFQbXv4D", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Technician = _t, #"Week Number" = _t, #"Site Date" = _t, State = _t, #"Daily Site Hours" = _t, #"Hours Running Total" = _t, Hours = _t, Overtime = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Technician", type text}, {"Week Number", Int64.Type}, {"Site Date", type date}, {"State", type text}, {"Daily Site Hours", type number}, {"Hours Running Total", type number}, {"Hours", type number}, {"Overtime", type number}})
in
#"Changed Type"

Requirement:

With the data grouped by Technician and Week Number

If the State is 'IA', 'ND' or 'MD' the 'Overtime' is calculated on a daily basis if the Daily Site Hours are greater than 8,  the 'Hours ' would then be calculated as 'Daily Site Hours' less 'Overtime

For all other States the 'Overtime is calculated when the 'Hours Running Total' is greater then 40 at which point 'Overtime' is the difference and the 'Hours' will be  'Daily Site Hours' less 'Overtime', 'Overtime' for all subsequent days in the week will equal 'Daily Site Hours'. 'Hours' for all previous days will equal 'Daily Site Hours'

Thanks in advance for any help

[Anonymous · Accepted solution]

Hi,

Thanks for the solution Omid_Motamedise and AlienSx   offered, and i want to offer some more inforamtion for user to refer to.

hello @Richard_Halsall , you can fer to the following m code.

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("lVfLbtwwDPyVwudAK5F62McCaYAAbU89FAhyzK1Fc+3f15a8lsSH1CywBozlDGkOR/S+vCxffr//+vP37e2TWx4W3PYLuJvdbmDB7zfPn/eLNdbF/Enk7vVBZIApA54fjQEpg4M7RTrvgob1FBuNW+/5duxqYJI9jOs/GNTskVVu28phx066l3j9bXb0Blatfm9zDsoQug60dwrDRhmSiW0PwazqU2QGtJQBe4ZogtDDjLXu5iypv8UCDBQsDEAZfNdDiKKCBYsUu5oUm/6HqqfC4CmDc1X1PSgqCoLY/++PB2f+ytFdr3P0mr9ydNfdK9pFLRxoeDIhW9QEDYJSBnQ6wIuAjQJQ9PjTV7k/NTp8KDp+KDrR6F3r9qRyyrSg6NbM0M+q187byrCxiimDfN5eDN0EZYatP/GQnbcXtpunjA30tLTj+rsRE+o/GES/VAakDEfFVYV99pwG9QzarRqPxokC+kVamD9+svLVhVkZYMogC1gZkDJMF2bFeopd+8PSKgJWhjCu/2AQBawMkdXfrU20VMAKTWOotwMBuQMLw/95uDJslCGxCVYF5A7MDK1r887TReA+lFoIAxG4D+81tG8eURKBG1ASIc5E6HwoJA+7jGILg+7DNW+qla6RiuC+G8dzl9mcwm0agnnrRABbbhXD3FSeA1cNwM3jMsIPknDXFEx0HKG4pLwIJCmH4go7jOceiKW7XgGII5+769UkfMhPDKoIabbzr0HPwqb5aHpgZUVxeL89siNUXSKVAaYM8hKpDEgZ/DJ4e6g4P87s2e6p0DCFxsljR8oQz9NngksUl/+W4ADHbZBxDspFid9ofChDzXxwQToflBTiKXMBOh80ALBqjs4GQuMPqHhgVwacM6hzw01Syi51H6vqgr7+Aw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Technician = _t, #"Week Number" = _t, #"Site Date" = _t, State = _t, #"Daily Site Hours" = _t, #"Hours Running Total" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Technician", type text}, {"Week Number", Int64.Type}, {"State", type text}, {"Daily Site Hours", type number}, {"Hours Running Total", type number}}),
    #"Changed Type with Locale" = Table.TransformColumnTypes(#"Changed Type", {{"Site Date", type date}}, "en-GB"),
    #"Added Custom" = Table.AddColumn(#"Changed Type with Locale", "Custom", each let a=[Technician],
b=[Week Number],
c=[State],
d=[Site Date],
e=Table.RowCount(Table.SelectRows(#"Changed Type with Locale",each [Technician]=a and [Week Number]=b and List.Contains({"IA","ND","MD"},c)=false and [Site Date]<=d and [Hours Running Total]>40))
in e),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Overtime", each if List.Contains({"IA","ND","MD"},[State]) and [Daily Site Hours]>8 then  [Daily Site Hours]-8 else if [Custom]=1 then [Hours Running Total]-40 else if [Custom]>1 then [Daily Site Hours] else 0),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Hours", each [Daily Site Hours]-[Overtime]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom2",{"Custom"})
in
    #"Removed Columns"

Output

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

[Richard_Halsall]

@Anonymous Many thanks this worked great

[AlienSx]

let
    daily_states = {"IA", "ND", "MD"},
    ot = (tbl) => 
        [daily = Table.ToList(Table.Sort(tbl, "Site Date"), (x) => x & {List.Min({x{4}, 8}), List.Max({x{4} - 8, 0})}),
        rows = List.Buffer(Table.ToList(Table.Sort(tbl, "Site Date"), (x) => x)),
        weekly = List.Generate(
            () => [i = 0, r = rows{0}, overtime = List.Max({r{5} - 40, 0}), hours = r{4} - overtime],
            (x) => x[i] < List.Count(rows),
            (x) => 
                [
                    i = x[i] + 1, 
                    r = rows{i},
                    overtime = List.Max({0, List.Min({r{4}, r{5} - 40})}),
                    hours = r{4} - overtime
                ],
            (x) => x[r] & {x[hours], x[overtime]}
        ),
        result = Table.FromList(
            if List.Contains(daily_states, tbl{0}[State]) then daily else weekly,
            (x) => x, 
            Table.ColumnNames(tbl) & {"Hours", "Overtime"}
        )][result],
    group = Table.Group(data, {"Technician", "Week Number"}, {"x", ot}),
    combine = Table.Combine(group[x])
in
    combine

[Richard_Halsall]

@AlienSx Many thanks for your code which worked great

[Omid_Motamedise]

use the following formula

let
    // Step 1: Load your source data and change types as given
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("nZhLj9MwEMe/Csp55drj9xEJkJCAEwek1R73BmKvfHvccVqPPX5tKyWpE/1/8Xgenvb5+fj85+3333+vrx/U8XTomE6gLjJeQIJJg68f00kKqRx+/Gp0vDx1mbBk6vOzz9QtU8FN6M+RTdeQDkOIA5ppaU6oUOakgoAyqp8NZ2jnVl+Zdt9ex+yV1F5ItEK5Wg1rqz23ms5FGwGBWL2Yp5E4j5Zpq9WajYbM2DK9cNQ7IEKZWf1sxNSyZeqa6YS17O6IJtVFycZqqgOg8WP3mNAyTeUBcDl+zJZfpG5pQXhHfG1pNAWMy/J8wDQtUykahcbR+Am4mtBnQtfXPz6hRfToqypvoiqQY6SqvHZXKbeSQSvzwmJJwkseDMW6906thF291XSFcSTU3dr25dt8RYvKPqRyD6l8q0pxRGv5ZNRh8jqEzDpPZqMhMzLrWsruPnZnVpGLzFjvEjrvY3GLVkU00my758gysntMaJlsH5PhnVZXWZA9rqlXUz4odnMIMwxWtQJGC0VKm4hYiFR/czRHrxH6+Yubtt8IFSYsmbsBVJi6ZfYbofrukGZaWqg3HUkDKOzN0M6tvjI3A6gwHbO6aoe0zAEkt4z2c5iRNIAiBpCcBRCvQJn6aFUrzNgyPcvFEkB+tZS8AiGzrmOpdyEub2rcgFnVoZ57gLp86SVeh27zpN2qQ5dPS3mB6fkEU+tCXK7R5W7p8qoSdaZoU9TQxZv8NLHjOhTylk/OcqTl9YZoJjpeUySKVCzfh2JWQk4B5F5lJWfVIs9Xh7W5vCjkvsrkNwNmrx2peRXIaqfO5cIxEw+S3ZNmsNsSFi1Lakk0Yx1PXJcX2Ny/jqTd/MQlMgsfDVLxFOgdcS/18KnFCx7DheYpdvWdNffVirXQdfPoO/6weHQ/L0xYMnf388LULdMcbdk1M72Zz8nkdmBzMnYJc+9eNNcyXY4Bssu4md63etxDNNV39ibXTVXUK7id8N+ikS62uhyvKmeq7QX9XVzlan4pKahTaZWrRApnSZ6LoRWz3suqfQfyBB4w3xH1PK2zkdmwa6dQYPk/hFQbXv4D", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Technician = _t, #"Week Number" = _t, #"Site Date" = _t, State = _t, #"Daily Site Hours" = _t, #"Hours Running Total" = _t, Hours = _t, Overtime = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Technician", type text}, {"Week Number", Int64.Type}, {"Site Date", type date}, {"State", type text}, {"Daily Site Hours", type number}, {"Hours Running Total", type number}, {"Hours", type number}, {"Overtime", type number}}),
    
    // Step 2: Group by Technician and Week Number
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Technician", "Week Number"}, {
        {"Data", each 
            let
                TableData = _,
                
                // Step 3: Apply conditional overtime calculation based on State
                OvertimeCalculation = Table.AddColumn(TableData, "Calculated Overtime", each 
                    if List.Contains({"IA", "ND", "MD"}, [State]) then
                        // Daily basis overtime calculation if State is IA, ND, or MD
                        if [Daily Site Hours] > 8 then [Daily Site Hours] - 8 else 0
                    else
                        // Overtime when Hours Running Total > 40 for other states
                        if [Hours Running Total] > 40 then [Daily Site Hours] else 0
                ),
                
                // Step 4: Calculate the Hours column based on Overtime
                HoursCalculation = Table.AddColumn(OvertimeCalculation, "Calculated Hours", each 
                    [Daily Site Hours] - [Calculated Overtime]
                )
            in
                HoursCalculation
        }
    }),
    #"Expanded Data" = Table.ExpandTableColumn(#"Grouped Rows", "Data", {"Technician", "Week Number", "Site Date", "State", "Daily Site Hours", "Hours Running Total", "Hours", "Overtime", "Calculated Overtime", "Calculated Hours"}, {"Technician.1", "Week Number.1", "Site Date", "State", "Daily Site Hours", "Hours Running Total", "Hours", "Overtime", "Calculated Overtime", "Calculated Hours"})
in
    #"Expanded Data"

If this answer helped resolve your issue, please consider marking it as the accepted answer. And if you found my response helpful, I'd appreciate it if you could give me kudos. 

Thank you!

[Richard_Halsall]

@Omid_Motamedise Many thanks for your code