Forum Discussion
[URGENT] Get Data from SharePoint Folder Dynamically
There are 2 ways:
Assuming you use data pipeline, you can use sharepoint REST API to copy the file from sharepoint into a lakehouse staging zone :
Note : Though the link is for ADF/synapse, data pipeline concpet remains the same.
You can parameterize the file name as per your convenience
In case if you want dataflow,plz follow the below link as reference :
Hi JatinSaini,
We appreciate your inquiry through the Microsoft Fabric Community Forum.
Please follow the steps mentioned below to dynamically ingest, cleanse, transform, and load data from SharePoint into a Data Warehouse using Microsoft Fabric:
-
Store all the files in a specific SharePoint folder following the naming convention FileName_Month.
-
Open Dataflow Gen2 in Microsoft Fabric and connect to the SharePoint folder by providing all the necessary details, and select the folder where the files are stored.
-
To dynamically handle the changing file names, use Power Query M code to parameterise the file path based on the current month:
a) Add a custom column in Power Query to generate the file path dynamically.
b) Use expressions like DateTime.ToText(DateTime.LocalNow(), "MMM") to fetch the current month and construct the file name dynamically. Please refer to the sample code below:let
CurrentMonth = DateTime.ToText(DateTime.LocalNow(), "MMM"),
FileName = "FileName_" & CurrentMonth
in
FileName -
Use the Power Query editor to clean and transform the data as required. Save and close the dataflow.
-
Create a new Data Pipeline in Microsoft Fabric to orchestrate the data ingestion process. Add an activity to ingest data from the Dataflow Gen2 created in the previous steps.
-
Add transformation activities to perform any additional processing required on the data before loading it into the Data Warehouse.
-
Add an activity in the Data Pipeline to load the cleaned and transformed data into the Data Warehouse. Use the COPY statement for efficient, high-throughput data ingestion.
-
Schedule the Data Pipeline to run on a monthly basis or as per the file addition schedule. Set up triggers within the pipeline to ensure it executes automatically whenever new files are added to the SharePoint folder.
For additional guidance, please refer to the following resources:
- Set up your SharePoint folder connection - Microsoft Fabric | Microsoft Learn
- Ingest data with Microsoft Fabric - Training | Microsoft Learn
- Ingesting data into the warehouse - Microsoft Fabric | Microsoft Learn
If you find this response helpful, kindly mark it as the accepted solution and provide kudos to assist other members with similar queries.
Best regards,
Pavan