Forum Discussion

Kopek's avatar
Kopek
Helper IV
7 years ago
Solved

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, ...
Need Help
  • v-piga-msft's avatar
    v-piga-msft
    7 years ago

    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

thaddeusferber's avatar
thaddeusferber
Frequent Visitor
4 years ago

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).