• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

consolidate many workbook with many worksheet in powerquery

C

Cheloo

New Member
God morning the greatest Team

Please, I am requesting your expertise in merging and consolidating multiple workbooks in Excel using Power Query.
I have budget sales data spread across two workbooks, each representing a different town (K Town, New Layout). Each workbook contains four worksheets, with each sheet representing a different quarter (Q1, Q2, Q3, Q4) of the budget year.
My goal is to consolidate all of this data into a single table, organized by quarter, town, and product. I would be incredibly grateful if you could guide me through the steps using Power Query.
Thank you in advance for your valuable assistance.
Best regards,
 

Attachments

  • Budget Reg 2024 K TOWN.xlsx
    43 KB · Views: 3
  • Budget Reg 2024 NEWLAY OUT.xlsx
    43.1 KB · Views: 2
Hi Cheloo

I've explained that a few time and you might find more details in my other answers if you can find them via the search. Here is the big steps solution.
  1. save those excel files in a folder (can be SharePoint)
  2. Open a new excel and on Get & Transform tab look for From file -> From Folder (or look for From SharePoint Folder)
  3. Click on Combine and transform
  4. follow the process as proposed in the UI You need to do some additional mojo to deal with those pivoted and double headers for each of the quarters. Chandeep on YouTube (goodly.co.in) has a tutorial on that with some more advanced code.
  5. Save and load to pivot table and you can create your reports.
 
You must log in or register to reply here.
Back
Top