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 wo...
@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.
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.SergeiBaklan, 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..
