Column has mix of date & text. Convert string to Date and obtain number of days from date and today

[dd88]

The datasource is an Excel spreadsheet and contains a column 'Date'. however is formatted as text.This allows users to enter the date, or text N/A or leave blank.
eg

in powerbi , if I convert the column to Date format with the steps ..
select Transform data, select column, in Transform, select Detect Data Type
The result the 'dates'convert to Date format. However the N/A is converted to Error and blanks converted to Null

In PowerBI I need a formula that reads the string, and by the value if N/A, blank or date then converts string to date, and then works out the number of days from the date and today.

Do I need something like
btain the string, if not n/a or blank convert string to date?
calcualte the number of days from date and today?

TIA

[v-zhengdxu-msft · Accepted solution]

Hi @dd88 

Thanks for the reply from @mahenkj2 and @AnalyticsWizard, please allow me to provide another insight:

Here I create a set of sample data:

Then we can use the dax DATEVALUE() to convert the text to datetime.

So add a calculate column to works out the number of days from the date and today:

Datediff =
IF (
    'Table'[Date] <> "N/A"
        && 'Table'[Date] <> BLANK (),
    DATEDIFF ( DATEVALUE ( 'Table'[Date] ), TODAY (), DAY )
)

The result is as follow:

Best Regards

Zhengdong Xu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[dd88]

@v-zhengdxu-msft 

I used the dax and amended. The formula is great as it cleans up the N/A and blanks. however the days difference returned is incorrect.

In Excel, I performed the caculations of date different from TODAY.  These are the results

ANNUAL EXPIRY - Copy		TodaysDate		DaysDiff
10/05/2025		29/04/2024		11
8/01/2025		29/04/2024		254
23/10/2024		29/04/2024		177

DateDiff = IF('Table_INPUTDATA'[ANNUAL EXPIRY - Copy]<> "N/A" && 'Table_INPUTDATA'[ANNUAL EXPIRY - Copy] <> BLANK(), DATEDIFF(DATEVALUE('Table_INPUTDATA'[ANNUAL EXPIRY - Copy]), TODAY(), DAY))

I would like to know why there is a discrepancy in power bi compared to excel results. is it my formula

TIA

[mahenkj2]

HI @dd88 ,

For an easier reporting perspective and proper rule to keep one kind of data in one column, in Power query you may duplicate the column as 'Expiry date' with conditional column (if NA or blank then blank else PC EXPIRY). This column will be your proper date column which you can as well later connect to date table if needed.

Then you can further duplcate PC expiry column as conditional column with (if NA or blank then no expiry else expiry available). This column can be used in report as filter if needed.

Finally remove PC expiry column. 

You may play with something like this. but this is a simple strategy which may help you in many times during power bi report development tasks.

Please check if this helps in your situation.

[AnalyticsWizard]

@dd88 

To calculate the number of days from the date in the ‘Date’ column to today in Power BI, while handling “N/A” and blank entries, you can use the following Power Query M code:

let
    Source = Excel.CurrentWorkbook(){[Name="YourTableName"]}[Content],
    ChangedType = Table.TransformColumnTypes(Source,{{"Date", type text}}),
    CustomFunction = (dateText) => if dateText = "N/A" or dateText = "" then null else Duration.Days(DateTime.LocalNow() - DateTime.FromText(dateText)),
    AddedCustom = Table.AddColumn(ChangedType, "DaysFromToday", each CustomFunction([Date]))
in
    AddedCustom

Replace "YourTableName" with the actual name of your table. This script transforms the ‘Date’ column to text, checks if the value is “N/A” or blank, and if not, converts the string to a date and calculates the difference in days from today. If the value is “N/A” or blank, it returns null. Remember to add this code in the Advanced Editor in Power Query.