I have a strange problem, which I would like to share, hopping maybe someone knows what is going on or even might now a fix.
I have a report with a dataset of around 44 tables, most of the tables having between 20,000 to 200,000 rows. 4 Tables have over 1,000,000 rows. The tables in the dataset are sourced from CSV-files (and a few from Excel-Sheets), which are located in an Office 365 Sharepoint folder.
I know a database or Azure Storage could be a better fit, but it was demanded by the customer to create a solution which is entirely build with Office 365 resources.
The whole Sharepoint folder is imported into Power Query by a parameter pointing to it and then loading the whole folder in one query. Every table is transformed as needed by referencing a "Content" column of a row in this folder query. Only the table queries are enabled for loading to the Power BI report.
Until now everything went well and I build a report containing around 50 pages. Then I did export the report as template. When I open the template it asks for the parameter to choose the Sharepoint folder and then starts loading the data.
Depending on the internet connection, loading can take up to 3 hours at the moment. But it finishes without errors. However, when I check the data in the report, generated from the template, and the report, that was the origin of the template, I find that some tables in the newly loaded report have missing rows. When I select these tables in Power BI and hit refresh from the context menu, they reload and finally have the correct number of rows.
I don’t know where this comes from and the tables with the missing rows are not always the same nor the largest tables in every report generated from the template.
Does someone have an idea about that or did someone experience a similar behavior? I disabled all relationship, background data and parallel loading options for the report file.
I have this problem as well. I have the row in the excel file but if I search for it in power query editor is not found. Any clue why is this happening?
unfortunately, we could never figure out why this happens, but it seems to have to do with the Excel-Format and maybe Share Point or both. We changed the format of all files to CSV and moved away from the Share Point connection this fixed the problem somehow.
My guess on the root cause:
From other services like DataFactory I know, that large CSV file are red as stream, while Excel files are in some cases not supported to be red as stream, which means they are loaded as one batch before any processing can be done. This might also be the case for Power BI and cause resource exhaustion or timeouts. Today I always recommend to use special formats such as CSV or Parquet or an actual database for large datasets instead of Excel files. Also I do not recommend Share Point when it comes to large data files.
I am sorry, that I do not have a better answer yet.
According to your description, did you mean that when you view report, you will miss some rows? Yo could check whether this is caused by the aggregation or relationship? and you said that it will show all rows when hit refresh from the context menu, it seems like the refresh issue(did you modify data after load it in model?). I am not sure which cause this problem, if it is published on service, will it work correctly or not?
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
thank you for your reply.
The deviation is visible in the Data View with all filters removed. In this case relationships and aggregation should not be an issue, right? Thank you for the idea of trying a report refresh in Power BI Online. I uploaded the broken report and a sheduled refresh did fix the deviation. It seems there is something wrong with how Power BI and sharepoint communicate over the network outside of the Microsoft Cloud.
I am currently talking to a support person of Microsoft Power BI about that.
Find out who won the T-Shirt Design Challenge and the top 3 finalists.
Find out more about the March 2023 update.