Unanswered

Saving Power BI query against a dataset in Excel file stored in SharePoint

(0) Share

Report

Posted on by ViktorPavlov

Hi everyone, I am trying to set up a flow that every 24 hours is running a query against a dataset and is trying to save the output in an Excel file. Currently I for some reason can't save it in Excel I am only able to do so in a CSV file. Can I get some pointers?

Categories:

Building flows

I have the same question (0)

All responses (10)

Answers (0)

lbendlin 8,474 Super User 2025 Season 2 on at

Like (0)

Report

Re: Saving Power BI query against a dataset in Excel file stored in SharePoint

What is the error message?

Does your Excel file exist and have a table defined?

Was this reply helpful? Yes No
ViktorPavlov 10 on at

Like (0)

Report

Re: Saving Power BI query against a dataset in Excel file stored in SharePoint

Hi, thank you for your reply!

I have tried two approaches.

One with an already existing file Excel file in SharePoint and me trying to add the data as rows. It's my preferred option, but does not work, as intended. When the flow is running it only creates empty rows without the data from the query.

My second option was to make the flow create a new Excel file in SharePoint every time, but I could not seem to get that right somehow, I still don't know why.

Keep in mind that I have to use SharePoint to store the files, as a technical constraint.

Was this reply helpful? Yes No
lbendlin 8,474 Super User 2025 Season 2 on at

Like (0)

Report

Re: Saving Power BI query against a dataset in Excel file stored in SharePoint

Did you correctly map the fields from the FirstTableRows part of your query result?

Was this reply helpful? Yes No
ViktorPavlov 10 on at

Like (0)

Report

Re: Saving Power BI query against a dataset in Excel file stored in SharePoint

I am new to Power Automate, can you give me an example with a flow how I would do that?

Was this reply helpful? Yes No
lbendlin 8,474 Super User 2025 Season 2 on at

Like (0)

Report

Re: Saving Power BI query against a dataset in Excel file stored in SharePoint

In your case you wouldn't create a HTML table but a "Apply to each" loop that goes through all rows of the first table and loads that data into Excel.

Was this reply helpful? Yes No
ViktorPavlov 10 on at

Like (0)

Report

Re: Saving Power BI query against a dataset in Excel file stored in SharePoint

I am trying to do it with apply to each and add to excel records, but when I do so it only creates empty rows inside the Excel file. Can you give me an example of that context, as you also mentioned it?

Was this reply helpful? Yes No
ViktorPavlov 10 on at

Like (0)

Report

Re: Saving Power BI query against a dataset in Excel file stored in SharePoint

Hi there! I still can’t get it correct. Can you please help out more. I still don’t get why is it only adding new empty rows. How do I need to map the fields to the existing excel file

Was this reply helpful? Yes No
lbendlin 8,474 Super User 2025 Season 2 on at

Like (0)

Report

Re: Saving Power BI query against a dataset in Excel file stored in SharePoint

please post your sample Excel file (with defined table)

Was this reply helpful? Yes No
ViktorPavlov 10 on at

Like (0)

Report

Re: Saving Power BI query against a dataset in Excel file stored in SharePoint

Here is how my file looks like. Super basic just 6 columns, with a defined table

Example.xlsx

Was this reply helpful? Yes No
lbendlin 8,474 Super User 2025 Season 2 on at

Like (0)

Report
Re: Saving Power BI query against a dataset in Excel file stored in SharePoint

Here is the code that works for me

"actions": { "Run_a_query_against_a_dataset": { "type": "OpenApiConnection", "inputs": { "parameters": { "groupid": "myworkspace", "datasetid": "xxx70223-cd73-484e-afc1-27b30472a873", "specification/query": "EVALUATE TOPN(5,financials)", "specification/serializerSettings/includeNulls": true }, "host": { "apiId": "/providers/Microsoft.PowerApps/apis/shared_powerbi", "connection": "shared_powerbi", "operationId": "ExecuteDatasetQuery" } } }, "For_each": { "type": "Foreach", "foreach": "@outputs('Run_a_query_against_a_dataset')?['body/firstTableRows']", "actions": { "Add_a_row_into_a_table": { "type": "OpenApiConnection", "inputs": { "parameters": { "source": "sites/xxx.sharepoint.com,xxx57514-8505-4842-abb4-6d66a545eca9,xxxd1e9f-8269-4a25-94d6-88918274655b", "drive": "xxxHXVWQWFQkirtG1mpUXsqZ8enW9pgiVKlNaIkYJ0ZVv4GztWKEJPSJr9b-o2fG_l", "file": "xxx722ILYLA73JE35X3FCI6XG6AKKU3T74", "table": "xxx491055-0BA9-48F8-AAB0-AE86297CA8AA}", "item/Column 1": "@{items('For_each')?['[Country]']}" }, "host": { "apiId": "/providers/Microsoft.PowerApps/apis/shared_excelonlinebusiness", "connection": "shared_excelonlinebusiness", "operationId": "AddRowV2" } }, "metadata": { "xxx722ILYLA73JE35X3FCI6XG6AKKU3T74": "/Example.xlsx", "tableId": "xxx491055-0BA9-48F8-AAB0-AE86297CA8AA}" } } }, "runAfter": { "Run_a_query_against_a_dataset": [ "SUCCEEDED" ] } }, }

Was this reply helpful? Yes No