Extract 70k rows from PBI using Automate

[nok]

Hello!

I have a visual table on my Power BI dashboard that can have up to 70k rows and I want to create a flow that extracts all this data into an xlsx or csv file (or even insert each row into a sharepoint list).

However, when I run the action "Run query against a dataset" it only brings me 503 row and not 70k. I saw that this action has a limit, but has anyone found a way to extract all the rows in an Power BI visual table through automate?

[FLMike]

Hi @nok 

Just curious how wide is your Table? Can you limit the columns you want back? Technically it should give up to like 100K, but depends on lots of things

If I have helped you, I would really appreciate if you please Mark my answer as Resolved/Answered, and give it a thumbs up, so it can help others

Cheers

Thank You
Michael Gernaey MCT | MCSE | MCP | Self-Contractor| Ex-Microsoft
https://gernaeysoftware.com
LinkedIn: https://www.linkedin.com/in/michaelgernaey

[nok]

Hi, @FLMike Thanks for the reply!

My table has 15 columns that come from 4 different related tables in my PBI. And I need to extract the data with all these 15 columns.

[WillPage]

You might be able to add some code to your DAX query to get 500 rows at a time with in a For each loop by incrementing a variable you use in your query.

If you data has a rank column or an ID or something then you can add that to the visual filter in Power BI desktop, set it to filter for a range of values then examine the DAX in the Performance analyzer tool. When you copy the DAX over from PBI desktop you can substitute the value with dynamic content.

In a a For each loop you can get the iteration number using IterationIndexes('For_each') to get the current index, so then perhaps multiply that by 500 and add 500 to get the bounds of your filter query, e.g the first iteration will have index 0 so you can go mul(IterationIndexes('For_each'),500) in a compose, which equals 0 then in another Compose add 500 to that so you have two values, 0 and 500 then in your DAX query you can filter for ID >= 0 && ID <500.

The next iteration will be 1 so the numbers will be 500 and 1000 so the filter will be ID >= 500 && ID < 1000 etc etc.

You'll need an array variable to store the results - use the union() function to combine the results of each iteration with the contents of the variable from previous iterations and set the Until loops condition to be when the DAX query returns no results.

N.B - you'll hit a wall at 100MB of data in your variable so be careful what columns you select when you're dealing with that much data.

[FLMike]

Hi @nok 

Yeah so when you are combining that also takes a hit (unfortunately).

So I believe you are hitting several things

Width issues, Linked Issues, Size issues (amount of data).

As @WillPage  mentioned the only way that I know of to break this (and it stinks because you have to kinda do it for every time you do this in a flow for Power BI)

You need to find the limit that you can get, which right now seems like 500ish, which I believe is why @WillPage  recommended that.

So yes you would need a way to make rows Identifiable by Index and pull them out Batch by Batch. I will also add, that its possible that batch one gives you 500, but batch 2 gives you 400, so you aren't guaranteed to get even 500 depending on the data in each Row.

I wish there was a better answer, but since you cannot lower the amount of columns, you are talking about a long Do Until loop with a high run time, and a max of 500 per loop.

If I have helped you, I would really appreciate if you please Mark my answer as Resolved/Answered, and give it a thumbs up, so it can help others

Cheers

Thank You
Michael Gernaey MCT | MCSE | MCP | Self-Contractor| Ex-Microsoft
https://gernaeysoftware.com
LinkedIn: https://www.linkedin.com/in/michaelgernaey