Forum Discussion
Helper IExpression.Error: We cannot convert the value 1110011 to type Text.
We have a daily report that we run with no issues. Today we received the "Expression.Error: We cannot convert the value 1110011 to type Text." message and it doesn't make sense. The source spreadsheet has the ID data formatted as General. Some of the IDs begin with two letters and some of them do not (e.g., "SP123" and "2435"). The vast majority of the IDs begins with two letters.
In Power Query, I imported the source spreadsheet like normal and removed any records that are missing IDs. My final applied step is to change the entire column to type text. No issues. However, when I filter for 1110011, I get the Expression.Error message. What's even stranger is that if I filter for a 12345, I get the Expression.Error message, but it still has 1110011 instead of showing 12345:
I haven't changed anything so I'm not sure why I'm getting this message. Please advise.
5 Replies
- Vijay_A_Verma
Most Valuable Professional
At the bottom, you will be able to see column profiling based on Top 1000 rows. Click it and change it to entire data set.
Now, go to View menu and check column quality box. See if any column (not necessarily this column) is showing Error.
If yes, go to Home menu - Keep rows - Keep errors
Then you will be able to analyze error.
Jeffrey2Thank you for the response. I did not know about that feature so that was good to know.
However, strangely enough, every column shows Valid 100%. When I select "Keep Errors", I still get the Expression.Error: We cannot convert the value 1110011 to type Text. message.
- Vijay_A_Verma
Create a duplicate query and before the final step, Home, remove rows, remove blank rows and then see what final step is giving.
