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

[dyabes]

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. 

[AMIDINEX]

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 FuctionsTransform Text

[markandrew]

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]

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]

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]

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

[v-jiascu-msft]

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

[russellclaude]

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.

[dyabes · 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

[sunnsonofindia]

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.

[allenabraham]

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

[v-jiascu-msft]

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

[gvg]

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