Excel/ Power Query/ Pivot as a web part in SharePoint?

Copper Contributor

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.  I've also posted this request in another excel forum, but thought that this may reach a wider audience - apologies if you're seeing this twice.  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

1 Reply

Hi Kerry,

 

If in brief, querying your data by Power Query add data to data model, add measures if necessary and publish the file on Power BI services as dataset. It will be refreshed automatically. On the top, build Power BI report directly in services using Web interface or better by Power BI Desktop. Report could be published on Sharepoint Online site as web part. However, Power BI Pro licenses are required for users if nothing changed.

 

You may check that blog https://www.linkedin.com/pulse/excel-power-bi-teams-unlocking-value-your-enterprise-data-wyn-hopkins... by @Wyn Hopkins - that's not exactly your case, but close to it.