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
- Sidhant
A 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.