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.
Data Flow

In Power BI Desktop, you can use newly created dataflow as a data source for your Power BI reports.

Image showing created data flows

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.

Leave a Reply

Your email address will not be published. Required fields are marked *