cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Kopek
Helper III
Helper III

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

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!

 

 

1 ACCEPTED SOLUTION
v-piga-msft
Resident Rockstar
Resident Rockstar

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

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

5 REPLIES 5
thaddeusferber
Frequent Visitor

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
Resident Rockstar
Resident Rockstar

Hi @Kopek ,

 

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

 

replace error.png

 

Best  Regards,

Cherry

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi Cherry

 

 

 

 

v-piga-msft
Resident Rockstar
Resident Rockstar

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

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
v-piga-msft
Resident Rockstar
Resident Rockstar

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

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
Microsoft Build 768x460.png

Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!

May UG Leader Call Carousel 768x460.png

What difference can a User Group make for you?

At the monthly call, connect with other leaders and find out how community makes your experience even better.

Power BI Release May 2022 768x460.png

Check it out!

Click here to read more about the May 2022 updates!