Forum Discussion
Expression.Error: We cannot convert the value to type Text.
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
Hi Anonymous,
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
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.
Excellent, this makes sense. Converting the column to Text format and then performing the operations would help in most cases.
Thanks sunnsunofindia, this solved the issue for me 🙂
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
I just wonder how is that possible that some string or number can not be converted to text?
