Insert blank value based on another field

[Yaniv]

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 

[v-yulgu-msft]

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]

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]

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

[Yaniv]

I hope the attached screenshot will make it clear.

[v-yulgu-msft]

Hi @Yaniv,

I noticed that in formula, the source column is Service[Done], but in data view, you pointed out that source column is [Created], why? Also, from the error message, what is the "in Table" column? Did you created any relationship between this table and another table?

Regards,
Yuliana Gu

[Yaniv]

HI @v-yulgu-msft,

Yes, there is a mistake in the screenshot. The column source is done and not 'created' but they are same data type (date). I haven't created any relationship for this table.

Appreciate your assistance. 

[v-yulgu-msft]

Hi @Yaniv,

Could you please share your pbix file?

Regards,
Yuliana Gu

[Yaniv]

@v-yulgu-msft,How can I shre it with you? I haven't find an option to attach file to a message here.

Am I missing something? 

[v-yulgu-msft]

Hi @Yaniv,

Can you see this option when posting a reply?

Regards,
Yuliana Gu

[Yaniv]

@v-yulgu-msft - Unfortunately, I can't. I just have the option ot attach an image file. 

Please see the screenshot below.

[Yaniv]

@v-yulgu-msft, I just see in the below post that regular users can't share files here.

https://community.powerbi.com/t5/Desktop/Anyone-knows-how-to-attach-sample-files-to-a-post-here/td-p...

[v-yulgu-msft]

Hi @Yaniv,

As suggested in above link, could you please upload and share the sample files on OneDrive, Dropbox etc?

Regards,
Yuliana Gu

[Yaniv]

Hi @v-yulgu-msft,

I sent you a private message.

[v-yulgu-msft · Accepted solution]

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

[Yaniv]

Hi @v-yulgu-msft,

I also tried to do that but the option of changing to date format was grayed out.

Can you please advise me how did you do that?

[v-yulgu-msft]

Hi @Yaniv,

Was my suggestion helpful to you? If yes, would you please kindly mark the corresponding reply as an answer?

Best regards,
Yuliana Gu

[Yaniv]

HI @v-yulgu-msft,

Yes, there is a mistake in the screenshot. The column source is done and not 'created' but they are same data type (date). I haven't created any relationship for this table.

Appreciate your assistance.