Converting Value to Number Error

[mmossman]

I have a column of serial numbers where the majority of the data is numeric but there are some that are alphanumeric. I have the column formatted to text but I am still getting an "error converting value to number" message. The result is that all of those serial numbers just say ERROR instead of the serial number. This column is critical to a some downstream matching processes. How can I resolve this?

[v-jingzhang · Accepted solution]

Hi @mmossman 

Usually Power Query will change column data types automatically after you connect to the data source at the first time. Please click the first "Changed Type" step in Applied Steps pane to check whether this step has the same error. If it does have the same error, remove the step. Then modify the serial number column to Text type manually. Change data type of other columns too. 

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

[mmossman]

That did it. I also found some additional steps where PowerQuery kept trying to change it back so I fixed those also. No errors on refresh anymore.

[HotChilli]

can you post the Advanced Editor code please + tell us the name of the column?