cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Ishi_garg
New Member

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

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. 

 
 

Error.PNG

5 REPLIES 5
CrazyAnalyst
New Member

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.

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

 

edhans_0-1617286217095.png

 

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.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting
Jimmy801
Super User III
Super User III

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
Super User III
Super User III

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:

2020-01-14 07_37_59-Data - Power Query Editor.png

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.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting
Jimmy801
Super User III
Super User III

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

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

Top Solution Authors
Top Kudoed Authors