I recently received an email from someone who needed my help combining data from multiple Excel files that were stored in Sharepoint:
Hi Mike
Please can you advise/help. In a SharePoint Library I have 6 folders – 1 per Business Unit. Each folder contains subfolders – 1 per year. Each of those year folders contains 12 subfolders – one for each month of the year.
Once per month I need to create a report and consolidate data from all the files for that particular month. How do I consolidate the data from Sharepoint subfolders using Power Query? Do I have to change the path to import the data for the next month? Or could I use a parameter (which I don’t know how to do)
In this video I demonstrate how to use Parameters in Power Query so that all the user has to do each month is type the required month name into a cell in the worksheet and click Data > Refresh All. Behind the scenes, the month name is passed to Power Query which then combines the data from all the folders with a folder name that matches the specified month name.