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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

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
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

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

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.