Feedback by UserVoice

How can we improve Excel for Windows (Desktop Application)?

Power Query data from "This Excel Workbook" - Do not break Query if this file name or path changes!

If you want to have a self-contained file where your data is entered not in table format, but you want to get it INTO a table via PowerQuery, you can currently PowerQuery on the same file via ""From File" and turn it into a table and bring it into that same workbook. But the problem is that now you:
1) Have to save every time before you refresh the query.
2) Cannot change the name of the file or where it is saved without breaking the query.
I believe there's a workaround for #2 involving setting up a parameters table and doing something in the advanced editor, but that's over my head for now. Plus this should be a simple thing for a basic PQ user to do. There should be another source option for getting data from with PowerQuery and it should be "This Workbook". I know you can do that if your data is already formatted as a table, but what about for data that is not formatted as a table? I'd like to be able to PQ that to get it into a table format and then I also want to be able to change the file name and location sometimes without breaking the query. PQ would update the query with the new file name and path automatically. And I would think this would also let you refresh the query without having to save the file first.

38 votes
Sign in
(thinking…)
Sign in with: facebook google
Signed in as (Sign out)

We’ll send you updates on this idea

Tim Graham shared this idea  ·   ·  Flag idea as inappropriate…  ·  Admin →

11 comments

Sign in
(thinking…)
Sign in with: facebook google
Signed in as (Sign out)
Submitting...
  • Anonymous commented  ·   ·  Flag as inappropriate

    Here is my working solution. Create an Excel Table. Mine is named 'tblData'. From the Data Ribbon, 'Get & Transform' section, select New Query / From Other Sources / Blank Query. When the editor opens, paste this into your query editor (without the quotes): = "Excel.CurrentWorkbook(){[Name="tblData"]}[Content]". That's it.

  • Gabriel Sandu commented  ·   ·  Flag as inappropriate

    Relative paths needed for source files targeted in powerquery source items. If you have an Excel file using a powerquery of a local file as a source you have to edit the source every time you move the Excel file in a different folder.

  • Simon Nuss commented  ·   ·  Flag as inappropriate

    I've been using a simple workaround a few years now. Simply reference a named cell (using Excel.CurrentWorkbook()) containing the following formula:

    =LEFT(CELL("filename",A1),FIND("[",CELL("filename",A1))-1)

    Good luck!

  • Anonymous commented  ·   ·  Flag as inappropriate

    Hi All, These are all very interesting comments. My problem is that I have a set directory folder in which I drop a new file on a regular basis. This directory only ever contains the latest file. My issue is the file is always named differently. Does anyone know a way of setting the PQ Source to equal a fixed location e.g. C:\Temp\My Folder and "=Excel.CurrentWorkbook()" or "*.xls" I don't want to have to join the file as a new table each time nor do I want to rename the file. Any help much appreciated.

  • Imke Feldmann commented  ·   ·  Flag as inappropriate

    @Tim Graham,
    I can understand your objections and figured out in the meantime that we might not be on the same page here, so opened up a separate post on it here: https://excel.uservoice.com/forums/304921-excel-for-windows-desktop-application/suggestions/15673497-harmonize-excel-currentworkbook-with-excel-workboo

    My use case is to lay a standardized "frame" around different incoming data to enable a secure import of data. So you have all sorts of different incoming data (time series from different sources) which share a common set of very basic metadata (ID and date). So the user copy/pastes some standardized columns to the left and rows on top where he places the codes (mark which row or column contains ID and dates in which format) but he shouldn't touch the data at all.

    So just a different way trying to avoid errors to user interactions basically.

  • Tim Graham commented  ·   ·  Flag as inappropriate

    I've been using the Named Range and =Excel.CurrentWorkbook() solution and that's working well enough. And this may be better (for me) than a Current Workbook + This Worksheet option since it helps to define a specific area to be queried and probably helps avoid potential issues arising due to adding things to the worksheet that are not really part of your data (comments, notes about what's included in the data, etc.) and that might mess up your query (if you didn't set up the query to guard against that or didn't realize you'd mess up the query by addin extra rows at the top, et cetera). But I could imagine there would be plenty of cases where someone doesn't need to, or want to, set up a named range to do this, and may not have concerns about "extra data" getting added to the worksheet, and to just query the current worksheet would be faster -- as may be Imke Feldmann's case.

    Anyway, I guess my suggestion now is more about making all this easier to figure out. I did a lot of Googling and bought "M is for Data Monkey" in order to figure it out. Just feels like it should be easier to discover than that. In my job, I always struggle with whether to ask users to enter numbers into a proper Excel table (which a lot of them don't like and can mess up easily), or entering it into a simple range, where you can direct their attention better. So for me, when I started trying Power Query, one of my first realizations was how great it is to now be able to have users enter to a range, and then be able to turn it into a TABLE and allow me to get the data into pivot tables -- best of both worlds. But then I almost lost my mind trying to figure out how to properly (and safely) query the current workbook when that workbook could end up having many different file paths / names over its life. This solution just seemed way too "hidden".

  • Imke Feldmann commented  ·   ·  Flag as inappropriate

    @GuyHunkin: Excel.CurrentWorkbook() doesn't solve the problem, as it doesn't return the content from the current file that comes as Kind "Sheet" from Excel.Workbook.

    Which is the data that sits in sheets as a simple range and is neither a named range/ defined name or a table. This is a big problem, as the workaround via File("Sheetname") is totally buggy (see here: https://social.technet.microsoft.com/Forums/en-US/1269e769-fc79-46fe-af7e-b0a67393c56a/excelcurrentworkbook-not-returning-consistently?forum=powerquery ).

    So if the Excel.CurrentWorkbook() would return the content of a sheet which if formatted as a simple range this would solve one of the problems!

  • Guy Hunkin commented  ·   ·  Flag as inappropriate

    Regarding this one:
    "...Plus this should be a simple thing for a basic PQ user to do. There should be another source option for getting data from with PowerQuery and it should be "This Workbook"..."

    You can open a blank query and write "=Excel.CurrentWorkbook()" command in the formula bar. This will show you all the named ranges (and tables) in your workbook. You can then choose and import the data that you want.

  • Imke Feldmann commented  ·   ·  Flag as inappropriate

    Strongly support this!

    Need it for client applications and the need to save before the refresh actually returns the correct data doesn't sell very well :-(

    People who are not that familiar with the engine will get confused or loose trust in the correctness of the results

Feedback and Knowledge Base