Forum Discussion

andreazambon's avatar
andreazambon
Icon for Helper V rankHelper V
3 years ago
Solved

PowerBI and Excel table with #N/A errors due to VLookUp

Hi there. 

 

I have an Excel Datasheet stored in Sharepoint, where I have some VLookUps formulas and lots of "#N/A" Errors. 

Of course if I try to import data from this sheet, PBI returns "Error" on his cells.

 

I tried to put some IfErrors (IfError(Vlookup;"")) on the Excel datasheet, trying to solve this starting from the roots. Then I reloaded the Data in PBI but the errors still remains. 

I tried also to delete the SourceData and reloaded it again, but with no results. 

 

A way to solve this could be to change errors with PowerQuery... but why it doesn't works by adding IfError on the Excel formulas? 😕

 

Thanks

Need Help
  • andreazambon's avatar
    andreazambon
    3 years ago

    Hi there, 

    I managed to handle my problem by replacing the errors with null whit powerquery. Seems that it works fine 🙂

3 Replies

  • Ritaf1983's avatar
    Ritaf1983
    Icon for Super User rankSuper User
    3 years ago

    Hi andreazambon 
    What is the data type of the imported column?
    Can you share a link to some dummy excel like yours?

    • andreazambon's avatar
      andreazambon
      Icon for Helper V rankHelper V
      3 years ago

      Type General in Excel, Type ABC123 in PBI. 

      You can try with 2 tables, one for dataset and one with VLookUPs as PBI datasource, its the same

  • andreazambon's avatar
    andreazambon
    Icon for Helper V rankHelper V
    3 years ago

    Hi there, 

    I managed to handle my problem by replacing the errors with null whit powerquery. Seems that it works fine 🙂