Microsoft 365 and Office | Excel | For home | Windows
A family of Microsoft spreadsheet software with tools for analyzing, charting, and communicating data.
Thanks Franz
I'll give it a try.
Using a file name parameter in SOURCE statement for Power Query (XL2013)
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(128, 8%, 22%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EA%3C/text%3E%3C/svg%3E)
Anonymous
I've just installed the latest Power Query (PQ) update which allows you to add parameters to your queries, and I'm trying to set up a query where I can pass the name of an Excel file (the query source) to the query's SOURCE = Excel.Workbook(File.Contents("file path and name"), null, true) statement.
I've set up my parameter - called FilePathAndName, set to "C:\users\myname\Data.XLSX" - and set up the SOURCE statement as
Source = Excel.Workbook(File.Contents(FilePathAndName), null, true) via the advanced editor, but all I get is a summary of the sheet names from the source file.
The data file has 2 sheets, with the one to be imported called DATASET1.
It seems I'm almost there, but must be missing something.
Hopefully someone has figured this out already, given this added functionality to PQ I think is pretty new.
Thanks in advance !
Microsoft 365 and Office | Excel | For home | Windows
Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.
2 answers
Sort by: Most helpful
-
Anonymous
2016-10-31T02:37:08+00:00 -
Anonymous
2016-10-31T01:32:43+00:00 I've just installed the latest Power Query (PQ) update which allows you to add parameters to your queries, and I'm trying to set up a query where I can pass the name of an Excel file (the query source) to the query's SOURCE = Excel.Workbook(File.Contents("file path and name"), null, true) statement.
I've set up my parameter - called FilePathAndName, set to "C:\users\myname\Data.XLSX" - and set up the SOURCE statement as
Source = Excel.Workbook(File.Contents(FilePathAndName), null, true) via the advanced editor, but all I get is a summary of the sheet names from the source file.
The data file has 2 sheets, with the one to be imported called DATASET1.
It seems I'm almost there, but must be missing something.
Hopefully someone has figured this out already, given this added functionality to PQ I think is pretty new.
Thanks in advance !
Once you have the list of all sheets, you have to filter in order that only the relevant sheet remains. Then you can expand the table in order that you can work on your data.
Hope it helps.
Regards
Franz