Dec 04 2023 10:09 AM
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
Dec 04 2023 10:18 AM
Dec 04 2023 10:21 AM
@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.
Dec 05 2023 12:09 PM
@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
Dec 06 2023 03:04 AM
Dan, what do you import by Power Query? Initial csv file or one opened in Excel and saved after that? Results could be different.
Dec 06 2023 07:20 AM
@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..
Dec 06 2023 07:27 AM
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.
Dec 06 2023 11:01 AM
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
Dec 06 2023 11:51 PM
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?