Forum Discussion
How to auto-update an Excel workbooks that is connected with other workbooks
I have Excel files that are combined as one. But I want the combined workbook to be auto updated if there are any changes in the Excel workbooks. Is there any way for this?
- I'm sure the answer is "yes, there are ways" but your description is pretty sparse with details, and as the saying goes, the devil is in the details.
So tell us more. Where are each of the files (on-line, on your computer); who has access; how is each updated? are they all open at the same time? etc. etc.- they are all stored in the sharepoint but only respective people have access to the Excel workbooks for example there are 5 workbooks named A, B, C, D, E and they are all only available to respective 5 people but the admin wants the combination of these 5 workbooks together as one so that he/she can upload it to power BI and they will not be open at the same time and the changes that will need to be made will also not be the same. Hence I want the combined workbook to have something like a scheduler so that every once a week or so it will get updated
You may use Power Query to combine these few files into one. If in master file load Power Query into data model as well, for such file published on Power BI you may define scheduled refresh. If master file is needed only in Power BI as data source, perhaps it's better to create dataflow which collects information the same way.
If there is the risk what source file could be kept opened, with Power Automate you may copy such files into another place on SharePoint and make Power Query transformations on copies. Such flow also could be scheduled.
