Forum Discussion

lpriceFTW's avatar
lpriceFTW
Helper II
3 years ago
Solved

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?

  • HI lpriceFTW 

     

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

     

2 Replies

  • mlsx4's avatar
    mlsx4
    Memorable Member

    HI lpriceFTW 

     

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