Importing excel data that contains #N?A values

[Anonymous]

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

[az38 · Accepted solution]

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

[kymramosrpo]

'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())

[Anonymous]

Hi

So simple and it works great, thanks.

Chris