“We want to be able to export an unlimited number of rows from PowerBI from any filtered report, retaining aggregations and totals without the need for a developer to assist.”

My heart sank a bit as I heard the request from my new client, a New York City municipal service. I was hoping to leverage Power BI Paginated reports which were released about a year ago. “Yes, we already considered that, but it’s too complicated for our business users,” the client stated when I mentioned the functionality. “I’ll have to get back to you,” I replied.

I knew this was going to be more complicated than it should be. Unfortunately, I was right to be cautious. My early research quickly drew me to this article which states unambiguously that Power BI supports export of 30,000 rows to CSV, 150,000 rows to Excel or 16MB of uncompressed data from direct query data sources. But why?

Clearly Microsoft does not want Power BI to be used this way, and I can understand why. Large amounts of data are expensive to retrieve and store in multiple places. Generating this data to be consumed also requires an application server to connect to the database and write the files and then notify the client when the file is ready for download. It’s also bad data governance to have core business data proliferate in various Excel files on users hard-drives, which can become outdated easily.

To further complicate matters, although you may export limited amounts of data from PowerBI, the total row does not come with the data. This has been a feature request from the community for nearly three years. With that sort of a wait time it seems unlikely that Microsoft will ever implement this feature, pretty-much forcing users to use paginated reports for this functionality.

Being a software developer, I hate limitations placed on the functionality of my application. Not exporting totals is a major miss on this piece of functionality.

Continue reading Part 2 to check out my research into potential work arounds.