Do you think I have unreasonable expectations for using SharePoint folders as a datasource with this large number of files and folder structure? I am curious to know if others have had success with similar situation.
I am doing query development in Excel Power Query and Power BI Desktop query and then copy pasting in to Power BI Online Service Dataflow
I am experimenting using a SharePoint folder with 4,000 csv files as a datasource. Files are all small 5 to 20 kb size. Files are in nested structure no more than 3 levels deep eg main folder \ subfolder 1 \ subfolder 2. Total rows for all 4,000 files is about 390,000 rows. Files have between 3 to 10 columns.
I have the exact same content on my local drive to use as a local datasource for development purposes runs very fast:
1) A query that returns a basic list of files finishs in seconds.
2) Another query that uses function & parameter to extract rows from binary returns all rows from the 4,000 files (about 390,000 rows) in less than 10 seconds.
However, when using the Sharepoint folder as a datasource:
1) The query that returns a basic list of files takes up to 10 minutes to finish but most often times out.
2) The other query that uses function & parameter to extract rows from binary returns all rows from the 4,000 files (about 390,000 rows) has never finished, always timing out returning "Error: DataSource.Error: SharePoint: Request failed: " error.
This is the case regardless of using Excel Power Query, Power BI query or Power BI Online Service Dataflow.
Ideas? Suggestions?
