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