Detailed report in Excel from PBI dataset

[Anonymous]

Hi all!

I have a report in Power BI connected to a "large" dataset (about 10M rows, 12 tables, 1 fact).

This report have good performance in my workspace and in my app.

Now I need a dynamic excel file with the detail of each row (with join with dimension), with the possibility to filtering. The filtered table can have about 30k rows and 10 columns.

I tried to build a connected excel with pivot table but is really slow (sometimes it goes in error).

I tried with table feature but as far as i know there is no possiblity to have dynamic filter.

Are there another options?

Thank you!

[rajendraongole1]

Hi @Anonymous -In Power BI Service, navigate to your workspace and locate the dataset.Click on the … (More options) next to the dataset.Select Analyze in Excel.

it allows you to create PivotTables in Excel that connect directly to your Power BI datasets. While exporting large, detailed datasets from Power BI to Excel can present challenges, leveraging the right tools and optimization techniques can significantly enhance performance and usability. By creating optimized datasets, using Power Query for efficient connections, and optionally automating the refresh process with Power Automate, you can achieve a dynamic and manageable Excel file that meets your analysis needs.Use the PivotTable fields to select the dimensions and measures you need.Apply filters directly within the PivotTable to refine your data. Since you're dealing with a filtered subset (around 30k rows), this should perform efficiently.

using power automate, Requires setting up a flow in Power Automate, which might need some learning.Power BI Limitations: The Export To File action has limitations on file sizes and may not handle very large exports efficiently.

Hope this helps.

Create Excel workbooks with refreshable Power BI data - Power BI | Microsoft Learn

DAX Tools - Analyze in Excel for Power BI 1 - Introduction (youtube.com)

[Anonymous]

Hi @rajendraongole1 ,

thanks for your supoport, but these are all the tasks I already did.

I have a subset of 10M rows and pivot table querying seems not a efficiently solution for a detailed list dynamically refreshed.

I wish that there is a way to use the the table feature instead of pivot, but it seems not.

Thank you.