Reduce the size of an Excel report on a Power BI for Office 365 site
You can upload any Excel report to a Power BI for Office 365 site. An Excel report with a Data Model has two parts: the Data Model and the rest of the report—the core worksheet contents. If the report meets the following size limits, you can view it in a browser and take advantage of other Power BI for Office 365 site features:
The report as a whole can be up to 250 MB.
The core worksheet contents can be up to 10 MB.
Here are some things that can make the core worksheet contents larger than 10 MB:
If you can’t view a report in the browser
You can save a report larger than 10 MB to a standard SharePoint Documents folder on Office 365. If you have a Power BI for Office 365 license, you can open reports up to 30 MB in a browser. However, if it’s larger than 30 MB, when you try to open it in a browser you see:
One option is just to open the report in Excel.
If the report has a Data Model, you have some other options:
Create a memory-efficient Data Model to reduce the overall size of the report.
Move the tables to the Data Model and delete the tables from the report.
Read more about file size limits for reports in SharePoint Online.
Remove tables from a report
If you import data using Excel or Power Query, you might have the same data in an Excel table and in the Data Model. Large tables in Excel may make the report too large to view in a browser. Removing the table in Excel and keeping the data in the Data Model can save a lot of space in the core worksheet contents of the report.
When you import data:
In Power Query : Clear Load to worksheet.
The table is imported only into the Data Model and the table in Excel disappears.
From the Excel Data tab and selected Table in the import wizard: Go to Existing Connections > click the connection > Only create connection. Delete the original table or tables created during the initial import.
From the Excel Data tab , don’t check Table in the Import Data box.