Converting to Power Query; how do I detect Excel formulas in my imported text file? RRS feed

  • Question

  • I am trying to convert to the new Power Query style of connecting to some text files that I use for analysis. These files are spreadsheets in another program and are only compatible in Excel via importing as text files. The columns that have formulas i.e. =C3-D3 are detected as text whereas the legacy import connections detected these formulas and calculated them. I had some issues with the older style and deleted some connections and when I went to restore them, I was not aware that Excel updated to Power Query in Jan. 2016. I can't seem to find an answer and have hacked away at this for almost two days. Any help would be appreciated; probably a simple fix but I haven't found it yet. Thanks in advance...Steve


    Friday, July 7, 2017 6:45 PM


All replies

  • Hi Steve. Importing Excel formulas embedded in text files isn't something Power Query currently supports. However, you should still be able to use the legacy connector (which is still available, just hidden by default). This article explains how to show the legacy data import buttons.


    Friday, July 7, 2017 7:27 PM
  • Thanks for the reply; I saw the legacy options and changed them but nothing happened nor did I know to look for the "legacy wizards" in the "get data" option. I found out once you use the Power Query on a sheet the legacy doesn't show up (greyed out); so I made a new sheet & the legacy was able to work, I have my data back the way I need it; thanks you saved my weekend.



    Friday, July 7, 2017 8:09 PM