Forum Discussion
Advocate VManaging and Exporting High-Volume Datasets (~1M Records) in Power BI
Community SupportHi Sidhant , Thank you for reaching out to the Microsoft Community Forum.
For end-user-driven dynamic column selection, Power BI and the DAX language do not natively allow passing a flexible list of column names into SUMMARIZECOLUMNS at runtime, the set of fields must be known in advance. The best workaround is to dynamically build the DAX query string inside Power Automate before calling the dataset, inserting only those columns that the user selects via UI or parameters. This lets you control exactly what columns are exported while always including Index for batching.
When it comes to user-specific file storage in SharePoint or OneDrive, best practice is to create a separate folder for each user inside a common document library. After the file export, Power Automate can break inheritance and assign permissions so that only the relevant user can access their own exports. Microsoft’s official guidance covers precisely how to do this using the “Grant access to an item or folder” action: Manage list item and file permissions with Power Automate. Having each user’s exports in a folder named after them and applying unique permissions, ensures data privacy and keeps the system manageable.
Dynamically specifying the SharePoint Site Address in the Create File action is not directly supported via user input in the SharePoint connector UI; it’s typically chosen from a fixed dropdown. However, if you want to make it dynamic, you can leverage environment variables or store a user-to-site mapping and use more advanced Power Automate actions, such as the HTTP connector targeting SharePoint’s REST API.
For more details, please refer:
SUMMARIZECOLUMNS function (DAX).
Environment variables in Power Automate.
Export and email a report with Power Automate - Power BI | Microsoft Learn
Export paginated reports with Power Automate - Power BI | Microsoft Learn
SharePoint - Connectors | Microsoft Learn
Store and manage values in variables in Power Automate - Power Automate | Microsoft Learn
Advocate VHi v-hashadapu ,
Thanks for the reply and giving an overview on how to proceed for the next set of steps. So today I was working on a sub-task which is how to pass the column values by taking input from user. For that I did the following:
// Created a table with two columns one as a display name and one which is DAX friendly
UserSelections =
DATATABLE(
"DisplayName", STRING,
"DaxToken", STRING,
{
{ "Domain", "'Sheet1'[Domain]" },
{ "Location", "'Sheet1'[Location]" },
{ "Value", "'Sheet1'[Value]" }
}
)
// Then created a measure which gets the selections from the slicer and which in turn is passed on to the Power Automate visual
Selected Export Columns =
VAR IsAnyFilter =
ISFILTERED(UserSelections[DisplayName])
|| ISCROSSFILTERED(UserSelections[DisplayName])
VAR cnt = COUNTROWS( VALUES(UserSelections[DaxToken]) )
VAR allCnt = COUNTROWS( ALL(UserSelections[DaxToken]) )
RETURN
IF (
NOT IsAnyFilter || cnt = allCnt,
"", -- empty string → flow fallback
CONCATENATEX( VALUES(UserSelections[DaxToken]),
UserSelections[DaxToken],
"," )
)It looks like: (Had added a card visual just to check the selections are proper)
In the Power Automate flow I simply created a variable (empty) and then simply appended the values
And simply used this variable in the 'Run a query against dataset (2)' in the do-until loop (wherein previously I had hard-coded all the columns)
So I have solved the first part (instead of hard-coding all the columns there is some flexibility to the end user which column they would want in their export)
Now I need to check the download part. I hope this breakdown is useful for others (who might have a similar requirement)
If possible any article that might help (apart from the documentation that you shared earlier that too can help) and if possible a simpe example to get it done (like how I just explained that will be quite useful)
Gabry , Poojara_D12 if you have any inputs please do share them.
Regards,
Sidhant.
- v-hashadapu
Hi Sidhant , Thank you for reaching out to the Microsoft Community Forum.
You’ve done an excellent job solving the dynamic column selection challenge, that’s typically the most complex part of this type of export workflow. Your approach using a mapping table and a DAX measure to generate a query ready column string is exactly how to give end users flexibility while maintaining control over the dataset query structure. By passing that string to Power Automate and dynamically constructing the DAX query inside the flow, you’ve effectively enabled runtime customization without breaking Power BI’s fixed schema requirements.
A practical way to think about your setup is to create a mapping table with display-friendly and DAX column names, let users choose fields through a slicer, use a DAX measure to concatenate the chosen technical names into a string and then pass that string to Power Automate as input. Inside the flow, you can split the string and use it to dynamically build the DAX query for export. This gives users complete flexibility in what they export, while keeping your model design and security consistent.
For the download step, continue this pattern by letting Power Automate handle the export logic, use the Run a query against a dataset action to retrieve only the selected columns (plus your Index field for batching) and export the results to SharePoint or OneDrive. Once the export completes, apply user specific folder permissions so each user can access only their own files.
For documentation and practical walk-throughs, there isn’t a single official article that covers this exact “dynamic column export via Power Automate” pattern end to end but I think the following references along with what I already shared together support each part of your solution:
Power BI - Connectors | Microsoft Learn
Export and email a report with Power Automate - Power BI | Microsoft Learn
Manage list item and file permissions with Power Automate | Microsoft Learn
- Sidhant
Hi v-hashadapu
Thanks for acknowledging my response appreciate that. I had few more queries since previously I clarified as this solution is to be more end-user centric wherein the Power BI report is an embedded report (assume it like when we install any software like VS code, Power BI Desktop on click of the download button it simply gets downloaded on your local super easy) similarly let's say the user wants to download a file (present on SharePoint, OneDrive) they can do that using 'Create File' action but the pre-requisiste here is we need a standard Gateway to be installed and configured
Power Automate Flow to save file to local drive
I came across this article but here I had few queries: Herein we need to have the gateway installed and then set-up but since we have information of end users will this solution work (because in the article they first setuped the gateway on the system and then built the flow).
Then next question I had right now for the export I have just worked on data from a single table, but there can also be a scenario wherein we don't have just one table but data is spread across multiple tables like a single fact and multiple dimensions so in that case how should one be moving ahead.
Also, in terms of security let's say RLS is implemented (assume we have 3 end-users: Jim,Ryan and we have tables: orders, sales, people; So based on RLS Jim is only allowed to access data from orders whereas Ryan from orders and sales), so in that case will be RLS be maintained (that needs to be defined at the workspace level?). Because I cam across one comment wherein they had used a similar approach but the issue was RLS was getting overridenThe other thing is there any hard limit on the number of rows that can be retrieved right now I was able to export 1M rows and for larger size I was thinking of increasing the batch size (from 25,000 to 1,00,000) but in the above image it points that it does not work for 2M rows so for the respective action 'Run a query against a dataset' do we have any limit on what amount it can retrieve.
For folder/item level access you have suggested of using the 'Grant access to an item/folder' (under Manage list item) wherein once the file is created in SharePoint we apply this but my question was how do we create end -user folders (like creating folders based on the client name/end user name and then storing them under that folder), for that we need to capture some information (with respect to end users) but since they won't be part of any entra id how to manage this part. Once this is done then when we use Grant access therein as well need to specify the email recipients so this how to specify the same)
Lastly in the my current flow I also made few tweaks to add value level filter (earlier I had given an option to select which columns should be exported)
And I did observe duplication issue in columns (when we just selected one which was due to Apply to each loop) so I modified it with the following expression
Now the issue that I observed was the there was even header repetition in the csv what I mean to say is this:
// Here are the expression that were being used for the filter values and the update to avoid duplication in column selection //DomainCsv @{if(equals(length(variables('DomainsArr')), 0),'',concat('"', join(variables('DomainsArr'), '","'), '"'))} //DomainFilterClause @{if(equals(outputs('DomainCsv'), ''),'',concat(' && ''Sheet1''[Domain] IN {', outputs('DomainCsv'), '}'))} [This speccified the filter condition format wherein we simply specify the table_name[columnname] and then use IN operator & the values are retreieved from the previous action] //FilterArray: @coalesce(triggerBody()?['entity']?['Power BI values'], array()) @and(not(empty(item()?['Selected Export Columns'])), not(equals(item()?['Selected Export Columns'], ''))) //ColumnList : To avoid duplication (skipped the Apply to each loop) @{trim(string(outputs('SelectedColumnRow')?['Selected Export Columns']))}The earlier logic used to keep appending the same value (due to the loop)
The other thing in Filter value slicer if no selection was been done then I simply passed all the values else only pass the selections as you can see in the below image
So if you help me with these queries it will help a lot.
Also if you Gabry , Poojara_D12 have any inputs on this do let me know.
Regards,
Sidhant.- Sidhant
Hi 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 headersSo 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.