Forum Discussion
Make Excel Workbook Sheet Import Dynamic
I have an M query which combines a specific sheet from multiple workbooks into one table.
The source is a folder which contains 12 Excel workbooks (one per month).
When setting up the query as a "from Folder" query, PBI automatically creates supporting queries and objects, including a sample query which has a "Navigation" step:
PBI sets up the "Navigation" query like so:
Where the "Item=" is a hardcoded value representing a sheet name that is contained in the sample workbook. (See image below for the table containing the items available.)
My problem is that the hardcoded value ("YTD Jan 2022") is a sheet name that does not apply to all 12 workbooks, as each one has a different sheet name reflecting whatever month that workbook is (Feb, Mar, Apr, etc.) See the "Source" workbook step in the following image:
I would like to replace the "Item="YTD Jan 2022"" with a dynamic value which selects a sheet if it contains the string "YTD".
How would I do this?