Power-Query: convert cell formula to text whiel there is a formula error

[DanHuber]

I have an "incorrect" sheet where text cells contain values like 

=+@GF+@ ABC

=+@GF+@ HELLO

=+@GF+@ XYZ

Those are interpreted falsely as fromula and create #NAME? errors

Correct content would be 

'+GF+ ABC

'+GF+ HELLO

'+GF+ XYZ

In excel, I can manually correct this by search and replace in the column within the formulas

search for =+@GF+@, replace by '+GF+

How do I do this in Power-Query?

There is no search/replace in formulas, but only replace values of cells.

Thanks for any pointer.

Dan

[#2]

@DanHuber 

That's like

and on next step replace =+ on +

[#3]

@DanHuber Difficult to visualise without seeing the file, but you can right-click on a column header in PQ and select 'replace values'. Enter the @ sign and replace it with 'nothing' (thus leave it blank). That should resolve it. 

[#4]

@hanks for the ideas. Unfortunately, it did not work. Most likely because I do not get the "bad" cells into PQ anyway. I noticed, while testing, that the import is not importing that cells at all. instead, I end up with cells that contain #Name? instead. 

So, I have a file that oviously was created by a database export or such. As son as i do a save (i.e. to csv) or a PQ import, the cells are evaluated the first time, I guess.. 

What i do is the following:

1 - opening the file in excel, selecting the cell

2 - see what's in that cell

3 - do a search and replace

4 - result is that the error in the cell is gone...

NOW I can import it in PowerQuery...

Weird, isn't it?

If I import the file in PQ without that search/replace before in plain excel, I only get the cell content of #Name? in that very cell..

Any ideas?

Dan

 

[#5]

@DanHuber 

Dan, what do you import by Power Query? Initial csv file or one opened in Excel and saved after that? Results could be different.

[#6]

@Sergei Baklan, I got the .xlsx file from a vendor who generated it from their system. I do not know the details of that system, though.

Then I open an empty .xls file, go to the Data tab and connect to the excel file. Enter the path to the file and import. On the screen where I can select the sheet within the xls file, I just noticed that at this moment, the error is already there. I did not see this before, because that issue is on line 4800 or so in the original file. I cut out the lines that have no error for this test.

See screenshot..

 

[#7]

@Sergei Baklan 

Here is the shortened file. Nothing special on the content, except that error (cells F2:F3) which I want to correct while importing to PQ. 
Fix could be done if I am opening the file and do a search/replace as mentioned above. After that, the file works fine.
But I want PQ doing the search/replace... and I never get that far..
I guess it's really the loading procedure which tries to prevent errors being loaded at the first place.

[#8]

Hi @DanHuber 

I guess it's really the loading procedure which tries to prevent errors being loaded at the first place

Nope, the Errors are stored in the XML:

I won't say it's missing impossible but not far I'm afraid

[#9]

@DanHuber 

On reflection... The Errors are in the XML after you opened the file in Excel and save a few rows

Could you share (hope it's not giant) the file as it comes from your other system w/o touching it at all?