Forum Discussion
Power-Query: convert cell formula to text whiel there is a formula error
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
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.
@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
Dan, what do you import by Power Query? Initial csv file or one opened in Excel and saved after that? Results could be different.
