Forum Discussion
Null values creating new errors in edit query conditionals
Sorry, I didn't even realize I could click on the "error" to get more details... here is the error:
Super UserInteresting. It sounds like you have some sort of if null then ... rather than if x = null then ... happening.
Please share the M code.
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),- AlexisOlson
The comparisons here will throw errors if one or both are null.
try ( if [TermedDate] < [OneYRago] then 0 else if [HireDate] < [TermLess90] then 0 else if [TermedDate] < [DataDate] then 1 else null ) otherwise nullYou can use try ... otherwise ... for error catching.
I appreciate your help on this, what I still can't figure out is why this changed. I had it set up this way for 6 months or more without any null errors, and now they are throwing errors. It seems like the standard BI "add conditional column" interface doesn't have this "otherwise" logic as an option, so I presume I would have to add that in advanced editor? What I did is use the "replace errors" function to clean up my report, but still unsure of what changed to give me the errors.