Forum Discussion
Connecting Sharepoint folder to Power BI
Hi Anonymous
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.
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.
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.