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

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
Ronaldo_Gaitan4
Frequent Visitor

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

@Ronaldo_Gaitan4 
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
Memorable Member
Memorable Member

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

CONCATENATE

 

Im attempting to add these two 

Ronaldo_Gaitan4_0-1649709056333.png

 

Hi, @Ronaldo_Gaitan4 ,
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]

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? 

 

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

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

Now im getting this error 

 

Ronaldo_Gaitan4_0-1649712009382.png

 

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

This worked! Thank you so much!

@Ronaldo_Gaitan4 
Happy to help.

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

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

Top Solution Authors