Forum Discussion
Expression error in one column and not the other
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
3 Replies
- edhans
Community Champion
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.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 Anonymous - still need data here to troubleshoot. Links above for guidance on that.
