DataFormat.Error: Invalid cell value '#N/A'.

[Kopek]

I connceted whole folder to the PBI, and I am receiveing the above error.

Each month new excel file will be added to the folder.

The #N/A value is in different columns - not always in the same, that's why it's hard to find it. 

(the #N/A value is outcome of VLOOKUP)

Each excel  has more than 2000 Rows, so again it's hard to find vthe error value.

I tried to use replace values, and replace errors for the whole query, and it does not work.

Do you have any ideas how I can replace #N/A with TBC for example, so the error is not showing ?

I would prefer to do it in the powerBI query, than to do it in each excel on motnhly basis.

Thanks for your help in advance!

[leandroab1]

Transform > Replace Errors. It will clean the #N/A.

[thaddeusferber]

If you are hitting the same issue that I was getting stuck on, once I expanded the excel table column in Power Query, it was too late, neither replacing errors nor deleting them seemed to work.

What I ended up having to do is to make the change in Power Query in a step prior to expanding the excel tables. So for me what worked was getting to the point where Power Query was showing a list of my excel tables in a [Name] column with the data from each table in a column named [Data], and then I created a new column based on that one as follows:

Table.AddColumn(#"Previous Step" , "Adjusted Data", each if [Name] = "Name of Your Table Here" then Table.ReplaceErrorValues([Data], {{"Name of the Column in Your Excel Table", null}}) else [Data])

 

Then I could expand that new [Adjusted Data] column without any errors (so any excel cells with #N/A or any other errors would have be replaced in the code above with null (or whatever else you want them changed to).

[v-piga-msft]

Hi @Kopek ,

You could right click the column which including errors and select the option Replace Errors.

Best  Regards,

Cherry

[Kopek]

Hi Cherry,

I tried that option for entire table, and in query it looked fine, but when i clik save & apply button, i had Data Format Error.

Maybe because there is too many rows (few thousand) ?

[v-piga-msft · Accepted solution]

Hi @Kopek ,

Have you solved your problem?

If you have solved, please always accept the replies making sense as solution to your question so that people who may have the same question can get the solution directly.

If you still need help, please feel free to ask.

Best  Regards,

Cherry

[v-piga-msft]

Hi @Kopek ,

Please check if you have installed the latest version of Power BI Desktop 2.66.5376.2161.

In additiom, if it is convenient, could you please share the data sample which could reproduce your issue so that I could have a test in my site.

Best Regards,

Cherry