Results 1 to 8 of 8

Thread: Variable Sheet Name

  1. #1
    Acolyte Nick Burns's Avatar
    Join Date
    May 2017
    Posts
    75
    Articles
    0
    Excel Version
    Office 365

    Variable Sheet Name



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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?
    Oh... by the way, YOU'RE WELCOME!

  2. #2
    Super Moderator Bob Phillips's Avatar
    Join Date
    Mar 2011
    Posts
    1,668
    Articles
    0
    Excel Version
    O365
    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.

  3. #3
    Conjurer Bill Szysz's Avatar
    Join Date
    Apr 2016
    Location
    Poznań, Poland Dublin, Ireland
    Posts
    102
    Articles
    0
    Excel Version
    Excel 365
    Hi Nick,
    The question is too general.
    Without an example file containing the "Timesheet" and other worksheets, it's difficult to suggest something.

  4. #4
    Acolyte Nick Burns's Avatar
    Join Date
    May 2017
    Posts
    75
    Articles
    0
    Excel Version
    Office 365
    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?
    Oh... by the way, YOU'RE WELCOME!

  5. #5
    Acolyte Nick Burns's Avatar
    Join Date
    May 2017
    Posts
    75
    Articles
    0
    Excel Version
    Office 365
    Quote Originally Posted by Bill Szysz View Post
    Hi Nick,
    The question is too general.
    Without an example file containing the "Timesheet" and other worksheets, it's difficult to suggest something.
    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).
    Oh... by the way, YOU'RE WELCOME!

  6. #6
    Acolyte cyborgski's Avatar
    Join Date
    May 2019
    Posts
    60
    Articles
    0
    Excel Version
    Office 365
    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
    Attached Files Attached Files

  7. #7
    Acolyte Nick Burns's Avatar
    Join Date
    May 2017
    Posts
    75
    Articles
    0
    Excel Version
    Office 365
    Quote Originally Posted by cyborgski View Post
    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
    Mike that's perfect!
    Simple elegant solution.
    Oh... by the way, YOU'RE WELCOME!

  8. #8
    Acolyte cyborgski's Avatar
    Join Date
    May 2019
    Posts
    60
    Articles
    0
    Excel Version
    Office 365
    Glad it worked Nick!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •