[Expression.Error] cannot convert null value to Logical

[MKPartner]

Hi Guys, 

I have two columns where both are without "null" values: 

PV_orig = ([orig_tot_fabric_assgn_qty]*[prod_cost])/4.84)

and

(([g1_metres]+[g0_metres]+[g2_metres]+[dk_metres]+[allow_metres]+[qc_metres])*[prod_cost])/4.84)

Based on two above columne I want to pick higher value using below: 

PV LE = if [PV_real] >= [PV_orig]
then [PV_real]
else [PV_orig]

Final result is error during Power Bi refreshing like in header. I don't understand why even there is no null value to compare and if I pick PV LE column like PV_real or PV_orig seperately then everything is works. I gues, if function is givong a problem but I don't know why.

Thanks for help. 

[v-echaithra]

Hi @MKPartner ,

Thank you for the update. I just wanted to check if the issue has been resolved on your end, or if you require any further assistance. Please feel free to let us know, we’re happy to help!

Thank you 
Chaithra E.

[v-echaithra]

Hi @MKPartner ,

Thank you @KarinSzilagyi , @raisurrahman , @NumaData for your inputs.

We’d like to follow up regarding the recent concern. Kindly confirm whether the issue has been resolved, or if further assistance is still required. We are available to support you and are committed to helping you reach a resolution.

Best Regards,
Chaithra E.

[MKPartner · Accepted solution]

Hello, 

I solved the issue with DAX function. It didn't work for me in Power Query. 

[raisurrahman]

@MKPartner 

I’m fairly certain there are nulls. As @KarinSzilagyi suggested, please check Column quality and Column profile. By default, Power Query infers data types from only the first 1,000 rows. If possible, load the data into an Excel table and review the entire dataset.

Regards,
Rais

[KarinSzilagyi]

Hi @MKPartner just to be 100% sure: Did you check e.g. via "View > Column profile" or "View > Column Quality" in Power Query Editor that there definitely aren't any null-values in any of the columns used in a mulitplication for your added columns?

Don't forget to switch to "Column profiling based on entire data set" if your table has >1000 rows:

[NumaData]

Hi there, 

Perhaps you could wrap your DAX with another if statement to protect against nulls like this:

PV_LE = if (if [PV_real] = null then 0 else [PV_real])
>= (if [PV_orig] = null then 0 else [PV_orig])
then [PV_real]
else [PV_orig]

You could also find the max pretty neatly in Power Query using this M Code as a new Column: 
PV_LE = List.Max({ [PV_real], [PV_orig] }) - Returns the maximum item in the list or the optional default value if the list is empty

Let me know if this helps!
Numa