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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors