DataFormat.Error: We couldn't convert to Number but I am using Text columns

[fcerullo]

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?

[Excel_john]

Thanks all!

[jdbuchanan71 · Accepted solution]

@fcerullo 

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.

[amessing]

I have this exact error and when the files are expanded it assigned a type of text, which seems correct.  Any other ideas of what could be going wrong?

[RTriangle]

Thanks a lot!

[Anonymous]

This helped me aswell...thanks

[fcerullo]

I added a step to make the format Text on all columns of these 2 queries.

[fcerullo]

oh, I see what your saying JD, I will take that auto-created step out and then do my own transformation.

[fcerullo]

This worked!

The PQ was directing me to an error at a the Expand table step, but, it was actually caused earlier in my transformation.