Integrating SharePoint Lists with Microsoft Fabric (real time)
Subhad365
7
User Group Leader
Like
Share
ReportHi guyz, howz life?
Today let me help you with SharePoint integration with Microsoft Fabric. Let me give you a situation, where you maintain lists. You have now a requirement to pull in the data into Onelake, so as to enable you to create power BI dashboards/reports out of it.
Problem statement
You have a travel request list like this:Users can raise travel requests and create travel requests, which would be subjected to approvals.
And you want to take this list out into Microsoft Fabric, so that you want to prepare dashboards/reports to understand the travel costs, date of travels, country of travel, etc. The following article can help you with the same.
Before that here goes a small recap describing the background of Microsoft Fabric and it’s capabilities:
Microsoft Fabric is an end-to-end analytics and data platform designed for enterprises that require a unified solution. It encompasses data movement, processing, ingestion, transformation, real-time event routing, and report building. Microsoft Fabric is a data analytics platform that helps organizations manage, access, and act on data. It's designed to address all aspects of an organization's analytics needs, including all ingredients of data movement like: data processing, ingestion, and transformation, as well as real-time analytics to allow users to explore, analyze, and act on large volumes of streaming data in near-real time. Essentially the Fabric could be a well-designed mechanism to separate each and every department of you your organization, by creating separate Workspaces. In a word, Workspaces are places to collaborate with colleagues to create collections of items such as Lakehouses, warehouses, and reports, and to create task flows.
Now over to steps to integrate SharePoint with Fabric 😊
Step 1
Power up your Microsoft Fabric to come to your Lakehouse landing page (experience = Data engineering):For now the tables are empty, we would bring data into here.
Click on Get data >> New data flow gen2:
Click on Get Data >> and then on More:
From here select:
And the following screen will open:
Here we need to populate Site URL here. And the next step tells how to do this.
Step 2
Coming back to your SharePoint site will show you the following (click on Home icon to come here):
You can get the URL from the browser as shown.
Copy this and come back to the previous step:
Click on Advanced Option >> Select the Default mode and click on Next to continue. The default will give you exactly what your default view columns show you in the SharePoint list.
Click on Next to continue.
This will take a while to generate the following output:
Select Travel requests to make the columns be visible on the preview pane:
Click Create to Continue. The following preview of data will come up:
Click on publish to continue.
The following will be shown as under process:
With a notification coming up shortly as:
As the processing ends, you can now come back to your Lakehouse to see the results:
You can get the URL from the browser as shown.
Copy this and come back to the previous step:
Click on Advanced Option >> Select the Default mode and click on Next to continue. The default will give you exactly what your default view columns show you in the SharePoint list.
Click on Next to continue.
This will take a while to generate the following output:
Select Travel requests to make the columns be visible on the preview pane:
Click Create to Continue. The following preview of data will come up:
Click on publish to continue.
The following will be shown as under process:
With a notification coming up shortly as:
As the processing ends, you can now come back to your Lakehouse to see the results:
And not to mention, you can make the copye activity scheudled so as to enable whatever changes are happening in SharePoint to make it flow to Fabric.
Step 3
Select the view mode from the top of the pane to SQL Analytics endpoint:This will give you the options to write your own views, queries and triggers as per your need. Click on Reporting tab:
This will trigger your abilities to create Power-BI reports, dashboards as per your wish. For example, I am selecting all the fields that I need and selecting a pie-chart to prepare a report out of it:
Which when previewed, look like this:
Not to mention, you can further add more ingredients to your report, manage drill-through visuals, and what not 😊
Whew!!! With that let me take your leave, will be back soon with more such cool hacks of data engineering and related integration staffs. Till then take care and much love
*This post is locked for comments