Forum Discussion

Sidhant's avatar
Sidhant
Advocate V
11 months ago

Managing and Exporting High-Volume Datasets (~1M Records) in Power BI

Hello everyone, I'm currently working on a use case where I need to manage and export high-volume datasets (~1 million records) using Power BI, and I'm exploring multiple approaches. I’d appreciate ...
Need Help
SantoshPothnak's avatar
SantoshPothnak
Frequent Visitor
10 months ago

Hi @Gabry, @v-hashadapu, @Poojara_D12,

Thanks a lot for all the valuable suggestions shared so far. Really appreciate the depth of details—it has helped us narrow down our direction.

Just to align on our scenario:

  • We’re working with Power BI Embedded (Premium Capacity – P1/F64) reports hosted inside a custom portal application, where end-users (business users, not developers) interact with the reports.

  • This report has a complex prompt-based setup (migrated from MicroStrategy) with 15–20 filters, a mix of date filters, slicers, hierarchical field parameters (2-level), and various field parameter selectors.

  • Based on these selections, the report displays a large table visual where the data easily goes from a few rows to frequently 500K+ rows (sometimes 1M+). This exceeds the Power BI export limit (150K), which is the key challenge.

From your shared ideas, we are evaluating the following approach as the most practical:

Proposed Flow

  1. Power BI Report → Users make selections via slicers/parameters (field & hierarchy prompts).

  2. Trigger via Power Automate → Button in the report passes selected filters.

  3. Fabric Notebook → Notebook applies the same filters, queries the dataset, and writes the resulting data to OneLake/Blob Storage in CSV/Excel/Parquet format.

  4. Power Automate Notification → Once the notebook job succeeds, the flow retrieves the output file path and emails the link or attachment back to the user.

This pattern:

  • Cleanly bypasses the 150K export limitation,

  • Keeps the experience simple for end-users (just a button click),

  • Scales to handle 1M+ rows,

  • Gives flexibility in format (CSV/Excel/Parquet for downstream use).

Notes on Alternatives

  • Paginated reports → We tested column visibility rules, but as many of you highlighted, exports still tend to include all columns unless the dataset itself is dynamically shaped. That adds unnecessary complexity for 20 prompts, so we may not proceed here.

  • Translytical Task Flows / UDFs → These look very promising, but as of now they have limitations in Power BI Embedded, hence not adopting in this first phase. We’ll revisit once that support matures.

  • External Tools (Bravo/DAX Studio) Sidhant  mentioned → Great for developers, but not suited for end-users in an embedded portal scenario.

Where We Are

Our team Sidhant  Anonymous and myself shall continue POC work with the Fabric Notebook + Power Automate orchestration and loop back here once we validate this end-to-end flow. Thanks again for pointing us in the right direction :-).