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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
irnm8dn
Post Prodigy
Post Prodigy

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

I have a .pbix file that has been running great for two weeks, refreshing as expeted.  The source of these dashboards are two .csv files.

 

All of a suddent I am receiving the message below:

 

Capture.JPG

 

I have read a lot of the messages in the forum, with no luck in resolving the issue.  Is there a way to identify where the problem is, and how to resolve it?

 

Thanks.

1 ACCEPTED SOLUTION

Did you change the columns in the editor? Make sure you go back and replace the steps - otherwise it will try to convert to number, then back so will still errror out.

 

In the applied steps in the Query Editor, make sure you go back to all the changed type steps and click on them. Then, you can do your tranaformation on this step. Make sure you click replace current.

pp.png

 

 

Power BI usually looks at a subset of rows to determine the format. It is likely you have a column with many numbers and a small amount of text somwhere.



Did I answer your question? Mark my post as a solution! Proud to be a Super User!


Connect with me!
Stay up to date on  
Read my blogs on  



View solution in original post

6 REPLIES 6
SteveCampbell
Memorable Member
Memorable Member

In the csv, something has been entered that is not a number. Check your csv files.

 

 

Otherwise, it's possible that it could be an ID column that was just numbers, so power bi assigned this column as a number, but now has a text value (e.g. 51215, 02150, C6767).

If so, go to the query editor and check the column formats (it will say 123) next to the column. On the applied steps, locate "Changed Type", click on this, thenm change the format of the column to text, and click replace current step when asked. 



Did I answer your question? Mark my post as a solution! Proud to be a Super User!


Connect with me!
Stay up to date on  
Read my blogs on  



@SteveCampbell

 

Steve thanks for the advice.  Based on the size of the .csv file the recirds is not easily identified.  In my ,pbix file I changed all the columns to text, and it still failed to load.

 

In another example, I loaded the file to a NEW .pbix - complared the applied steps - and still had the same issues.

 

Any thoughts?

Did you change the columns in the editor? Make sure you go back and replace the steps - otherwise it will try to convert to number, then back so will still errror out.

 

In the applied steps in the Query Editor, make sure you go back to all the changed type steps and click on them. Then, you can do your tranaformation on this step. Make sure you click replace current.

pp.png

 

 

Power BI usually looks at a subset of rows to determine the format. It is likely you have a column with many numbers and a small amount of text somwhere.



Did I answer your question? Mark my post as a solution! Proud to be a Super User!


Connect with me!
Stay up to date on  
Read my blogs on  



hi,

 

I am uploading the data from a csv file but power bi gives the error as shown below for some values:

 

melisseker_0-1666856147280.png

 

Could you please help me fix the issue?

 

Thanks!

So in reviewing the .csv files, I couldn't detect anything amiss.  With that said, I noticed that there are some broken records in my source files that were being cleaned up first in Excel, then PowerBI.

 

You are correct that a field idetnified as a number had some alpha characters in it.  In Query Editor I modified to a text type.  Because the two .csv files we modeled, it continued to break because you can't have a dumplicated in "one-to-many" relationship.  The alpha characters were duplicates so by inserting a step to remove the dupes, my files processed properly.

 

All of this said, how would you recommend identifying the "problem rows" when essentially they are cleaned up by viewing in Excel?

The best way, would be to have data validation in the excel files.

 

Otherwise, if you're after removing text values, you could add a custom column in query editor with the following code:

 

 

 = Number.FromText([Column1])

and change column1 to the correct column. Then, on the new column, you can select home > Remove Rows> Remove Errors. Then you can delete the new column.

 

 

Here's it in M code if you're comfortable with it (fill in red with your values):

 

 

#"Added Custom" = Table.AddColumn(#"MY LAST STEP", "ERRtest", each Number.FromText([Column1])),
#"Removed Errors" = Table.RemoveRowsWithErrors(#"Added Custom", {"ERRtest"}),
#"Removed Columns" = Table.RemoveColumns(#"Removed Errors",{"ERRtest"}),

 

 



Did I answer your question? Mark my post as a solution! Proud to be a Super User!


Connect with me!
Stay up to date on  
Read my blogs on  



Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.