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

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