Connecting Sharepoint folder to Power BI

[analyst123]

Hi,

I am trying to create a power automate function to connect excel files in a sharepoint folder to Power BI. When I am doing so directly on Power BI, it is not transferring all of the files so doing it through power automate will help resolve the issue. Can someone please help me with the steps needed for this to work? I am mainly looking for the Power Automate prompt for it to generate a flow for me. 

[v-csrikanth]

Hi @analyst123 

I wanted to follow up since I haven't heard from you in a while. Have you had a chance to try the suggested solutions?
If your issue is resolved, please consider marking the post as solved. However, if you're still facing challenges, feel free to share the details, and we'll be happy to assist you further.
Looking forward to your response!

Best Regards,
Community Support Team _ C Srikanth.

[v-csrikanth]

Hi @analyst123 

We haven't heard from you since last response and just wanted to check whether the solution provided has worked for you. If yes, please Accept as Solution to help others benefit in the community.
Thank you.

If the above information is helpful, please give us Kudos and mark the response as Accepted as solution.

Best Regards,
Community Support Team _ C Srikanth.

[v-csrikanth]

Hi @analyst123 

It's been a while since I heard back from you and I wanted to follow up. Have you had a chance to try the solutions that have been offered?
If the issue has been resolved, can you mark the post as resolved? If you're still experiencing challenges, please feel free to let us know and we'll be happy to continue to help!
Looking forward to your reply!

Best Regards,
Community Support Team _ C Srikanth.

[v-csrikanth · Accepted solution]

Hi @analyst123 
Thanks for reaching out to the fabric Community.

When Power BI’s Combine Binaries, step silently skips files it cannot parse, you may lose data without any clear notification. To ensure all 32 files are processed, you can choose one of two workarounds:

Workaround 1: Fail Fast Diagnostics in Power Query

• In your SharePoint Folder query, disable the Skip files with errors option.
• Once the automatic steps run, right click the Invoke Custom Function step and select View Errors. This will list each file and the specific parsing error (e.g., mismatched sheet names, unsupported formats, blank tables).
• Correct the source files rename sheets, convert legacy Excel formats to .xlsx, or remove empty tables so that all files conform, then reapply Combine Binaries to include every file.

Workaround 2: Pre Consolidation via Power Automate

• Create a flow triggered by file changes in your SharePoint folder.
• Use List files in folder to retrieve all Excel files, then loop through them with List rows present in a table (Excel Online).
• Append each file’s rows into an array variable.
• After the loop, use Create CSV table on the consolidated array and Create file to save it (for example, as CombinedData.csv) in SharePoint.
• In Power BI, connect to Text/CSV and point it to CombinedData.csv. You now have a single, guaranteed complete source.

Either method will capture all of your files reliably. Option 1 keeps everything in Power Query, while Option 2 offloads the work to Power Automate so Power BI only ever ingests one consolidated file.

If this resolves your issue, please give us Kudos and mark the response as Accepted as solution.
Best Regards,
Community Support Team _ C Srikanth.

[PwerQueryKees]

I am regularly retrieving all (thousends) files from big sharepoint sites. 

What do you mean by "When I am doing so directly on Power BI, it is not transferring all of the files"? At what point in your (power) query are you missing the files? Do you get a message?

I understand you are trying a work arount through Power Automate, but that will result in a convoluted solution that will be hard to maintain by anyone else beside you...

As @Cookistador remarks: You will see only 999 in the powerquery editor, but that is not specific to SharePoint. You can use the 999 you do see to develop your query, on refresh is will return all.

And try both SharePoint.Folders() and SharePoint.Contents(), one of them may work the best for you.

[analyst123]

Hi,

These are the steps that I am following to connect my sharepoint folder to power bi:

1. Get data -> Sharepoint folder -> Paste link -> Transform data

2. Then I am filtering out the source column by only choosing the folder I want.

3. Then I combine the data and click on 'skip files with error'.

Now, I had almost 32 files in that folder and only 15 of them are loading in power bi. I checked all of their formats and everything is the same, inlcuding the sheet tab names. 

So, I was suggested by someone to do this through power automate, by automating combining all these files into one and getting a link for power bi to connect everything. 

I do not get any error message since I choose the option to skip these files but if I go back to view the steps I can see which of these have an error. 

I hope this is a better explanation. 

[v-csrikanth]

Hi @analyst123 
Thanks for reaching out to the Fabric Community!

Follow these simplified steps to connect your SharePoint folder to Power BI using Power Automate:

• Ensure all Excel files are stored in a specific SharePoint document library or folder.

• Go to Power Automate and select Create → Automated cloud flow.

• Name your flow (e.g., SharePoint to Power BI Refresh), choose the trigger "When a file is created or modified in a folder (SharePoint)", and click Create.

• Enter your SharePoint site address and the folder path you want to monitor.

• Add the action "Refresh a dataset" from the Power BI connector and select your Workspace and Dataset.

Reference links : https://learn.microsoft.com/en-us/power-query/connectors/sharepoint-folder 

This setup assumes your Power BI dataset is directly connected to your SharePoint folder.

If the above information helps you, please give us a Kudos and marked the Accept as a solution.

Best Regards,
Community Support Team _ C Srikanth.

[nathancwatkins]

Do you mean like "When a file is updated in SharePoint, trigger a dataflow refresh"?

[analyst123]

No, I am unable to get all of my sharepoint files to power BI, so I need a function where I can combine the sharepoint files and connect the main excel to power BI

[Cookistador]

Hi @analyst123 

Normally Sharepoint will retrieve natively all the files,  if you look in Power Query, you are limited to 999 rows, so you have to apply some filters to retrieve the files you need