Forum Discussion

ovl's avatar
ovl
New Member
8 years ago

Conditional column - cannot convert the value null to type text

Hi,

 

I am trying to add a conditional column that shows "complete" when XYZ columns contain a value (0 included), and "not complete" when any of XYZ columns contains a blank cell (null).

 

I have added a conditional column as follows:

 

If [columnX] equals [null] then [not complete]

Else if [columnY] equals [null] then [not complete]

Else if [columnZ] equals [null] then [not complete]

Otherwise ["complete"]

 

The funny thing is that the conditional columns works as expected in the query editor but when I close&apply the query, it shows the error "cannot convert the value null to type text". How can I check if a cell that contains a number is blank if the value "null" is considered as a text type by the operator?

 

Any ideas will be greatly appreciated!

Thanks!

3 Replies

  • ovl's avatar
    ovl
    New Member
    8 years ago

    Hi,

     

    I am trying to add a conditional column that shows "complete" when XYZ columns contain a value (0 included), and "not complete" when any of XYZ columns contains a blank cell (null).


    I have added a conditional column as follows:

     

    If [columnX] equals [null] then [not complete]

    Else if [columnY] equals [null] then [not complete]
    Else if [columnZ] equals [null] then [not complete]

    Otherwise ["complete"]

     

    The funny thing is that the conditional columns works as expected in the query editor but when I close&apply the query, it shows the error "cannot convert the value null to type text". How can I check if a cell that contains a number is blank if the value "null" is considered as a text type by the operator?

     

    Any ideas will be greatly appreciated!

    Thanks!

  • v-jiascu-msft's avatar
    v-jiascu-msft
    Microsoft Employee
    8 years ago

    Hi ovl,

     

    Can you share the formula here please? Did this happen after you added the column?

     

    Best Regards,

    Dale

    • ovl's avatar
      ovl
      New Member
      8 years ago

      Hi Dale,

       

      Thanks for quick reply!

       

      Here is a screenshot of what I was trying to explain:

       

      As you can see below, when I click "ok", the column is added and works as expected (in the query editor). However, when I close and apply the query, it shows me the error "we cannot convert the value null to type text" and the column is not added.

       

      Thanks for your help!

      Oriol