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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
Anonymous
Not applicable

Cannot convert value '' of type Text to type Number.

I have a column that is a date but is in Text format and i have another column that is a text format as well. Im trying to add them both together into a seperate column to show new dates

 

I dont understand why i keep getting the error above even after i change the data type to date or number and back to text. 

 

This is the formula im using. 

 

Calculated Deadline = 'Table_Name (Calc)'[Dependent Completed Dates] + 'Table_Name (Calc)'[# of Days]
1 ACCEPTED SOLUTION

@Anonymous 
It's probably because you have empty strings there, you can check for the blanks or empty strings first and then execute otherwise do nothing:

 

AddDate = 
IF ( NOT(ISBLANK(Dates_Add[Dependent Completed Dates]) || Dates_Add[Dependent Completed Dates] = ""),DATEVALUE(Dates_Add[Dependent Completed Dates]) + VALUE('Dates_Add'[# of Days]))

 

vojtechsima_0-1649712500751.png

 


If I helped you and you found the solution, please kudo my messages and Accept them as the Solution. Thank you

View solution in original post

10 REPLIES 10
vojtechsima
Resident Rockstar
Resident Rockstar

Hi, @Anonymous 
If you want to add two strings (text) together you have to use this symbol & (instead of +) or function called:

CONCATENATE

 

Anonymous
Not applicable

Im attempting to add these two 

Ronaldo_Gaitan4_0-1649709056333.png

 

Hi, @Anonymous ,
Make sure Date column is Date and # of Days is a number, then creating new Column should work like this:

vojtechsima_0-1649709749961.png

Column = Patients_Calendar[Date] + Patients_Calendar[Index]
Anonymous
Not applicable

So I have to have the date column as a date for it to work with another formula i have running. Is there a work around for me to get this one to work? 

 

Anonymous
Not applicable

Sorry i meant i have to have it as a text*** 

@Anonymous 
If you don't wanna change the column, you can use something like this:

Column = DATEVALUE(Patients_Calendar[Date]) + VALUE(Patients_Calendar[Index])


DATEVALUE takes Date value from TEXT.
VALUE takes Number values from TEXT. 

Anonymous
Not applicable

Now im getting this error 

 

Ronaldo_Gaitan4_0-1649712009382.png

 

@Anonymous 
It's probably because you have empty strings there, you can check for the blanks or empty strings first and then execute otherwise do nothing:

 

AddDate = 
IF ( NOT(ISBLANK(Dates_Add[Dependent Completed Dates]) || Dates_Add[Dependent Completed Dates] = ""),DATEVALUE(Dates_Add[Dependent Completed Dates]) + VALUE('Dates_Add'[# of Days]))

 

vojtechsima_0-1649712500751.png

 


If I helped you and you found the solution, please kudo my messages and Accept them as the Solution. Thank you

Anonymous
Not applicable

This worked! Thank you so much!

@Anonymous 
Happy to help.

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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