Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
Kopek
Helper IV
Helper IV

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

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

6 REPLIES 6
leandroab1
Advocate II
Advocate II

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

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

 

 

 

 

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.

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
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.