Page 1 of 2 1 2 LastLast
Results 1 to 10 of 11

Thread: Naming new worksheet during "load to…"

  1. #1
    Seeker jmlee's Avatar
    Join Date
    Apr 2020
    Location
    Bonn, Germany
    Posts
    6
    Articles
    0
    Excel Version
    O365

    Naming new worksheet during "load to…"



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

    I'm new to Power Query and new to the forum. I have scoured the whole entire internet :-) for an answer to the following question:

    When loading to a new worksheet, is it possible to name the worksheet from the query (for example with the table name)?

    My specific case concerns invoicing data for multiple business units at a customer. I have up to 5000 rows of raw data (for example of email accounts to be billed), that are processed with PQ and summarized in a pivot table. The final counts and sums are invoiced as allocated to each of up to 8 business units. Those units want to review the original data, so that they can verify against the final sums. The business units have thus requested that the raw data for each be split into separate worksheets (so they don't have to get their hands dirty with filtering etc.).

    I thus have a set of PQ queries that create new worksheets per business unit based on my "data-all" table. PQ does the work in a snap, but then I have the tedious exercise of renaming eight worksheets. I want to avoid VBA, because I don't want to send .xlsm sheets to the customer, nor do I want to complicate the my template and monthly results files. (I need to do this for multiple services, so for a months' billing, it adds up to 6 renaming procedures with between 5 and 8 business units.)

    I most appreciate your help.

    Cheers,
    Jamie

  2. #2
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,379
    Articles
    45
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    Hi Jamie,

    Unfortunately, no. Power Query just creates worksheets using the default names of Sheetx, and there isn't a way to change that.

    How often do you do this kind of work? It is a feature that we could consider adding for Monkey Tools (rename worksheets to match query names).
    Ken Puls, FCPA, FCMA, MS MVP

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training), with my book M is for Data Monkey, or our new Power Query Recipe cards!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  3. #3
    Seeker jmlee's Avatar
    Join Date
    Apr 2020
    Location
    Bonn, Germany
    Posts
    6
    Articles
    0
    Excel Version
    O365
    Hi Ken,

    thanks for the reply. I suspected that it was so.

    This will be a monthly routine for my assistant. We're in the process of on-boarding the customer, so I am building up the billing model. Currently, we are the only ones in my company using PQ. This customer is, however, atypical (multiple invoice recipients and a complicated service structure and price list). Our colleagues have things easier--or harder, since they are still using vlookup ;-).

    I do think, however, that naming the worksheets out of PQ would be useful. After all, currently *every* new worksheet created by PQ must be manually renamed by the user. Frankly, I am surprised that it is lacking in Excel. After all, resulting tables inherit their queries' names. Should be easy to implement.

    Cheers,
    Jamie

    And, cheers to you on Vancouver Island. My mother lived for 20 years on San Juan Island. Lovely area.

  4. #4
    Administrator AliGW's Avatar
    Join Date
    Nov 2015
    Location
    Ipswich, Suffolk, England
    Posts
    1,435
    Articles
    0
    Excel Version
    Office 365 Subscription
    Have you added this to the Microsoft PowerQuery User Voice? I would vote it up if you did.

    https://excel.uservoice.com/forums/3...gory_id=143376
    Ali
    Enthusiastic self-taught user of MS Excel!

  5. #5
    Seeker jmlee's Avatar
    Join Date
    Apr 2020
    Location
    Bonn, Germany
    Posts
    6
    Articles
    0
    Excel Version
    O365
    I have just posted the idea to the User Voice as suggested by Ali. The idea is called "Power Query: Name new worksheet from "Load to…"

  6. #6
    Administrator AliGW's Avatar
    Join Date
    Nov 2015
    Location
    Ipswich, Suffolk, England
    Posts
    1,435
    Articles
    0
    Excel Version
    Office 365 Subscription
    I’ve voted for it.
    Ali
    Enthusiastic self-taught user of MS Excel!

  7. #7
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,379
    Articles
    45
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    In the mean time, I'm going to look at adding some functionality to Monkey Tools in order to do this. You'd still have to do the work the way you are currently, but at list we can get you the ability to rename the sheets with a couple of clicks, rather than individually.

    I'll post back once we are closer on that.
    Ken Puls, FCPA, FCMA, MS MVP

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training), with my book M is for Data Monkey, or our new Power Query Recipe cards!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  8. #8
    Super Moderator Bob Phillips's Avatar
    Join Date
    Mar 2011
    Posts
    1,782
    Articles
    0
    Excel Version
    O365
    Quote Originally Posted by jmlee View Post
    PQ does the work in a snap, but then I have the tedious exercise of renaming eight worksheets. I want to avoid VBA, because I don't want to send .xlsm sheets to the customer, nor do I want to complicate the my template and monthly results files.
    You could knock-up a simple add-in to do it, then it stays with you.

  9. #9
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,379
    Articles
    45
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    Quote Originally Posted by Bob Phillips View Post
    You could knock-up a simple add-in to do it, then it stays with you.
    True enough.

    For what it's worth, I think this is useful and a common enough use case that I've added it to Monkey Tools. The coding is done, we just need to do a bit of UI work on the DestinationSleuth form, so hopefully it should be out later this week if all goes well.
    Ken Puls, FCPA, FCMA, MS MVP

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training), with my book M is for Data Monkey, or our new Power Query Recipe cards!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  10. #10
    Seeker jmlee's Avatar
    Join Date
    Apr 2020
    Location
    Bonn, Germany
    Posts
    6
    Articles
    0
    Excel Version
    O365
    Great to hear! I am going to take a closer look at monkey tools today.

Page 1 of 2 1 2 LastLast

Tags for this Thread

Posting Permissions

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