OneDrive for business folder connector

[piotrostr]

Hi all 

Im trying to set up data source based on onedrive folder - ive created flow with power automate to download all the reports when the mail with attahment arrives - it works well, so i have now diffrent folders full of csv files, each file got diffrent name as it comes daily (in some cases every other hour). Each folder contains one type of data, which i would like to combine into separate datasets in power bi (so 1 folder = 1 dataset)

What im trying to do is to connect to the one folder following some old tutorial from 2017, it allows me to connect to the root folder and then i can pick folder path to manipulate and create db based on this. Problematic part is that i have over 1k files (which is the limit).

Is there any uopdate in this matter since we have fabric and all that new upgrades to the source files?

Im failry new to the PowerBi but it seems very odd that Microsoft doesnt support their own tools and i can easily connect to the Google sheets but i cant work with Microsoft Onedrive. Even if i manage its a lot  of workarounds without straight solution to make all this effort a little less time consuming and  for sure much less frustrating.

Any advice please?

[piotrostr]

Hi Pete,

Yes i mean 1k files limit when i try to apply the filter in power query to filter out the folder i need to combine files from. The tutorial i mentioned was including the way of combining files by importing main sharepoint folder and then filtering out the path to desired folder and after that some manual edits to merge files from one path.

Im fairly new to BI and im just trying to figure out things, If this way of storing data isnt really supported i will need to find another way. I think to set up a server or use power automate flow to combine the folder files into one (which seems to be more convenient way).

Thank you for your input.

[BA_Pete · Accepted solution]

Ok, so are you talking about this?:

If so, then you can explicitly define your filter conditions rather than selecting individual items from this list. This allows you to specify if a file path Starts With, Contains, Ends With (etc.) certain text and applies the filter to the entire dataset, ignoring any preview limits:

Pete

[piotrostr]

Thank you Pete,

Having this in mind, building variety of dashboards from multiple files i think merging the files into one "db" on onedrive will allow me to automate it quicker.

I still believe connector for onedrive should be available as at least as some kind of addon with warning about stability.

Anyway, appriciate your help 👍

[BA_Pete]

Hi @piotrostr ,

As you're obviously aware, OneDrive for Business is built on the SharePoint platform. Microsoft are quite open about the fact that they neither encourage nor support SharePoint being used as a data warehouse and, indeed, openly throttle SharePoint requests that behave in this way in order to maintain the stability of the rest of the platform for regular users. To be honest, I have to agree with them on this.

That being said, as far as I'm aware, there's no limit to the number of files you can import, as long as your published PBIX size is < 1GB. When you reference the 1,000 files limit, are you confusing this with the 1,000 row preview data?

Pete