Excel Forum

Filter sheet name from Excel worksheet dynamically via parameter

Dana Sarbulescu

Member

Members

Power Query

Forum Posts: 17

Member Since:
October 7, 2022

Offline

January 9, 2023 - 12:31 pm

Hi,

I am using data from a worksheet to which I add a new tab every month. I am naming each new sheet with the name of the month in the format MMMYY, e.g. Dec22 for December 2022. I need to use data every month from this workbook, but from the sheet that corresponds to the current month. So i set up a parameter that every month returns the name of the month and then when I am pulling into the query the workbook with all the months, I'd like to automatically filter the sheet of the workbook that corresponds with the current month. This always returns an empty table. I assume it has something to do with the syntax. Can anyone help? Can I not use a parameter to filter the name of the sheet? Do I need to use a function?

I am uploading screenshots of the query and the steps. When I manually filter the sheet to "Nov22" it works. But when I use the parameter CurMoName (which is a single item list containing "Nov22") it does not return any results.

Thank you!

See Answer

Dana Sarbulescu

Member

Members

Power Query

Forum Posts: 17

Member Since:
October 7, 2022

Offline

January 9, 2023 - 12:34 pm

The attachment might not have saved in my post. Here it is.

Catalin Bombea

Iasi, Romania

Member

Dashboards

Power Query

Power Pivot

Xtreme Pivot Tables

Excel for Decision Making

Excel for Finance

Excel Analysis Toolpak

Power BI

Excel

Word

Outlook

Excel Expert

Excel Customer Service

PowerPoint

Forum Posts: 1944

Member Since:
November 8, 2013

Offline

January 9, 2023 - 3:21 pm

Hi Dana,
"I am uploading screenshots of the query and the steps. When I manually filter the sheet to "Nov22" it works. But when I use the parameter CurMoName (which is a single item list containing "Nov22") it does not return any results."

Data type is essential, a list is not the same thing as a text string, you need to provide a text instead of a list. CurMoName{0} for example returns the first item in the list.
Instead of a parameter, you can use the system date:
DateTime.ToText(DateTime.LocalNow(),"MMMyy")