Forum Discussion
Importing excel data that contains #N?A values
Hi
When I am importing data or a table from excel into powerquery that contains a #N/A value in cell I get a an 'Error'. When I click on the error or link, which is diplayed in the field, the description states DataFormat.Error: Invalid cell value '#N/A'. I know why I am getting the #N/A; this is linked to a 'vlookup' on my data source but unfortunelaty I am unbale to change the formula in Excel.
Any Suggetions. The Error message happens to any cell(s) where any vlookup(s) fail.
Chris
Finance & Information Analysts
Hi Anonymous
there are 2 options "remove errors" and "replace error" in Power Query Editor
Right-click to columns that contains errors and choose appropriate option
do not hesitate to give a kudo to useful posts and mark solutions as solution
3 Replies
Hi Anonymous
there are 2 options "remove errors" and "replace error" in Power Query Editor
Right-click to columns that contains errors and choose appropriate option
do not hesitate to give a kudo to useful posts and mark solutions as solution
'Remove Errors' worked for me! Here's my case:
- Data source was an Excel file from a SharePoint folder- Date column was sorted in descending order and resulted to, 'DataFormat.Error: Invalid cell value '#N/A''
- Traced back a few steps and found that another column, a string column, had errors:- Right-clicked on the string column > 'Remove Errors' (Table.RemoveRowsWithErrors())
