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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.