Get the name of the excel spreadsheet hosting a given table

[_Antoine_]

Hello,

Would anyone know how to retrieve in power query language M the name of the spreadsheet hosting a given table? For exemple, let us assume i have a table named "Sales_Per_Region" on "tab1" spreadsheet, how can I ask power query to return "tab1" as the OUTPUT with  "Sales_Per_Region" as an INPUT?

Thank you

Antoine

[PwerQueryKees · Accepted solution]

Power Query by itself can't do it unfortunately. 

But there are some work-around you may find useable:

• enter the formula =CELL(" filename") and define a name referring to it.
  • I found it somewhat unstable after moving the file, but that may not a problem for you
• use VBA to create a query returning the filename directly in the workbook.queries collection
  • You can put it in your personal.xlsb, but then it is personal.
  • Or you have to turn your workbook into a XLSM and your users get all kinds of scary security warnings
• You can write a PowerScript to insert a query into your workbook
  • You need a separate script file to go with your workbooks
  • Powerscript is not easy to make bullet proof and it is scarily powerfull

I found chat-gpt very helpful in writing the functions specific to my situation.

I ended up not using any of these methods because of all the downsides, so I can't share my solution with you...

[_Antoine_]

Thank you very much for your detailed answer and multiple suggestions of solution!

[PwerQueryKees]

You're welcome. What solution did you go for?