Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
freidus
Regular Visitor

DataFormat.Error: We couldn't convert to Number.

Hi

 

I am a real beginner in Power BI and I would greatly appreciate some help.

 

I am importing a large number of rows from 8 csv files (about 3m rows).

 

A large number of these rows is coming up with an error. All the errors seem to be related to putting numbers in a number format.

I am getting the error DataFormat.Error: We couldn't convert to Number.

 

However, they are all actually numbers. Here is one example:

 

DataFormat.Error: We couldn't convert to Number.
Details:
    -2146457.58

 

I thought that it might be that the data type was set to whole number instead of decimal. However, I changed that, refreshed the preview, and it still comes out as an error.

 

Thanks so much.

Dani

12 REPLIES 12
freidus
Regular Visitor

Sorry for the delayed response, but I have been struggling to reproduce the error in a data excerpt. 

 

It may be though, that once I upgraded to the November version, the problem has become sorted. I still need to load the full dataset to see if this is the case. 

 

I will revert with feedback.

 

Thank you all again for your help.

@freidus

How is the issue going?

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Thanks for checking up on this.

 

I actually am at a total loss and I've tried to work around the issue.

 

I managed to clean up my data in Python which seems to skirt the issues I was facing.

 

However, even with this clean data, it seems that when I change the data format using the query editor, errors are shown. However, when I do it in the data view using the modeling tab, it works.

 

Seems like I'm just going to have to use the workarounds...

freidus
Regular Visitor

Thank you all for your help.

 

I have, to some extent, managed to work around the issue by using Excel files, instead of CSV files, as the source.

 

Still don't understand what was wrong with the CSV files.

 

I am going to copy an excerpt of my data (from the original csv, opened with a text editor), to see if anyone can pick up formating issues:

 

03/07/2009,7,2009,1400000,6,30/07/2009,Jul-09,0,100,Out_Period,94,80,-370.18,-370.18,Advance,348.83,0,2146457.58,1395.32,-2146457.58,100,

 

The item with the issues is the "-2146457.58". Please excuse the random extract, but the data are confidential.

No issues with your excerpt.

 

If the previous tips didn't help, then maybe you can share a file (Excel or pbix) with only that single value (in a query) that is having the issue.

Specializing in Power Query Formula Language (M)
freidus
Regular Visitor

Thank you both for your suggestions. However, I have tried what you have suggested with no luck.

 

Even when I change the type to text, which I understand should be able to read anything, it doesn't work with these specific cells.

 

The delimiter that I am using is commas. When I view the file in Excel, there is no issue with these numbers and they are dealt with correctly.

 

I should note that in the columns where I am having the issue, it is only the numbers with decimal points that are not being read correctly.

Can you share the code part where the numbers are comverted, so we can see what Locale code you are using?

 

Does your input have decimal commas or decimal points?

Or maybe a mix of commas and points?

Specializing in Power Query Formula Language (M)

Thank you so much for your help.

 

The code is as follows:

 

= Table.TransformColumnTypes(#"Changed Type1", {{"Actual_Receipts", type number}}, "en-GB")

The numbers in this particular column are all negative (preceded with a - ) or are 0. Some of the numbers do not have decimal points (eg -145101) and some do (eg -2146457.58). It seems that the numbers with decimals are the ones resulting in errors.

 

As far as I can tell, all the numbers with decimals all have decimal points (none with decimal commas).

 

Thanks again so much!

Anonymous
Not applicable

If you can upload an example of your data (copy paste from excel?) we can test some things for ourselves.

 

You could try to replace the comma in your file with a freshly typed comma? I think the other guy is right, and that your commas arent actually commas 😛

The only things I can think of:

1. The decimal point is some other character than a normal period (which has decimal unicode 46), and/or:

2. There are additional unprintable characters in your text. You can remove these by using Transform - Format - Clean.

Specializing in Power Query Formula Language (M)
Anonymous
Not applicable

Yeah you can also try the ""Locale" option instead of the decimal value. It probably has something to do with powerBI expecting comma's and receiving periods, or vice versa.

 

With "Locale" you can adjust the data to the locale that you got it from, like "A number from the Netherlands" 🙂

 

locale.png

dilumd
Impactful Individual
Impactful Individual

Hi

 

I think you should check the delimiter again? Also check the all headers of your data set. This is most probably due to characters in your column which your trying to convert into a number.

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.