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, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
dyabes
Advocate I
Advocate I

Expression.Error: We cannot convert the value to type Text.

I keep getting error "Expression.Error: We cannot convert the value 1043672 to type Text." whenever I try to merge two of my tables even though the columns used to match the tables were explicity defined as text for both tables. Examples of the data within the column are H007074686, H007074680, H007074689 but older data were numeric such as 1043672 which is where the error is coming from.

 

I'm not sure why the error despite the explicit definition that values of the column is Text. 

1 ACCEPTED SOLUTION

I did find what was throwing off the error.

 

The original source of the table was a folder with dozens of Excel files with multiples sheets within them. Combining these sheets with PQ into a single table,  I missed a step by removing Rows that are blank. When I explicitly defined the column to be Text, which both contains values that can are Text (H007074686, H007074680, H007074689) and Number (1043672) as well as the Blank rows, the step was throwing off an error on both the Blank and Number values. When I removed the Blank rows, Change Type now works and the Merge is no longer throwing off the error.

 

Perfect. But still no idea why the blank row would mess with the Change Type step. Hopefully someone can shed some light.

 

Cheers,

David

View solution in original post

13 REPLIES 13
AMIDINEX
New Member

There is a SIMPLE answer to your question, Value will not be coverted into Text if it has spaces or blank values. So whenever you are using a Text Function, you need to check your Row data shoud not have any spaces or blanks. For example : if a text is ""D990-56789"" , but its stored in the row like this ""     D990-56789  "" , this means you need to Transform the Text i.e Trim it.

 

Hence just check your "To be tranformed Column", you will get the ANSWER. Also refer to attachement for reference.

Use of Text FuctionsUse of Text FuctionsTransform TextTransform Text

markandrew
Regular Visitor

In case anyone else stumbles upon this thread, I believe I have a solution. 

 

I have Power Query importing data from an Excel file saved on SharePoint. There are approximately 2000 rows in the table, and I was getting errors for 9 rows in one particular column.

 

The data in the column contained both text and number values, so I had the data type set as text in Power BI. Nonetheless, 9 of the values were causing an error. There were no obvious issues with the data in those rows, in fact I had the exact same values in other rows, and those other rows were not causing any errors. This led me to open the Excel file itself, which is when I noticed the formatting of those cells in Excel was different. For the column in question, there were 9 cells formatted as "General" while all the others were formatted as "Text".

 

Once I changed the entire column to be formatted as "Text", in the Excel file, I refreshed the query and everything imported just fine.   

KaranSharma386
New Member

This thing worked for me:: Try this out.

 

Change the Data Type to Text in your Source Data. You wont face the error while performing the step.

turbofred69
Frequent Visitor

Hi all,

 

I got a very similar issue: some rows out of many (1000+) can not convert text whereas all the rows contain the same value in the XLS file.

 

Expression.Error: We cannot convert the value 2174214 to type Text.
Details:
Value=2174214
Type=[Type]

 

I can't figure out what is wrong. I've tried to repair teh XSL file but no effect

Keybonesabi
New Member

Have ensured you are not trying to join a numerical value to a text field?

v-jiascu-msft
Microsoft Employee
Microsoft Employee

Hi @dyabes,

 

Usually, we can change the type in the Query Editor to avoid such errors. Please try it out. If you still have problems, please provide a sample. I tested successfully with your description.

 

Best Regards,

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

I am having the same problem.

 

"We cannot convert the value 10 to type Text"

 

I am simply doing a Power Query Group By on a column full on product UPC codes. It's very frustrating.

I did find what was throwing off the error.

 

The original source of the table was a folder with dozens of Excel files with multiples sheets within them. Combining these sheets with PQ into a single table,  I missed a step by removing Rows that are blank. When I explicitly defined the column to be Text, which both contains values that can are Text (H007074686, H007074680, H007074689) and Number (1043672) as well as the Blank rows, the step was throwing off an error on both the Blank and Number values. When I removed the Blank rows, Change Type now works and the Merge is no longer throwing off the error.

 

Perfect. But still no idea why the blank row would mess with the Change Type step. Hopefully someone can shed some light.

 

Cheers,

David

I know it is very old post but replying if somebody faces the same issue now,

 

Check if the particular column originally had 'Any' format type which mean 'ABC123' then you replaced text prefix/suffix by blank or did some operation which makes the resultant column as 'ABC' from 'ABC123' but you end up having numbers in such column, most probably that should be the case. If yes, change the type to Text before you replace or do any other such operation. This should solve the issue. Let me know if it doesn't work.

Thanks sunnsunofindia, this solved the issue for me 🙂

Excellent, this makes sense. Converting the column to Text format and then performing the operations would help in most cases.

Hi David,

 

That's great. I'm glad you solve it. But I can't reproduce the issue. The root cause could be something else. If you can share the file, I will try to test more.

 

Best Regards,

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

I just wonder how is that possible that some string or number can not be converted to text? 

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

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

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

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