Forum Discussion
Advocate VManaging and Exporting High-Volume Datasets (~1M Records) in Power BI
Advocate VHi everyone,
So I was trying one last method which was using Power Automate and using the 'Run a Query against a Dataset' action wherein we provide the DAX and get the required result. So I have constructed the flow:
-> Wherein we first define few things the min value (here its 1) the batch size (25,000) and how many times the loop runs (iteration count).
The flow looks like:
The flow fails at the second action 'Run a query against a dataset 2' . Not sure what is incorrect in the query provided in the run a query dataset 2 action. I even tried using a compose before the datset 2 action and then pass the outputs of compose in it but no luck.
// The DAX Query incorporated in the action which is failing)
DEFINE
VAR __DS0FilterTable = FILTER(KEEPFILTERS(VALUES('Sheet1'[Index])),and('Sheet1'[Index] >=@{variables('MinRows')}, 'Sheet1'[Index]) <=@{variables('IncrementRows')} )
VAR __DS0Core =
CALCULATETABLE(
SUMMARIZECOLUMNS(
'Sheet1'[Domain],
'Sheet1'[Location],
'Sheet1'[Value]),
__DS0FilterTable
)
EVALUATE
__DS0Core
ORDER BY
'Sheet1'[Index]o/p: Error for Run a query against dataset 2 action
{
"statusCode": 400,
"headers": {
"Cache-Control": "no-store, must-revalidate, no-cache",
"Pragma": "no-cache",
"Strict-Transport-Security": "max-age=31536000; includeSubDomains",
"X-Frame-Options": "deny",
"X-Content-Type-Options": "nosniff",
"RequestId": "abedf306-b1b6-4f82-862a-8258bc6a4847",
"Access-Control-Expose-Headers": "RequestId",
"x-ms-environment-id": "default-1f4beacd-b7aa-49b2-aaa1-b8525cb257e0",
"x-ms-tenant-id": "1f4beacd-b7aa-49b2-aaa1-b8525cb257e0",
"x-ms-dlp-re": "-|-",
"x-ms-dlp-gu": "-|-",
"x-ms-dlp-ef": "-|-/-|-|-",
"x-ms-mip-sl": "-|-|-|-",
"x-ms-au-creator-id": "77c12444-2bba-4be3-81c8-d55a183a2a28",
"Timing-Allow-Origin": "*",
"x-ms-apihub-cached-response": "true",
"x-ms-apihub-obo": "false",
"Date": "Wed, 22 Oct 2025 10:54:24 GMT",
"Content-Length": "555",
"Content-Type": "application/json"
},
"body": {
"error": {
"code": "DatasetExecuteQueriesError",
"pbi.error": {
"code": "DatasetExecuteQueriesError",
"parameters": {
},
"details": [
{
"code": "DetailsMessage",
"detail": {
"type": 1,
"value": "Query (17, 3) A single value for column '\u003Coii\u003EIndex\u003C/oii\u003E' in table '\u003Coii\u003ESheet1\u003C/oii\u003E' cannot be determined. This can happen when a measure or function formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result."
}
},
{
"code": "AnalysisServicesErrorCode",
"detail": {
"type": 1,
"value": "3241803789"
}
}
]
}
}
}
}This is my table preview:
Note: I just had created a simple index column within Power BI desktop (starting from 1) for ordering the data and then published the report.
Can you let me know what is missing v-hashadapu , Gabry , Poojara_D12.
Regards,
Sidhant
Advocate VA quick update so I was able to resolve the issue made few tweaks in the query: one was use Filter with ALL method on the Index column instead of KeepFilters and in Summarize column also included the Index column.
{And the other change was earlier may be I had used Set variable to increment the three variable values MinRows, IncrementRows and counter varaiable} instead I used Increment variable action}
//Dax Query (Run a query against dataset 2 action)
DEFINE
VAR __DS0FilterTable =
FILTER(
ALL('Sheet1'[Index]),
'Sheet1'[Index] >= 1
&& 'Sheet1'[Index] <= 25000
)
VAR __DS0Core =
CALCULATETABLE(
SUMMARIZECOLUMNS(
'Sheet1'[Index],
'Sheet1'[Domain],
'Sheet1'[Location],
'Sheet1'[Value]
),
__DS0FilterTable
)
EVALUATE
__DS0Core
ORDER BY
'Sheet1'[Index]
So I had some follow up-queries which are as follows:
1. Currently what I have done is specified what columns that need to be considered for export (i.e. in Run a query against dataset 2 action you can see in SUMMARIZECOLUMNS the three columns. Now I wanted to understand is there a way that we let end users select the columns that they want (will keep the Index column as that is needed to avoid the error) and then pass that in the query.
2. This flow will be mostly triggered by end users (from the Power BI report) so right now I am creating the CSV file in a Sharepoint Site at a specific location now when we think from the end user perspective assume this being used by multiple users so in that case can we do:
i) The end user will specify the location where the file should be stored and we save that at respective location.
ii) (if i) is not viable) then if we are using SharePoint/One Drive so how we can segregate each users files (Assume I have Tim, John my end users -> We are storing the files in a SharePoint site in a folder: Now both users should not be able to each others data (Tim should not see John's files and John cant see Tim's file), how we can implement this security aspect.
iii) In Create file (SharePoint action; we specify 4 things: Site address -> Folder Path -> File Name -> Content), so right now I have specified everything explicitly (kind of hard-code) by any chance can we take the Site address dynamically (like end user tells which SharePoint site to use).
I am trying to build a solution which is more end-user friendly (they won't be interested in the underlying details), so if anyone can share their inputs do let me know.
Also v-hashadapu , Gabry and Poojara_D12 if you have anything to add do share that.
Regards,
Sidhant.
v-hashadapuCommunity Support
Hi 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
- Sidhant
Hi 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