Fix DataFormat.Error: Invalid cell value '#NAME?'

[klikk680]

Hi,

I imported a table into a Microsoft Excel sheet, when I apply the changes to the query, I get this error [DataFormat.Error] Invalid cell value "#NAME?" ".
The table contains a "Notes" text field, it may occasionally start with a  "=-" sign and this can determine the error.
Can I fix the error directly in PowerBi without modifying the excel file before importing?

Thank you in advance.

[Anonymous]

You have some "blank/empty" cells inbetween formated data thats why you get this error.

Try to replacing empty cells with some data/ or remove them. It should resovle your issue.

[Anonymous]

Hello,

Since this is an error related to DataFormat, I believe changing the data format should resolve this issue. You can try choosing any type and it should automatically created a replace error step for you and sync up your file.

[PhilipTreacy]

hi @klikk680 

I don't understand how your Notes column relates to these errors.  A screenshot or sample data would help.

But if you are importing Excel errors into PQ you can create a Custom Column and check for the error like so

let x = try if [Errors] <> null then 1 else 0 in if x[HasError] then x[Error] else [Errors])

where the data looks like this

This allows you to ccheck the error record if you wish.

If you just want to replace the errors with null you can use this line

let x = try if [Errors] <> null then 1 else 0 in if x[HasError] then null else [Errors])

Here's my full query loading a table from Excel

let
    Source = Excel.CurrentWorkbook(){[Name="Table"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Errors", type any}}),
    #"Added Custom2" = Table.AddColumn(#"Changed Type", "Custom.2", each let x = try if [Errors] <> null then 1 else 0 in if x[HasError] then x[Error] else [Errors]),
    #"Added Custom3" = Table.AddColumn(#"Added Custom2", "Custom.3", each let x = try if [Errors] <> null then 1 else 0 in if x[HasError] then null else [Errors])
in
    #"Added Custom3"

Regards

Phil

---

If I answered your question please mark my post as the solution.
If my answer helped solve your problem, give it a kudos by clicking on the Thumbs Up.

[Jimmy801]

Hello @klikk680 

this error is raised in Excel and Power Query is reading it's data as error, not the formula itself. So you can use Table.ReplaceErrorValues to replace them or add a new column where you reproduce the formula/content.

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

[klikk680]

I did just like this:

= Table.ReplaceErrorValues(My_Table, List.Transform(Table.ColumnNames(My_Table), each {_, 0}))

That doesn't solve my primary problem because now the "Notes" have a "0", losing what is written after.
The other solution with ReplaceValue does not work: just as you said, this error is raised in Excel

Fortunately there are rare cases of errors and this is a good compromise, I think.

Thank you

[PhilipTreacy]

Hi @klikk680 

In PBI remove the =- with ReplaceValue ?

= Table.ReplaceValue(Source,"=-","",Replacer.ReplaceText,{"Notes"})

Phil

---

If I answered your question please mark my post as the solution.
If my answer helped solve your problem, give it a kudos by clicking on the Thumbs Up.

[MattAllington]

I have not had a lot of success fixing these types of errors in PQ. You can try removing errors using find and replace and replace with null