Excel files From Sharepoint folder to Lakehouse

[Felpan]

Hi I am looking for advice how to get excel files that are stored in a Sharepoint site under Shared documents.
I tried Dataflow Gen2, but an error occurred beacuse I have a dynamic function. 
My goal is to acces a folder and combine multiple excel files to one table. What is the best way to get this table in Onelake?
Should I do the tranformation before creating the table in Onelake og should i get at the files first in Onelake and then transform?

I need to know what is the workflow for importing data from multiple excel files that is stored in Sharepoint.

[v-veshwara-msft · Accepted solution]

Hi @Felpan ,
Thank you for using Microsoft Fabric Community,

The issue you're encountering is likely due to the use of dynamic URLs or functions like Web.Contents(), which aren't supported in Dataflow Gen2.
A similar scenario was discussed here: Solved: DataFlow does not save due to dynamic data source - Microsoft Fabric Community 

To achieve your goal of combining multiple Excel files into a single table in OneLake, the best practice is to use the SharePoint Folder connector in Dataflow Gen2. Provide a fixed site URL (e.g. https://contoso.sharepoint.com/sites/yoursite) and avoid dynamically constructing URLs. Once connected, you can filter the relevant files and use the Combine Files feature in Power Query to merge them.

Workflow: Importing Excel Files from SharePoint to OneLake via Dataflow Gen2

1. Create a Dataflow Gen2
   Go to Microsoft Fabric > Data Factory > New > Dataflow Gen2.

2. Connect to SharePoint Folder
   Select Get Data, choose SharePoint Folder as the connector.
   Enter the static site URL (e.g. https://contoso.sharepoint.com/sites/yoursite) and authenticate with your org account.

3. Filter and Combine Files
   In the preview, filter for the required Excel files (e.g. based on file name or extension).
   Use the Combine Files option to merge them into a single table.

4. Transform Data
   Apply necessary transformations in Power Query (e.g., filter rows, rename columns, change data types).

5. Load to OneLake
   In the output settings, select your destination.
   Choose to create a new table or load into an existing one.

6. Schedule Refresh (Optional)
   Set up a refresh schedule to update data as new files are added to the SharePoint folder.

   For a detailed walkthrough, you might find this article helpful: Get Data from SharePoint Folder using Fabric Dataflow Gen 2.

Regarding your question about when to perform data transformations:

1. Transforming Before Loading into OneLake:

Use Case: When you want to store only cleaned and structured data in OneLake.

Benefits: Reduces storage of unnecessary raw data and keeps your Lakehouse organized.

2. Transforming After Loading into OneLake:

Use Case: When you need to retain raw data for auditing, staging, or complex processing.

Benefits: Provides flexibility for advanced transformations using notebooks or SQL after data is loaded.

The choice depends on your specific requirements.

Hope this helps. Please reach out for further assistance.
If this post helps, then please consider to Accept as the solution to help the other members find it more quickly and a kudos would be appreciated.

Thank you.

[Felpan]

Thank you for your answer this is good information.

[lbendlin]

Your Dataflow Gen 2 approach should work.  I have a blog entry pending that discusses combining multiple files in a firewall safe way.  Should be out by tomorrow.

[Felpan]

Thank you, looking forward to your blog entry. Do you have a link?