Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
dd88
Resolver I
Resolver I

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

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

Text Date.png

 

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

1 ACCEPTED SOLUTION
v-zhengdxu-msft
Community Support
Community Support

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:

vzhengdxumsft_0-1712639982084.png

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:

vzhengdxumsft_1-1712640213873.png

 

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.

View solution in original post

4 REPLIES 4
v-zhengdxu-msft
Community Support
Community Support

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:

vzhengdxumsft_0-1712639982084.png

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:

vzhengdxumsft_1-1712640213873.png

 

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.

@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))
 
 
 
Days Difference from TODAY - 01.png
 
I would like to know why there is a discrepancy in power bi compared to excel results. is it my formula
 
TIA
mahenkj2
Solution Sage
Solution Sage

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
Solution Supplier
Solution Supplier

@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.

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.