Make Excel Workbook Sheet Import Dynamic

[lpriceFTW]

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?

[mlsx4 · Accepted solution]

HI @lpriceFTW 

Why don't you filter, in the origin, the column by the values that starts with...? 

[lpriceFTW]

Thank you! Simple and straightforward.