Naming new worksheet during "load to "

[jmlee]

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

[Ken Puls]

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).

[jmlee]

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.

[AliGW]

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

[jmlee]

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…"

[AliGW]

I’ve voted for it.

[Ken Puls]

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.

[Bob Phillips]

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.

[Ken Puls]

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.

[jmlee]

Great to hear! I am going to take a closer look at monkey tools today.