Forum Discussion
how to dynamically load data to separate sheets? TIA
this is the mock data I used for reference
I have a set of monthly tasks for our department that I need to complete. After cleaning the data in a table(normally it is like 500-700 rows of data with like 50-80 locations, I need to load it into separate tabs based on location. For example, in the "San Diego, California" tab, I want the data to only include records related to San Diego, CA. Normally it should be like 50 to 80 tabs.
| Staff | Unit Price | Quantity | location |
| A | 7 | 82 | San Diego, CA |
Is there a way to automate this process in Power Query so that I don't have to manually duplicate and save a separate sheet for each location using the "Group By" function? I need a way to dynamically load data into the corresponding location tabs without doing it manually. Any advice will be appreciated!
3 Replies
Is this for Excel? Power Query does not support dynamic destinations.
It will require a complete write-up to automate this workflow. While PQ supports many to one (like consolidation etc) but it doesn't support one to many. One query can't load into more than one sheet. You will need multiple queries. But one query also can't create multiple queries.
You will need an Excel macro do the job which will split into many sheets after you load the result.
While, it will be a very long answer to integrate with Excel do which I will do some day, but for immediate relief, use the attached macro. You just need to select the column after populating your result or any cell in that column and run the macro and it will populate the respective sheets.
Do you really need separate tabs? Or could you put the data in a pivot table and add a slicer to select the location? Given the low number of records, there don't seem to be technical reasons to split...
