Simplify a multiple-conditional If/Then formula?

[Nick Burns]

I currently have this as a multiple-conditional formula:

= 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.

 

[AliGW]

Attach a sample workbook, please.

 

[Nick Burns]

Sample file attached (with the current multi-conditional column)

 

[Nick Burns]

Any help?

 

[Ron Coderre]

Using your posted workbook....
Create a new blank query and, using the advanced editor, copy the below code and paste it into the window:

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?

 

[Ron Coderre]

This M-Code is a bit more concise and easier to understand:

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?

 

[Nick Burns]

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:

= Table.AddColumn(KeepRowTotalCol, "Has Over 8hrs", each List.MatchesAny(Record.ToList(Record.SelectFields(_,List.Select(Table.ColumnNames(KeepRowTotalCol), each Text.Contains(_,"RegHours")))), each _ > 8 ))