Forum Discussion

DanHuber's avatar
DanHuber
Iron Contributor
Dec 04, 2023

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...
excel
SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Dec 06, 2023

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.

DanHuber's avatar
DanHuber
Iron Contributor
Dec 06, 2023

SergeiBaklan 

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.

  • Lorenzo's avatar
    Lorenzo
    Silver Contributor
    Dec 06, 2023

    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?

  • Lorenzo's avatar
    Lorenzo
    Silver Contributor
    Dec 06, 2023

    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

Resources