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