Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Null values creating new errors in edit query conditionals

Hi all,   I have an employee data report where I have added a number of conditional statements to determine things like which employees were hired in specific timeframes, termed in specific timefra...
Need Help
AlexisOlson's avatar
AlexisOlson
Icon for Super User rankSuper User
4 years ago

What does the error message say and what does your M code look like (you can paste it from the Advanced Editor)?

 

My initial guess is that it might be interpreting null as a string "null" rather than an empty date value but I can't tell if that's the case from your screenshots or not.

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Sorry, I didn't even realize I could click on the "error" to get more details... here is the error:

     

    • AlexisOlson's avatar
      AlexisOlson
      Icon for Super User rankSuper User
      4 years ago

      Interesting. It sounds like you have some sort of if null then ... rather than if x = null then ... happening.

       

      Please share the M code.

      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

        In the conditionals, it seems as though before the "else" 0 seemed to resolve the nulls, but that isn't working anymore.  Here is the first part of the code:

         

        let
        Source = Sql.Databases("HAMSQL007\Dayforce"),
        ....
        #"Added Custom7" = Table.AddColumn(dbo_DF_Employee, "Address Combined", each [Address1]&", "&[City]&", "&[StateCode]&" "&[ZipCode]),
        #"Added Conditional Column" = Table.AddColumn(#"Added Custom7", "% Female", each if [Gender] = "F" then 1 else 0),
        #"Added Conditional Column1" = Table.AddColumn(#"Added Conditional Column", "ActiveEECount", each if [EmploymentStatus] = "Active" then 1 else 0),
        #"Added Conditional Column2" = Table.AddColumn(#"Added Conditional Column1", "Count PoC", each if [DFEthnicity] <> "White (not Hispanic or Latino)" then 1 else if [DFEthnicity] <> "Decline to Answer" then 1 else 0),
        #"Removed Columns" = Table.RemoveColumns(#"Added Conditional Column2",{"Count PoC"}),
        #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"% Female", "GenderFemaleCount"}, {"DFEthnicity", "Ethnicity"}}),
        #"Added Conditional Column3" = Table.AddColumn(#"Renamed Columns", "EthnicityPoC", each if Text.Contains([Ethnicity], "American Indian") then 1 else if Text.Contains([Ethnicity], "Asian") then 1 else if Text.Contains([Ethnicity], "Black") then 1 else if [Ethnicity] = "Hispanic or Latino" then 1 else if Text.Contains([Ethnicity], "Two") then 1 else 0),
        #"Added Conditional Column4" = Table.AddColumn(#"Added Conditional Column3", "TerminatedEECount", each if [AssignmentType] = "Secondary" then 0 else if [EmploymentStatus] = "Terminated" then 1 else 0),
        #"Inserted Age" = Table.AddColumn(#"Added Conditional Column4", "Age", each Date.From(DateTime.LocalNow()) - [BirthDate], type duration),
        #"Inserted Total Years" = Table.AddColumn(#"Inserted Age", "Total Years", each Duration.TotalDays([Age]) / 365, type number),
        #"Rounded Off1" = Table.TransformColumns(#"Inserted Total Years",{{"Age", each Number.Round(_, 1), type number}}),
        #"Removed Columns1" = Table.RemoveColumns(#"Rounded Off1",{"Age"}),
        #"Rounded Off" = Table.TransformColumns(#"Removed Columns1",{{"Total Years", each Number.Round(_, 1), type number}}),
        #"Renamed Columns1" = Table.RenameColumns(#"Rounded Off",{{"Total Years", "Age"}}),
        #"Added Conditional Column5" = Table.AddColumn(#"Renamed Columns1", "PayClass FT", each if [PayClass] = "FT" then 1 else 0),
        #"Inserted Age1" = Table.AddColumn(#"Added Conditional Column5", "Age.1", each Date.From(DateTime.LocalNow()) - [HireDate], type duration),
        #"Renamed Columns2" = Table.RenameColumns(#"Inserted Age1",{{"Age.1", "Tenure1"}}),
        #"Calculated Total Years" = Table.TransformColumns(#"Renamed Columns2",{{"Tenure1", each Duration.TotalDays(_) / 365, type number}}),
        #"Rounded Off2" = Table.TransformColumns(#"Calculated Total Years",{{"Tenure1", each Number.Round(_, 1), type number}}),
        #"Renamed Columns3" = Table.RenameColumns(#"Rounded Off2",{{"Tenure1", "Tenure"}}),
        #"Added Conditional Column6" = Table.AddColumn(#"Renamed Columns3", "Tenure1+", each if [Tenure] >= 1 then 1 else 0),
        #"Added Conditional Column7" = Table.AddColumn(#"Added Conditional Column6", "Scholarship%", each if [Scholarship] = true then 1 else 0),
        #"Duplicated Column" = Table.DuplicateColumn(#"Added Conditional Column7", "Tenure1+", "Tenure1+ - Copy"),
        #"Added Conditional Column8" = Table.AddColumn(#"Duplicated Column", "Tenure 10+", each if [Tenure] >= 10 then 1 else 0),
        #"Renamed Columns4" = Table.RenameColumns(#"Added Conditional Column8",{{"Tenure 10+", "Tenure 10+ count"}, {"Tenure1+ - Copy", "Tenure1+ count"}}),
        #"Added Conditional Column9" = Table.AddColumn(#"Renamed Columns4", "Marital Married Count", each if [MaritalStatus] = "Married" then 1 else 0),
        #"Added Custom" = Table.AddColumn(#"Added Conditional Column9", "OneYRago", each Date.AddYears([DataDate],-1)),
        #"Added Conditional Column10" = Table.AddColumn(#"Added Custom", "TermL12", each if [TermedDate] < [OneYRago] then 0 else if [TermedDate] < [DataDate] then 1 else null),
        #"Added Custom6" = Table.AddColumn(#"Added Conditional Column10", "TermLess365", each Date.AddMonths([TermedDate],-12)),
        #"Added Conditional Column14" = Table.AddColumn(#"Added Custom6", "NewHire12", each if [HireDate] < [OneYRago] then 0 else if [HireDate] < [DataDate] then 1 else null),
        #"Added Conditional Column11" = Table.AddColumn(#"Added Conditional Column14", "Scholarship Count", each if [Scholarship] = true then 1 else 0),
        #"Added Custom1" = Table.AddColumn(#"Added Conditional Column11", "90DaysAgo", each Date.AddMonths([DataDate],-3)),
        #"Added Custom4" = Table.AddColumn(#"Added Custom1", "TermLess90", each Date.AddMonths([TermedDate],-3)),
        #"Added Custom8" = Table.AddColumn(#"Added Custom4", "30DaysAgo", each Date.AddMonths([DataDate],-1)),
        #"Added Conditional Column12" = Table.AddColumn(#"Added Custom8", "Term90", each if [TermedDate] < [OneYRago] then 0 else if [HireDate] < [TermLess90] then 0 else if [TermedDate] < [DataDate] then 1 else null),
        #"Added Custom2" = Table.AddColumn(#"Added Conditional Column12", "180DaysAgo", each Date.AddMonths([DataDate],-6)),
        #"Added Custom5" = Table.AddColumn(#"Added Custom2", "TermLess180", each Date.AddMonths([TermedDate],-6)),
        #"Added Conditional Column13" = Table.AddColumn(#"Added Custom5", "Term180", each if [TermedDate] < [OneYRago] then 0 else if [HireDate] < [TermLess180] then 0 else if [TermedDate] < [DataDate] then 1 else null),
        #"Added Conditional Column15" = Table.AddColumn(#"Added Conditional Column13", "TermNewHire12", each if [TermedDate] < [OneYRago] then 0 else if [HireDate] < [TermLess365] then 0 else if [TermedDate] < [DataDate] then 1 else null),
        #"Added Conditional Column16" = Table.AddColumn(#"Added Conditional Column15", "TermNewHire90", each if [TermedDate] < [OneYRago] then 0 else if [HireDate] < [TermLess90] then 0 else if [TermedDate] < [DataDate] then 1 else null),
        #"Added Conditional Column17" = Table.AddColumn(#"Added Conditional Column16", "TermNewHire180", each if [TermedDate] < [OneYRago] then 0 else if [HireDate] < [TermLess180] then 0 else if [TermedDate] < [DataDate] then 1 else null),
        #"Removed Columns2" = Table.RemoveColumns(#"Added Conditional Column17",{"Tenure1+ count"}),
        #"Added Conditional Column18" = Table.AddColumn(#"Removed Columns2", "Tenure>1", each if [EmploymentStatus] = "terminated" then 0 else if [HireDate] < [OneYRago] then 1 else 0),