Forum Discussion
Advocate VManaging and Exporting High-Volume Datasets (~1M Records) in Power BI
Advocate VHi folks,
Just a quick update so I was able to resolve the duplicate headers issue just had to make one slight update in the 'Append to String variable' action (within the do-until loop) where previously I was appending the outputs of 'Create CSV' action so what it did was based on the number of iterations it kept adding the headers
So to avoid this I made used the below expression:
if(equals(variables('LoopVar'), 2), body('Create_CSV_Table'), join(skip(split(body('Create_CSV_Table'), decodeUriComponent('%0A')),1), decodeUriComponent('%0A')))Now there are still doubts over few other points which I had shared in my last reply which were:
i) Is there any limitation with respect to the approach that I am using (Running a query against the dataset {semantic model}), as I had come across one comment stating it does not work when records are more than 2M/2.5M
ii) The next thing was I was planning to use this solution in an Power BI embeded report so therein will there be any challenges (with respect to integrating this solution) since in my case the report is an embeded in an existing report of a client through which end user will be interacting.
iii) The other thing was the security aspect, will RLS be carry forwarded in this solution.
(Assume we have 2 users: Tim can access Asia sales and John US & Europe region sales so in this case once they make the selections and export so will they be only get the records which they have access to or will that not be the case?)
iv) With respect to download I just wanted to have few approaches which can be incorporated:
Usually something like a local download (for which I did find an article: which I had shared in one of my earlier reply/comment but that requires a standard gateway setup to be done, which I am unsure will be accepted) and second thing that had asked is there any way that user specifies a location (SharePoint/OneDrive) so can that be passed in Power Automate and we save the file there.
If not, in one of the replies some of recommeded to use common SharePoint site, so I had couple of questions with respect to that:
(Since the solution will be end user centric how the SharePoint site access should be set up when it comes to security so that instead of getting the full control to anonymous users/ users outside the organisation we just give them minimal control -> then since there will be multiple end users {as we will have limited information} how do we create separate folders based on each user and then configure the aceess so that each user can only their own files and not others (for that I have gone through Grant access action {Sharepoint}), but the previous points is where I am unclear.
So v-hashadapu , Gabry , Poojara_D12 have any inputs please do let me know.
Regards,
Sidhant.
Community SupportHi Sidhant , Thank you for reaching out to the Microsoft Community Forum.
The current approach using Run a query against a dataset works well up to a point, but it isn’t designed for sustained multimillion row exports. Once the data volume crosses roughly 2, 2.5 million rows, you can expect timeouts or throttling because the dataset endpoint isn’t optimized for bulk extraction. It’s mainly intended for querying visuals, not streaming data dumps. You can extend performance slightly by batching through indexed ranges or smaller TOPN queries but reliability drops as size grows. For consistent large scale exports, the recommended pattern is to offload that work to a Fabric notebook or pipeline that writes the data directly to OneLake or Blob storage in CSV or Parquet format. This avoids Power BI’s memory and query timeout limits altogether.
When it comes to Power BI Embedded, there’s no inherent problem embedding this type of export driven report. The main consideration is identity, specifically, whose credentials are used when the export flow runs. If the process runs under a service principal or app identity, you’ll lose the per user filtering that Power BI normally enforces. To preserve end user security context, the export should either run using delegated user authentication or explicitly include the user identity through the effectiveIdentity property when calling the dataset or semantic model. As long as the flow executes with the correct user identity, the embedded scenario works seamlessly.
For Row Level Security, it only carries over if the export query runs in the same security context as the viewing user. RLS filters live inside the dataset, but they’re enforced based on who executes the query. If your Power Automate connection or API call uses a fixed service account, it bypasses RLS entirely. The correct implementation is to trigger the export under the user’s identity, so their RLS scope is honored or replicate equivalent row filters in your Fabric notebook or pipeline if delegated access isn’t possible. Testing the flow with restricted users is the quickest way to confirm RLS enforcement before rollout.
Regarding download handling, direct local downloads through Power Automate do require either the on premises data gateway or Power Automate Desktop installed on the user’s machine. These setups can be heavy for large audiences, so a better end user experience is to deliver files via SharePoint or OneDrive. You can dynamically create user specific folders using their email or ID as the folder name, then use the Grant Access action to assign permissions after the file is created. This ensures each user only sees their own exports. If the users aren’t part of your Entra ID tenant, you can either invite them as guest users (which is the secure, supported method) or share files via expiring anonymous links for limited access.
Export and email a report with Power Automate - Power BI | Microsoft Learn
Datasets - Execute Queries - REST API (Power BI Power BI REST APIs) | Microsoft Learn
Security in Power BI embedded analytics - Power BI | Microsoft Learn
Manage list item and file permissions with Power Automate | Microsoft Learn
- Sidhant
Hi v-hashadapu ,
Thanks for the detailed breakdown on my reply so I got some information with respect to the RLS (Row Level security) so they have kind of custom setup, for which I will give an overview for better contextSo the thing is end users intteract with the organization portal, so what this portal does (behind the scene is) it generates an unique ID based on the end-users email {this is just created once and stored in backend I presume and not at the time of authentication with Power BI service}
When the Power BI service is accessed (in the backend) at the organization there is a RLS table : 'v_user_facility_map' which is maintained that has 3 columns: id, user_email (end_user email), user_facility_email (kind of admin whose email has the required access within the org)
Example:
table: v_user_facility_map
d_facility_id: 32, user_email: [email protected], user_facility_email: [email protected]
d_facility_id: 32, user_email: [email protected], user_facility_email: [email protected]
And this table is turn connected to one of the dimension table (which denotes what level of access those set of user have, as per what I know)And then a role is created on top of the RLS table (v_user_facility_map)
[user_facility_security_id] IN CALCULATETABLE( VALUES('v_user_facility_map'[user_facility_security_id]), 'v_user_facility_map'[user_email] = USERPRINCIPALNAME() )And then within the workspace (Fabric workspace) we simply set the security for the semantic model.
So I tested this on my end for which I created a simple table (RLSDomainAccess) wherein I had specified the Domain and the email
Similarly created a role within Power BI desktop:And then published the respective report in Fabric workspace and set the respective users under the new role
Then to allow the end user to trigger the flow, I had to add the user under the Run-as option (because before that if the end user tried to trigger the flow it was Unsuccessful)
After this the user were ablke to trigger the flow (the users used the connection of the owner) and this also ensured that RLS was maintained like:
Normal view (with the entire access like super user)Assume 'John' accesses the report who has access to Domain== 'RESTAURANT'
So now based on this setup one thing is clear that RLS will be preserved (based on the setup that we have in the actual organization side) now I had couple of questions now when the flow needs to be triggered right now I tested this with few of my team-mates now in the actual case do the respective admin have to simply mention the email/ or the Azure AD group under Run as so they can trigger the flow.
The other thing you did mention for local download the setup will be quite heavy (for every user) so its better to go for SharePoint, but I'm still unclear about the flow on how to integrate the same in my existing flow.
Queries:
1: How to get the end user details (within this context)
2: You have mentioned to make use of Grant access action, so in this case will the organization where its to be deployed will have to maintain/create a SharePoint site and then the respective SharePoint admin will mange the access related things
3: To create a separate folder for every user there are some intial checks that whether the folder exist for the respective who has triggered (like Tim triggers the flow then we need to check in the respective SP document library a folder exist with his name/email if not then create one -> break the security and then gran the access to only that folder so here is there some kind of Post {HTTP} call that needs to be made: if yes what is the payload that will be passed {API endpoint and the parameters within it}
I hope this context helps you a have better understanding of the context and setup which I am working on.
Poojara_D12 , Gabry if you have any inputs please share your thoughts.
Regards,
Sidhant - Sidhant
Hi v-hashadapu ,
So there has been a progress when it comes to the part of folder creation and storing them within respective folders (you can check the latest comment have started a new reply for the same) now I wanted to understand based on the structure that I just described in my previous reply. The thing is the solution that we have proposed (using Power Automate along with Power BI to overcome the record limit) whether it will work in a complex environment.
The reason being right now what I have done is a just a single table with few set of columns but in actual environment that's not the case there we have multiple fact tables and dimension tables, so therein we need to switch between fact tables, like there are cases wherein field parameters have 10/20 columns, so for now what we were thinking we will just provide the most commonly used columns (from different tables). The other thing is in the UserSelections table as of now I have just placed columns from single table, but is it possible that we can consolidate the columns used in field parameters within a single table (like UserSelections)
'Orders[OrderID]', 'People[Department]' {Here Orders, People are two different tables so can that be consolidated when we construct the UserSelections.
I also wanted to understand that this solution what are the limitiations that things to keep in mind when the data is so large and considering the data model as well.
Gabry , Poojara_D12 if you have anything to add please share those points
SantoshPothnak if I have missed something please do add your points as well.
Regards,
Sidhant