Error: DataFormat.Error: We couldn't convert to Number. Details: #N/A

[Ishi_garg]

When I am trying to expand the column after merging two tables that have a common column and the same data type, I recieve the following error. 

[NestorKartio]

I had the same error, could not convert to number but the problem was that some of my numbers had over 19 decimals. Hope this helps somebody. 

[CrazyAnalyst]

Hi,

I was looking here for an answer, but eventually I found one on my own.

Here it goes: remove "change type" entry before merging or assign all columns to text.

[edhans]

In your case @CrazyAnalyst you must have Type Detection on, and it created the change type step, and did it incorrectly.

You don't need to remove it, nor make everything text. You just need to make it correct. For example, Power BI will usually detect numbers in US zip codes so automatically make that a whole number. But if later in the data (it only looks at 200 rows to decide the type) there are 5+4 zips, (12345-4444), or foreign code like Canada that has letters, it creates errors. 

That is why I have that feature off. I know my data better than Power BI, so I set my data types correctly based on my knowledge of the business.

[AgnieszkaFN]

THANK YOU! that resolved the issue! 

[Jimmy801]

Hello @Ishi_garg 

have you been able to solve the problem with the replies given?

If so, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

All the best

Jimmy

[edhans]

Somewhere in your code-country-region table in the Region field, you have an #N/A value. It may not be showing itself in the main table because Power Query doesn't evaluate every record when designing queries. Generally only the first 1,000 rows.

But when you merge, it forces PQ to do a more detailed analysis and this is showing up now.

In your code-country-region table, go to the last step and select Keep errors as shown:

That will show you the record(s) with the error(s).

Then either fix the data there by either filtering them out if those records aren't relevant, replacing it with null, or fix it in your source. Then remove the "Keep Errors" step once done trouble shooting.

[Jimmy801]

Hello @Ishi_garg 

most probably you forced column type change into number to do the merge, but not all of your data were numbers, and so somewhere in these columns you have a converting error. When you then merge queries with a column that contains that error, you will get this message

Jimmy