Forum Discussion
Populating a Master Sheet using a snippet of data from multiple Excel files in sub folders
I am desperate for some help on this one.
I will do my best to explain. Feel free to ask questions if I haven't made myself particularly clear.
I have a Sharepoint location that holds a number of folders for each of our branches across the UK, the requirement is that every so often a branch manager will drop their rosters into this folder.
There is a lot of information within these individual Excel files, but I just want to take a snippet from a single page from each (about 20 rows across 5/6 columns).
Within the same folder, I have a Master Sheet that I need to populate automatically when more Excel sheets get put into the Sharepoint location. We have 14 branches, and need data for 52 weeks of the year.
The end goal is to use a snippet of the data to create Power BI visual, which is fine for me, it is just getting the data from each of the Excel sheets to populate automatically each week within my Master file that I am having trouble with.
Thank you :)
1 Reply
If all files have the same structure you may use From SharePoint folder connector with Power Query to transform and combine all files at once.
If it'll be used in Power BI in general you don't need Master Excel file. With same query as above you may dataflow using Power Query Online. Dataflow could be loaded into semantic model. Or in Excel if you need it for some purposes.
