Through Power BI’s Get Data option, we could connect SharePoint and import data into Power BI. This is the first step to create a connection between SharePoint and Power BI. Once this is done, the user can then generate reports with visuals and charts.
Challenge:
NSquare Xperts used SharePoint as a dataset in which the client had over 400,000 records to create a Power BI report. However, when the data was refreshed to obtain the most recent information from SharePoint to Power BI, the process took longer than usual and eventually a timeout error appeared.
Solution:
To address this issue, NSquare Xperts implemented a dataflow within the Power BI Service.
Steps that we followed to Create a Dataflow in Power BI Service:
- Access Power BI Service: Open the Power BI service in a web browser.
- Select Workspace: Select the workspace where you want to create the data flow.
- Dataflow Creation: Click on the New tab and then choose Dataflow Option.
- Define New Tables: Select Define new tables to create a dataflow from the start.
- Choose Data Source: Specify the data source you want to connect to e.g., Excel, SQL Server, SharePoint.
- Select Specific Tables: Once connected, Select and choose the Necessary tables you want to include in your data flow.
- Transform & Preprocessing: Utilize Power Query Editor to clean, filter, and transform the data as needed for your reports.
- Name Your Dataflow: Assign a name to your dataflow for identification.
In Power BI Desktop, you can use newly created dataflow as a data source for your Power BI reports.
Outcome:
- Faster Refresh: By using a dataflow, you significantly reduced the refresh time from hours to less than a minute.
- Scalability: As your dataset Increases, Dataflows can handle the load efficiently. To minimize the refresh time, NSquare Xperts created a dataflow in Power BI Service which remarkably dropped it from 4-5 hours to less than a minute. This resulted in in faster insights and improved efficiency.
- Client Profile: The client is a facilities management company that provides a holistic and integrated facilities management solution in the UAE and beyond.
- Technologies Supported: Microsoft Dynamics 365, Power BI, and SharePoint.