Simplify a multiple-conditional If/Then formula?

Nick Burns

Nick Burns

Member
Joined
May 24, 2017
Messages
162
Reaction score
0
Points
16
Excel Version(s)
Office 365
I currently have this as a multiple-conditional formula:
Code: 
= Table.AddColumn(AddedNoSpreadError, "RTError", each if [SatRegHours] > 8 then 1 else if [SunRegHours] > 8 then 1 else if [MonRegHours] > 8 then 1 else if [TueRegHours] > 8 then 1 else if [WedRegHours] > 8 then 1 else if [ThuRegHours] > 8 then 1 else if [FriRegHours] > 8 then 1 else null, type number)

Is there a way to simplify that with List.Accumulate()? Hoping to be able to pass SAT-FRI and append it to "RegHours" and do the if/then for each column.

I have two additional columns to check if Overtime and DoubleTime have hours over 10.
 
Sample file attached (with the current multi-conditional column)
 

Attachments

  • EmpHours.xlsx
    17.6 KB · Views: 15
Using your posted workbook....
Create a new blank query and, using the advanced editor, copy the below code and paste it into the window:
Code: 
let
    Source = Excel.CurrentWorkbook(){[Name="EmpHours"]}[Content],
    SetDataTypes = Table.TransformColumnTypes(Source,{
        {"Company", Int64.Type},     
        {"Analysis", type text}, 
        {"EmpNum", type text}, 
        {"SatRegHours", Int64.Type}, 
        {"SunRegHours", Int64.Type}, 
        {"MonRegHours", Int64.Type}, 
        {"TueRegHours", Int64.Type}, 
        {"WedRegHours", Int64.Type}, 
        {"ThuRegHours", Int64.Type}, 
        {"FriRegHours", Int64.Type}, 
        {"SatOvtHours", Int64.Type}, 
        {"SunOvtHours", Int64.Type}, 
        {"MonOvtHours", Int64.Type}, 
        {"TueOvtHours", Int64.Type}, 
        {"WedOvtHours", Int64.Type}, 
        {"ThuOvtHours", Int64.Type}, 
        {"FriOvtHours", type number}, 
        {"SatDblHours", Int64.Type}, 
        {"SunDblHours", Int64.Type}, 
        {"MonDblHours", Int64.Type}, 
        {"TueDblHours", Int64.Type}, 
        {"WedDblHours", Int64.Type}, 
        {"ThuDblHours", Int64.Type}, 
        {"FriDblHours", Int64.Type}}),
    UnpivotDayCols = Table.UnpivotOtherColumns(SetDataTypes, {"Company", "Analysis", "EmpNum"}, "Attribute", "Value"),
    AddConditionalFactorCol = Table.AddColumn(UnpivotDayCols, "Factor", each 
        if (Text.Contains([Attribute], "Reg") 
            and  not Text.Contains([Attribute], "Sat")
            and [Value] > 8)
        then 1 
        else 0),
    CalcRowTotals = Table.Group(AddConditionalFactorCol, 
        {"Company", "Analysis", "EmpNum"}, 
        {{"RowTotal", each List.Sum([Factor]), type number}}),
    MergeDetailsWithRowTotals = Table.NestedJoin(
        SetDataTypes,{"Company", "Analysis", "EmpNum"},
        CalcRowTotals,{"Company", "Analysis", "EmpNum"},
        "Grouped Rows",JoinKind.LeftOuter),
    KeepRowTotalCol = Table.ExpandTableColumn(MergeDetailsWithRowTotals, "Grouped Rows", {"RowTotal"}, {"RowTotal"})
in
    KeepRowTotalCol

Is that something you can work with?
 
This M-Code is a bit more concise and easier to understand:
Code: 
let
    Source = Excel.CurrentWorkbook(){[Name="EmpHours"]}[Content],
    UnpivotDayCols = Table.UnpivotOtherColumns(Source, 
        {"Company", "Analysis", "EmpNum"}, 
        "Attribute", "Value"),
    KeepNonSat_Reg_Over8_Rows = Table.SelectRows(UnpivotDayCols, 
        each 
            not Text.Contains([Attribute], "Sat") 
            and Text.Contains([Attribute], "Reg") 
            and [Value] > 8),
    AddFactorCol = Table.AddColumn(KeepNonSat_Reg_Over8_Rows, "Factor", each 1),
    GroupRowsAndCalcRowTotals = Table.Group(AddFactorCol, 
        {"Company", "Analysis", "EmpNum"}, 
        {{"RowTotal", each List.Sum([Factor]), type number}}),
    MergeDetailsWithRowTotals = Table.NestedJoin(
        Source,{"Company", "Analysis", "EmpNum"},
        GroupRowsAndCalcRowTotals,{"Company", "Analysis", "EmpNum"},"Grouped Rows",
        JoinKind.LeftOuter),
    KeepRowTotalCol = Table.ExpandTableColumn(MergeDetailsWithRowTotals, "Grouped Rows", {"RowTotal"}, {"RowTotal"})
in
    KeepRowTotalCol

Does that help?
 
That did help me getting to think about the problem differently. This is a bit convoluted M-Code and I may change it to a function, but I got what I needed. The goals was to determine if any of the daily hours were over 8 hrs:
Code: 
= Table.AddColumn(KeepRowTotalCol, "Has Over 8hrs", each List.MatchesAny(Record.ToList(Record.SelectFields(_,List.Select(Table.ColumnNames(KeepRowTotalCol), each Text.Contains(_,"RegHours")))), each _ > 8 ))
 
You must log in or register to reply here.
Back
Top