In the first part of this series, we explored the premium method for connecting Microsoft Fabric Lakehouse to SharePoint. This approach leverages advanced features and robust tools, offering seamless integration, enhanced security, and greater flexibility for managing large datasets. However, for many organizations, especially those with tighter budgets or smaller teams, the cost associated with premium solutions can be a significant consideration.
That’s where the free method comes in. While it may not have all the bells and whistles of the premium option, the free method provides a practical and cost-effective way to achieve similar results. In this part of the series, we’ll walk you through the steps to connect your Microsoft Fabric Lakehouse to SharePoint using freely available tools. Whether you’re looking to save on costs or are simply exploring alternative solutions, this guide will show you how to make the most of your resources without compromising on functionality.
As part of this guide, we’ll populate the same SharePoint List that we used as an example in the previous blog.
Step 1: Create a SQL Query in Microsoft Fabric Lakehouse
1. Go to your Workspace and select the SQL analytics endpoint.
2. Select New SQL Query.
3. Use the same SELECT query from the previous blog, but this time, create a view out of it.
4. After running the SQL query, refresh the view by right-clicking on Views and selecting Refresh.
Step 2: Create the Power Automate Flow
Create a flow and choose your desired trigger.
- Add the action Run a query against a dataset
- Select the Workspace
- Enter the following query
EVALUATE
‘View_Name’
Save and run the flow.
Step 3: Parse JSON and Map Data
- Copy the entire output from the previous step.
- Edit the flow again and add a Parse JSON action.
- Use the Use sample payload to generate schema feature to generate the schema using the copied output.
4. Add an Apply to each action and map the values as shown below:
body(‘Parse_JSON’)?[‘results’][0][‘tables’][0][‘rows’]
Step 4: Map Values to SharePoint List
Following this, the most challenging step is manually mapping the values from the previous step in the “Create Item” action within SharePoint.
For example, if we want to map the OrderID column, we need to format it similarly to the sample provided: @{items(‘Apply_to_each’)?[‘DenormalizedSales[OrderID]’]}.
In this context, DenormalizedSales is the name of the view we created in the Lakehouse.
Now if we save and run this, the flow should populate the SharePoint list.
Final Thoughts
Connecting Microsoft Fabric Lakehouse to SharePoint using the free method offers a practical and cost-effective solution for organizations looking to make their data more accessible without breaking the bank. While this approach may require a bit more manual setup—such as mapping values in the Create Item action within SharePoint—it still provides a robust way to ensure that your non-technical teams have the data they need at their fingertips.
By following the steps outlined in this guide, you can successfully create a denormalized dataset that meets the specific needs of your team, all while staying within budget. Although the free method may involve some trade-offs in terms of automation and advanced features, it proves that powerful data integration doesn’t always have to come with a hefty price tag.
In the end, whether you choose the premium or free method depends on your organization’s specific needs, resources, and goals. By carefully considering these factors, you can make an informed decision that best suits your situation.
