Power BI – 30,000 Row Export Limit Workaround

D Walsham • 16 March 2020

Learn to export all of your data without restrictions

Introduction

In Power BI, when creating any kind of reports, dashboards which will contain an X amount of rows of data in which your queries will be pulling, a lot of us will want to be able to deliver an exportable report in which we can share.

So once going to a specific visual or in most cases a table visual we click the … ellipsis button in which we are presented with the following options below;

If you have over 30,000+ rows of data in your visual you may see the following notification message below;

Once clicking the OK button you will then be given the option to save the .CSV file to your desired location, and once opening you will see the export has only a maximum of 30,000 rows.

Why do I have this issue

The reason for this is because Power BI Desktop has an ultimate maximum of rows which can be pulled from an export which is 30,000 rows and this is because a .CSV file has this maximum within Power BI.

A .XLSX export on the other hand can have an export of 150,000 rows which is 5X better than the .CSV but the only issues is that you cannot by default do this within Powe rBI Desktop and also if you have data which exceeds 150,000 rows this can still cause issues.

Export Workaround

It’s quite a basic workaround but it does allow you to perform an export of every single row which you have.

On the left hand side you will see the pane which contains three types of views, the first of course being the main visuals you see, but the second option is the Data view which shows you a table of the entire data in which you have which is currently summarized within the Visual view.

Here you can see the entire table data view of everything.

If you right click any column for example and select Copy Table, wait for a few seconds whilst the time loading icon takes its course.

Open up a new excel spreadsheet and click paste.

Here you will now see all of your data and that’s including every row and every maximum row barrier exceeded.

In the bottom right hand corner you can see the count is 47357 which is over the 30k rows maximum

Microsoft Endpoint Manager: In-Place Upgrade or Side by Side Part 4 - SQL Server Requirement Analysis

by D Walsham • 13 December 2021
Looking through the current SQL Server topology and how it affects our decision

Microsoft Endpoint Manager: In-Place Upgrade or Side by Side Part 3 - Current Branch Upgrade Approaches

by D Walsham • 19 November 2021
Impact of Current Branch Upgrades

Microsoft Endpoint Manager: In-Place Upgrade or Side by Side - Part 2 - Operating System Considerations

by D Walsham • 29 October 2021
Introduction

Microsoft Endpoint Manager: In-Place Upgrade or Side by Side

by D Walsham • 7 October 2021
Introduction

Endpoint Manager: Device Estate Cleanup

by D Walsham • 6 October 2021
Introduction

Modern Workplace Management: Creating Applications Common Practices - LOB vs Win32 Package

by D Walsham • 3 September 2021
Introduction

Modern Workplace Management: Build Endpoints Without SCCM Part 6

by D Walsham • 12 August 2021
All the parts of the series we went into great detail about how we analyse an end to end solution and how we would design a solution in which would allow us to build endpoints without SCCM being a dependency. Whilst we did this, there is another scenario which we have not touched on yet, which is the hybrid scenarios. In a perfect world ideally you would have your Azure Active Directory within the cloud, every machine meets the recommended requirements for Windows 10, everything is imported into Intune/Autopilot and everyone is happy. But we know this isn't realistic in all cases. Many organisations cannot just simply up and go from on-premise into the cloud therefore the checkpoint here is of course getting into hybrid solutions such as; Co-Management Between Intune and SCCM Hybrid AD with Azure AD and On-Premise AD syncing together These things can play a very interesting part in how you would tackle this if you envisage the next step in the blueprint is to be in a position in which you can build and manage endpoints soley within Intune. With this final part of the series we will go in-depth in how the common hybrid setups look like and how we go about moving into the next step of being able to manage and build devices without SCCM.

Modern Workplace Management: Build Endpoints without SCCM Part 5

by D Walsham • 29 July 2021
In continuation from the previous part where we had discussed how we create the "on site" piece of the solution, this was the part which would allow us to get our endpoints into a state in which they would essentially be ready to go through the Autopilot process. Which leaves our next piece of the puzzle, to begin the configuration of the actual backend side that resides within our Endpoint Management console. And you will see how everything ties up together to satisfy the full end to end process of getting an unknown (or known) device to proceed thorough the whole workflow to be finally managed by Intune without the aid of SCCM taking part in any of the prerequisites or preparation at hand.

Modern Workplace Management: Build Endpoints without SCCM Part 4

by D Walsham • 15 July 2021
In this part we are now going to look into the technical step by step points on how we put everything together. In the previous part we spoke about the structure of how we would asses whether a machine was actually ready to be built with Autopilot or not with a build checklist process which would step through all areas which would cover an endpoints eligibility. Now with everything planned out we finally want to step into making things reality by putting everything together.

Endpoint Manager: Find machines not in Autopilot

by D Walsham • 2 July 2021
When it comes to managing your endpoints in endpoint manager, one of the things you may be looking to do is to get all of your Intune registered machines to also be enrolled as Autopilot devices. Now we can of course just have the deployment profile deployed to all machines and then hit the "Convert targeted machines to autopilot" but this might not necessarily be feasible for every client. We may want to perform some due diligence first so we can at least understand what devices in Intune are not in Autopilot.
Show More