Forum Discussion
Advocate IDataFormat.Error: We couldn't convert to Number but I am using Text columns
I get this error in both PowerBI Desktop Query Editior, and in Excel Power Query, when I am merging two queries.
Each query only has a few columns and they are all formatted as Text. I have columns like "Part_No" and "Commodity", and there are no numeric columns at all in either query. (There are quantities and costs in the inital load of the data but I delete those colunms in earlier steps prior to the Merge step that I am talking about here).
When I try to Expand the result, I get the error "DataFormat.Error: We couldn't convert to Number" and a specific part number. I have tried filtering that part number out of both queries but I still get the error.
I cannot figure out why it is trying to convert to Number when everything in both queries is formatted as Text.
Any solutions?
Sometimes PowerQuery will try to help by adding a step of "Change Type" where it looks at the first 1000 rows and tries to determine if it is a number or text etc. Did it add a step like that when you loaded your data from the source? If so, you can just delete that step to stop it from trying to convert.
9 Replies
- jdbuchanan71
Super User
Sometimes PowerQuery will try to help by adding a step of "Change Type" where it looks at the first 1000 rows and tries to determine if it is a number or text etc. Did it add a step like that when you loaded your data from the source? If so, you can just delete that step to stop it from trying to convert.
Thanks all!
