Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Don't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.

Reply
_Antoine_
Frequent Visitor

Get the name of the excel spreadsheet hosting a given table

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

1 ACCEPTED SOLUTION
PwerQueryKees
Super User
Super User

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...

View solution in original post

3 REPLIES 3
PwerQueryKees
Super User
Super User

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...

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

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

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.

Top Kudoed Authors