Forum Discussion
[DataFormat.Error] We couldn't convert to Number.
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.
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
5 Replies
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
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
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
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
