cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
dyabes
Helper I
Helper 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

5 REPLIES 5
v-jiascu-msft
Microsoft
Microsoft

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.

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

View solution in original post

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
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.