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

[andreazambon]

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

[andreazambon · Accepted solution]

Hi there, 

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

[Ritaf1983]

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

[andreazambon]

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