We run four interconnected businesses.
I have data for each business in a folder. This folder "gives" the data to several pivot tables through power query. I followed Mynda's tutorial - perfect - thank you - made such a difference.
We began in April 2024 so we now have 28 files - seven files for each section of the business. By the end of the financial year next March, we will have 48 files.
Each file has the business section and date within its "label" so they are easy-ish to find.
What happens next year, I wonder? Yes, I can keep adding files to the folder but is there a different, perhaps more efficient way of continuing?
Can I put files containing each business area in a folder, or put files containing each year's data in to a folder and still use this auto-refreshing power query/pivot table combination for the end result? Can power query take data from more than one folder? If so, will I have to reconfigure the power query and or redo the pivot tables?
Any suggestions?
Thanks
Chris
Hi Christopher,
Glad my tutorial was helpful.
If you wanted to get data from a different folder, you'd need a separate query for this, which you could then append to the original query to create one table. As long as the same original query is still feeding the PivotTables, then they will just include the new data.
That said, if the data in your current files isn't going to change, I would copy the query and load it to a table. Then copy that table out as values into a new Excel file. Then modify your original query to get this file. This will reduce the work Excel has to do at each refresh. You can then append this query to your new 'current year' query that gets the data from the new folder.
Remember, as long as your PivotTables reference the same original query, and the column/field names are the same, you can just refresh them to get new data.
Mynda
G'day
and Thank You
I am new to the world of pivot tables and even newer to that of power query.
So I have, I think, "got it" and will follow you instructions.
So nice to hear from the boss and not a faceless wonder.
Looking forward to more of your excellent videos, tips and hints
Kind regards
Chris
Good morning
I have been preparing data for to add to the query.
The previous owner of one of thebusinesses had some data which I can transform to make the sheet names, and columns match those we use for what is our first year.
I have come across a problem, though, and wondered if you might have a solution or workround.
Our business year and our tax year begin in April. So for our first month I used the 2024_04 for the file name then carried on adding another file for each month. The problem will be when we get to January 2025...it will still be in the fiscal (and our) year but if I use the file suffix 2024_01 that will look like last January; if I use 2025_01 it will look like it might be in fiscal 2025-2026.
Anything you can suggest? Do you know what other companies do?
Thank you in advance
Kind regards
Chris
