Variable Sheet Name

I'm reading a folder of hundreds of timesheet Excel Files. Each file can have multiple sheets, not necessarily the specific timesheet tabs that I need. These timesheet tabs will have employee names as the tab name, though not necessarily.
I'm the author of these files, it would be ideal if PQ could read the codename of the sheets and filter off of that.

How can I only retrieve those sheets that are truly "timesheet style" sheets?

Doubt that Power Query can get at the codename.

Do you know the names of all the sheets you want, if so you could build a list of same and filter on that list.

Hi Nick,
The question is too general.
Without an example file containing the "Timesheet" and other worksheets, it's difficult to suggest something.

Unfortunately no I won't know the name of the sheets since they will be employee names in some case and in other cases they won't. In other words, some workbooks may contain several timesheet tabs, others only one.

As mentioned, I'm the one creating the timesheets for employees to use. In it, Column D is hidden. I could put "Timesheet" in D1. So, I'm thinking along these lines:
1) get a list of all tabs (filter type on "sheet")
2) get just the first row of each tab, along with the sheet name
3) filter where the fourth column ("D") has timesheet
4) Now I have a list of just the timesheet tabs

Not sure how to go about that.

Also, once I have the list, do I still use the regular Sample Query that's created when you use a folder of files for import?

Bill Szysz said:

Hi Nick,
The question is too general.
Without an example file containing the "Timesheet" and other worksheets, it's difficult to suggest something.

Click to expand...

There's a lot of "intelligence" (aka VBA, locked cells, data validations) behind my timesheet files and a hidden sheet of sensitive data. I'll put one together that's stripped down to just the timesheet tab and get it uploaded. (May take a day or two).

Hi Nick,
Based on your description, are you conceptually looking for something like the attached?

I rewrote the Sample transform to pull multiple tabs and then look for the 4th column to contain the word TimeSheet on the first row.

= Table.SelectRows(Source, each [Kind] = "Sheet" and (try [Data]{0}[Column4]="TimeSheet" otherwise false ) )

Regards,
Mike

cyborgski said:

Hi Nick,
Based on your description, are you conceptually looking for something like the attached?

I rewrote the Sample transform to pull multiple tabs and then look for the 4th column to contain the word TimeSheet on the first row.

= Table.SelectRows(Source, each [Kind] = "Sheet" and (try [Data]{0}[Column4]="TimeSheet" otherwise false ) )

Regards,
Mike

Click to expand...

Mike that's perfect!
Simple elegant solution.

Glad it worked Nick!