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

[conniedevina]

Hi All,

I encounter this issue, while actually I uploaded csv file for 2 months it was no issue, but when I upload all my csv file to combine in Power BI I have this issue.

Even I already add step "Replaced Errors" I still encounter this issue. Is there any way to check which data has issue?
I have 1.222 csv files so it's not possible to check it one by one and total rows I have is around 10 million rows.

[PhilipTreacy]

Hi @conniedevina 

Just select 1 column at a time in your table and then do the Keep Rows -> Keep Errors.  If you don't get any errors then deleet that step and do it for the next column, until you get some errors appear.

It will help if you change the data being usded by PQ to preview your data.By default it just uses the first 1000 rows.  You can see this in the bar at the bottom of the PQ editor.

Click on that and then select Column profiling based on entire data set

It may also help for you to turn on Column Quality.  From the View area of the Ribbon, click the box Column Quality

Regards

Phil

[PhilipTreacy · Accepted solution]

Hi @conniedevina 

I set up some dummy CSV files to demonstraate how to handle this.  With my data imported to Power BI I get this table in Power Query

The error you are getting means your query is trying to convert something to a number and failing.  To replicate this I'll try to convert Column1 to Whole Number, and I get the same error as you

I can easily see where the errors are, and the Source.name column tells me the file with the problem.  But if you have millions of rows, you aren't seeing all of these in your query preview.

So, select the column with the errors, in my case it's Column1, then from the Home section of the Ribbon, click on Keep Rows, then Keep Errors

You end up with a table like this

The Source.name column tells you the file(s) with issues.  Your other columns (Column2 for me) can indicate where the issue is.

You can also extract the information from the Error if you like.  To do this create a new Custom Column with this code - change the column to match your column containing errors.  My error column is [Column1]

This gives a new column containing the error record

Expand the new column by clicking the double headed arrow in the column header, and only select Error

Which gives you this

Now expand this Error column

Which gives you this, telling you the actual values causing the errors

Regards

Phil

[conniedevina]

Hi Phil,

Thank you so much for your reply, I tried with the "Keep Errors" and here is showing.

Even actually I don't have Column 39 in my table, so I'm not sure why this is showing.

Below is my Power Query Editor

let
Source = SharePoint.Files("https://sharepoint.com", [ApiVersion = 15]),
#"Filtered Rows" = Table.SelectRows(Source, each Text.Contains([Folder Path], "https://sharepoint.com/folder")),
#"Filtered Hidden Files1" = Table.SelectRows(#"Filtered Rows", each [Attributes]?[Hidden]? <> true),
#"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File", each #"Transform File"([Content])),
#"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}),
#"Filtered Rows3" = Table.SelectRows(#"Renamed Columns1", each [Date created] >= RangeStart and [Date created] <= RangeEnd),
#"Removed Other Columns1" = Table.SelectColumns(#"Filtered Rows3" , {"Source.Name", "Transform File","Date created"}),
#"Expanded Transform File" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File", {"Day", "Category", "Class", "ItemCode", "ItemName", "Store", "Qty", "Sales", "Month Year"}, {"Day", "Category", "Class", "ItemCode", "ItemName", "Store", "Qty", "Sales", "Month Year"}),
#"Split Column by Character Transition" = Table.SplitColumn(#"Expanded Transform File", "Day", Splitter.SplitTextByCharacterTransition({"0".."9"}, (c) => not List.Contains({"0".."9"}, c)), {"Day", "Day.2"}),
#"Added Custom" = Table.AddColumn(#"Split Column by Character Transition", "Date", each [Day]&"-"&[Month Year]),
#"Changed Type with Locale" = Table.TransformColumnTypes(#"Added Custom", {{"Date", type date}}, "en-SG"),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type with Locale",{"Day", "Day.2", "Month Year", "Source.Name"}),
#"Added Custom1" = Table.AddColumn(#"Removed Columns", "DayofWeek", each Date.DayOfWeek([Date])),
#"Added Conditional Column1" = Table.AddColumn(#"Added Custom1", "Channel", each if Text.Contains([ItemName], "(TA)") then "Takeaway" else if Text.Contains([ItemName], "(TAD)") then "Delivery" else if Text.Contains([ItemName], "(TS)") then "Takeaway" else if Text.Contains([ItemName], "(TSB)") then "Takeaway" else if Text.Contains([ItemName], "(DA)") then "Delivery" else if Text.Contains([ItemName], "(DB)") then "Delivery" else "Dinein"),
#"Added Conditional Column2" = Table.AddColumn(#"Added Conditional Column1", "Division", each if Text.Contains([Store], "PLE") then "PLE" else if Text.Contains([Store], "PLR") then "PLR" else if Text.Contains([Store], "PK") then "PK" else if Text.Contains([Store], "DK") then "PLR" else null),
#"Added Custom2" = Table.AddColumn(#"Added Conditional Column2", "Store CD", each Text.Start([Store],5)),
#"Filtered Rows1" = Table.SelectRows(#"Added Custom2", each not Text.Contains([ItemName], "(STAFF)",Comparer.OrdinalIgnoreCase)),
#"Added Custom3" = Table.AddColumn(#"Filtered Rows1", "System", each "ABC"),
#"Added Conditional Column3" = Table.AddColumn(#"Added Custom3", "Division Custom", each if Text.Contains([Store], "PLE(3)") then "PLE-ASQ" else if Text.Contains([Store], "DK") then "CK" else [Division]),
#"Changed Type" = Table.TransformColumnTypes(#"Added Conditional Column3",{{"Category", type text}, {"Class", type text}}),
#"Added Custom4" = Table.AddColumn(#"Changed Type", "CategoryID", each [System]&[Division Custom]&[Category]&[Class]),
#"Filtered Rows2" = Table.SelectRows(#"Added Custom4", each ([CategoryID] <> "F9" and [CategoryID] <> "FR497")),
#"Added Custom5" = Table.AddColumn(#"Filtered Rows2", "Custom", each Text.RemoveRange(Text.From([Store]),0,6)),
#"Removed Columns1" = Table.RemoveColumns(#"Added Custom5",{"Store"}),
#"Renamed Columns" = Table.RenameColumns(#"Removed Columns1",{{"Custom", "Store"}}),
#"Kept Errors" = Table.SelectRowsWithErrors(#"Renamed Columns", {"Category", "Class", "ItemCode", "ItemName", "Qty", "Sales", "Date created", "Date", "DayofWeek", "Channel", "Division", "Store CD", "System", "Division Custom", "CategoryID", "Store"})
in
#"Kept Errors"

It was fine until "#Renamed Columns" , when I added #"Kept Errors" it's showing that error in my first screenshot.

Do you have any idea why it like this?

Thanks,

Regards,

Connie

[Anonymous]

Hi @conniedevina,

Can you please share some dummy data that keep the raw data structure with expected results? It should help us clarify your scenario and test to coding formula.

How to Get Your Question Answered Quickly  

Notice: please remove sensitive data before sharing.

Regards,

Xiaoxin Sheng

[conniedevina]

Hi @Anonymous ,

Thank you for your reply, I think it's kind of impossible for me to mask and give the sample data because I have thousands files as data source and I'm not sure which files that giving me that issues.. Because I tried with 3 files it's working, the problem appear when I tried to refresh in Power BI Service with all files.

Thanks,

Regards,

Connie