Renaming blank values in a date / number column

[Matt22365]

Hi

I have some excel source data where a couple of columns have IF statements to show a text caveat as to what blank means i.e. 1 columns function is to show a target lauch date, however some rows do not have a date set so an IF statements returns "NO LAUNCH DATE SET" if the value is blank. In Excel I can still format that column as a Date.

However when this is transfered to BI it defaults to a text column because of the caveat and I cannot change the format to date. This stops me using this column as a relative date filter.

As I need the functionality of both types of format (date for filter and text for table visual) can I either:

1 - Chnge the format to date but keep the wording

or

2 - remove the IF statement, leave blank rows blank, and change the value of blank to "NO LAUNCH DATE SET" in BI somewhere

Thanks for your help

Matt

[v-lili6-msft]

hi, @Matt22365

If there are text rows in your date column that leads to the whole column format is text?

IF so, you could change the data type to date in Edit Queries

then you could filter these "Error" or replace them.

Best Regards,

Lin

[Matt22365]

Hi

Yes thats correct.

Is there a way to automatically convert the errors into specific text? The data is constantly being updated so I was trying to avoid a manual step to remove errors

Thanks for your help

Matt

[v-lili6-msft · Accepted solution]

hi, @Matt22365

You could only replace the error with blank, and I recommend you this way as below:

Step1:

Duplicate the basic data table before change into date

Step2:

filter the data "NO LAUNCH DATE SET"

Step3:

Then do not do any change for this duplicate table and It is stored here as backup error data, and it will refresh automatically  with basic data.

Best Regards,

Lin

[Matt22365]

Hi

Thanks for your help, its a shame that you cannot have multiple formats in one column as you can in excel.

This has given me a workaround though so thank you

Matt