Hello,  I think my question can pertain to Power Query, Pivot, and BI, but I am hoping to get a better understanding of which route is most efficient, accessible, and if there are any things that need to be considered when adding a component to SharePoint. I am not a coder, so without training, that is not a viable option for me :).  If I am in the wrong place, please let me know.  Thank you!

Scenario:

I have a site collection in SharePoint and want to roll-up tasks from sub-sites to the the Parent.  The owner of the Parent needs to be able to easily pull information from the data (point and click to filter for what is needed).

After a few considerations, I thought why not export all excel files from SharePoint, import the tables into Power Query, combine all tables, and then make it fancy and easy to interact with either in Pivot, PowerBI, or even PowerApps or Flow and add it to the home page in SharePoint as a web part.  Maybe even a mini dashboard with slicers??

I've completed the steps through combine all tables in Power Query.  But then thought, how will I best ensure that the data refreshes (and can this even happen), how best can I add this as a web part to SharePoint - there may be restrictions in who can view or how info can be shared in SharePoint with PowerBI, and are there any nuances that I need to consider in adding this data to SharePoint.  

All insights are greatly appreciated.  Thank you in advance.

Kerry