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

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

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
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

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

Top Kudoed Authors