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

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

Accepted Solutions

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

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.

5 REPLIES 5

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

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. 

Highlighted
irnm8dn Member
Member

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

@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?

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

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.

irnm8dn Member
Member

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

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?

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

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"}),