Forum Discussion

Yaniv's avatar
Yaniv
Helper I
9 years ago
Solved

Insert blank value based on another field

Hi,

 

I have a table named 'service'.

I want to create a new calucuated column that says if the field 'done' (date field) is blank do not insert any value but if the 'done' field is not blank insert the date value+one day.

Below what I already tried - 

 

1. NewColumn= IF(Service[Done]=BLANK(),BLANK(),Service[Done]+1) 

I get the error message - Column '' in Table '' contains blank values and this is not allowed for columns on the one side of a many-to-one relationship or for columns that are used as the primary key of a table.

 

2. NewColumn = IF(Service[Done]=BLANK()," ",Service[Done]+1)

Error message: Expressions that yield variant data-type cannot be used to define calculated columns.

 

Does anyone have an idea how to resolve it? I'm getting frustrated :smileyhappy:

 

  • v-yulgu-msft's avatar
    v-yulgu-msft
    8 years ago

    Hi Yaniv,

     

    I changed the data type of Service[Done] from Text to Date/Time, delete and recreate the calculated column Service[DoneDate], everything worked.

     

    I have sent you the modified pbix file through private message.

     

    Best regards,
    Yuliana Gu

17 Replies

  • v-yulgu-msft's avatar
    v-yulgu-msft
    Microsoft Employee
    9 years ago

    Hi Yaniv,

     

    For this formula:  NewColumn = IF(Service[Done]=BLANK()," ",Service[Done]+1)

    As " " is a string value, while Service[Done]+1 returns date value, two different data types cannot exist in a single column. Otherwise, variant data-type error will prompt.

     

    For this formula: NewColumn= IF(Service[Done]=BLANK(),BLANK(),Service[Done]+1)

    It works fine and returns expected date in my test. I didn't the same error. So, in your scenario, were you creating calculated column in service table? How many tables and columns are referred to in your formula? Please provide more information so that I can reproduce your problem.

     

    Best regards,
    Yuliana Gu

    • Yaniv's avatar
      Yaniv
      Helper I
      9 years ago

      Hi v-yulgu-msft

       were you creating calculated column in service table? - Yes, the calcuated column is in the same table (service).

      How many tables and columns are referred to in your formula? - Only 1 table and one column.

       

      Thanks!!

      • v-yulgu-msft's avatar
        v-yulgu-msft
        Microsoft Employee
        9 years ago

        Hi Yaniv,

         

        I cannot reproduce your problem. Please share some sample data so that I can test for you. Or, if possible, share your pbix file.

         

        Regards,
        Yuliana Gu