Expression error in one column and not the other

[dmichel729]

Hello all,

I have the following query in one column. The "Late" status is the only  status that populates errors within the column (Red font). The error states : Expression.Error: We cannot convert the value null to type Logical.Details:Value=Type=[Type]. I made all null values in the field [#"Actual Initial 3 - Week Review #"]  as"N/A"

= Table.AddColumn(#"Changed Type12", "Revision Status Initial", each if [#"Scheduled Initial 3 - Week Review (8 weeks)"] > Date.AddDays(DateTime.Date(DateTime.LocalNow()),7) and [#"Actual Initial 3 - Week Review #"] = "N/A" then "On Track" else if [#"Scheduled Initial 3 - Week Review (8 weeks)"] < [#"Actual Initial 3 - Week Review"] then "Completed Late" else if [#"Scheduled Initial 3 - Week Review (8 weeks)"] > [#"Actual Initial 3 - Week Review"] then "Completed On-Time" else if [#"Scheduled Initial 3 - Week Review (8 weeks)"] <= Date.AddDays(DateTime.Date(DateTime.LocalNow()),7)and [#"Scheduled Initial 3 - Week Review (8 weeks)"] >= DateTime.Date(DateTime.LocalNow()) and [#"Actual Initial 3 - Week Review #"] = "N/A" then "At Risk" else if [#"Scheduled Initial 3 - Week Review (8 weeks)"] < DateTime.Date(DateTime.LocalNow()) and [#"Actual Initial 3 - Week Review #"] = "N/A" then "Late" else null)

However when I place the exact same expression for "Late" status alone in a different column, the values populate with no errors

= Table.AddColumn(#"Revision Status Initial", "Custom", each if [#"Scheduled Initial 3 - Week Review (8 weeks)"] < DateTime.Date(DateTime.LocalNow()) and [#"Actual Initial 3 - Week Review #"] = "N/A" then "Late" else null)

Any suggestions or thoughts?

-Dylan

[edhans]

I'd have to see some sample data. There are on obvious issues in the code, but I also don't understand why you have two columns with these names - "Actual Initial 3 - Week Review #", "Actual Initial 3 - Week Review" - may be totally legit, just seems weird - but I also don't understand your data like you do. 

How to get good help fast. Help us help you.

How To Ask A Technical Question If you Really Want An Answer

How to Get Your Question Answered Quickly - Give us a good and concise explanation
How to provide sample data in the Power BI Forum - Provide data in a table format per the link, or share an Excel/CSV file via OneDrive, Dropbox, etc.. Provide expected output using a screenshot of Excel or other image. Do not provide a screenshot of the source data. I cannot paste an image into Power BI tables.

[dmichel729]

Hi Edhans, 

The reason I have the two columns is that  "Actual Initial 3 - Week Review #" is formated as text therefore I replaced all null values with N/A , "Actual Initial 3 - Week Review" is formated as a date. Same data, just different format. I agree it is strage, but it was my own way of trying to get the code to work. Essentially the data I am trying to compare are date fields. 

[edhans]

Understood @dmichel729 - still need data here to troubleshoot. Links above for guidance on that.