Forum Discussion
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.
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.
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.
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.
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"}),
hi,
I am uploading the data from a csv file but power bi gives the error as shown below for some values:
Could you please help me fix the issue?
Thanks!
