Error while adding Custom Column in Power Query

[sureshrawat048]

Hi Team, getting errow on applying if condition in powerquery custom column.

Wanted to achieve : if condition 1 if column contain date after 1st Jan 2023 or column 2 contain value length > 9 then "Yes" Else "No" and in case of no criteria matches then it should not through error it shouldshow null value. 

[v-hashadapu · Accepted solution]

Hi @sureshrawat048, Hope your issue is solved. If it is, please consider marking the answer 'Accept as solution', so others with similar issues may find it easily. If it isn't, please share the details.
Thank you.

[v-hashadapu]

Hi @sureshrawat048 , Hope your issue is solved. If it is, please consider marking the answer 'Accept as solution', so others with similar issues may find it easily. If it isn't, please share the details.
Thank you.

[v-hashadapu]

Hi @sureshrawat048 , Hope your issue is solved. If it is, Please consider marking the answer 'Accept as Solution', if it isn't, please share the details along with sample data for us to work to solve it. Thank you.

[ZhangKun]

Because the condition of the if keyword cannot be null. The meaning of null is missing and unknown, and null obviously cannot be used for "judgment".You need to make additional judgments when the value of the "conditon1" column or the "numeric cell" column is null.

In addition, although the expression "if[Condition 1]" can be recognized by the program, it is better to write it as "if [Condition 1]" (add a space character).

[sureshrawat048]

[tharunkumarRTK]

@sureshrawat048 

I believe you are using Power Query in Excel. What is the result of this custom column in Power Query data preview? is there any error? if yes then please share the error message. If you are not able to see the error in the preview that does not mean there are no errors, by default power query profiles only 1000 rows, So I would suggest you to click on "Keep Errors" transformation and find the error message. 

I am guessing Date.FromText could be a possible reason for the errors. If Power Query cannot parse it as a proper date then it can through an error. You need explictly mention the format. For example 

Date.FromText( "2023-31-12", [Format="yyyy-dd-M" ] )

https://powerquery.how/date-fromtext/

Please share the error message for further assistance 

Need a Power BI Consultation? Hire me on Upwork

Connect on LinkedIn

Did I answer your question? Mark my post as a solution!
If I helped you, click on the Thumbs Up to give Kudos. Proud to be a Super User!

[sureshrawat048]

Hi, Error : 

Expression.Error: We cannot convert the value null to type Logical.
Details:
Value=
Type=[Type]

[tharunkumarRTK]

@sureshrawat048 

This error message suggests that you have a column with null values and you are using it as logical value (TRUE/FALSE) in an expression.

Handle this exception and see it that helps

Need a Power BI Consultation? Hire me on Upwork

Connect on LinkedIn

Did I answer your question? Mark my post as a solution!
If I helped you, click on the Thumbs Up to give Kudos. Proud to be a Super User!

[Omid_Motamedise]

click on one error and share the text related to the error.

I think it con be solve by replacing if[Condition1] with if Date.From([Condition 1]) and also replacing [#"numerical cell"] with [Numerical Cell]