Automate table extract from report & upload file to share point

[Justas4478]

Hi, I have this matrix visual table in my report that needs to be extracted every day.

Report is published in power bi service and after extract excel file would be placed in dedicated documents folder on sharepoint.
I would need solution that does not require to keep my laptop on to function.
Anyone knows of what solutions that I can use to archeve this?
If there is need for more detailed automation I want to create let me know and I will write it out.

[v-priyankata]

Hi @Justas4478 

Thank you for reaching out to the Microsoft Fabric Forum Community.

@andrewsommer @cengizhanarslan @luisoliveira89 Thanks for the inputs.

I hope the information provided by users was helpful. if your issue got sorted please accept the useful user reply as "accept as solution". If you still have questions, please don't hesitate to reach out to the community.

[Justas4478]

@v-priyankata @luisoliveira89 @cengizhanarslan @andrewsommer I tried to add some conditions to prevent flow from returning empty csv files or files that had old data if report refresh failed.
But I am getting this error and I am not sure what is wrong with it and how to fix it.

[andrewsommer]

Error could be from multiple different causes:

1. The Power BI query returned no rows, so the "first row" has no date field to extract
2. The date column is blank/null for the first record in your dataset
3. A previous action failed silently, so the value never got populated before being passed into the substring expression
4. Column name mismatch, the expression is referencing a column that doesn't exist or was renamed in the query

How to fix it

1. Add a null check before the substring call: if(empty(yourValue), '', substring(yourValue, 0, 10))

1. Add a condition action upstream to check whether the Power BI query actually returned data before proceeding
2. Check your Power BI query, run it independently to confirm it's returning rows with a populated date column
3. Use coalesce() to provide a fallback: substring(coalesce(yourValue, '1900-01-01'), 0, 10)

1. Inspect the action just before FirstRowDate in your run history, expand its output to see if the date field is actually null there

The root cause is almost certainly an empty or null result from your Power BI data, so I'd start by verifying the query returns data before the flow tries to process it.

[Justas4478]

@andrewsommer I found what was the problem.
column names in raw data when imported included table names.
I just had to naming in function and it solved the problem

[Justas4478]

@andrewsommer 
If I run without conditions and just extract to csv this is what it returns.

There is data and it does bring it back but as you see date column is not actualy just date.
I am first trying to count rows to see if it brings back any data.

Then it goes to 'are there any rows' condition.

Then it should convert first row of date column to date for further check. As you see in first picture csv file does not return just a date but this 2026-04-14T00:00:00.

 
Afterwards has compose to show what is yesterdays date 

The final condition is to check if date in extracted data is same as yesterdays date.

[luisoliveira89]

Another option, if you have a Fabric subscription, is to use Notebooks and PySpark. You can leverage the Power BI REST API to export content.

[Justas4478]

@luisoliveira89 I changed flow to create csv file and it works now. It is not ideal since size is double of xlsx but files themselves are not that big

[cengizhanarslan]

Option 1) Power Automate + Power BI Export API 

Step 1) Create a Power Automate flow with a Recurrence trigger set to daily at your preferred time.

Step 2) Add the Power BI Export To File for Reports action — this calls the Power BI REST API to export your report page as an XLSX or PDF file directly from the Service.

Step 3) Add a SharePoint Create File action and point it to your dedicated documents folder. Use the file content from the previous step as the file body.

This runs entirely in the cloud with no local machine dependency. The export respects the current data in your semantic model at the time the flow runs.

Option 2) Fabric Pipeline + Notebook 

If your workspace is on Fabric capacity, you can use a Fabric Data Pipeline with a scheduled trigger that runs a Notebook extracting data directly from your semantic model via XMLA or DAX query, formats it, and writes the output file to SharePoint via the SharePoint connector or Microsoft Graph API.

This is more flexible for complex transformations but requires more setup.

[Justas4478]

@cengizhanarslan If I am correct step two wont work for me since page extract does not include rows that are not visible.

[cengizhanarslan]

You are right, I did not see that your visual need scrolling down. So insted of exporting the report page you could execute the dax query behing it and use it to export into an Excel file.

[andrewsommer · Accepted solution]

Your best bet is to use Power Automate to query the dataset and then save the output in SharePoint. 

1. Create Scheduled Flow

• In Power Automate
• Use “Recurrence” trigger
• Frequency: Daily
• Time: your desired refresh time

2. Query Power BI Dataset

Use the Power BI connector:

• Action: “Run a query against a dataset”
• Workspace: your workspace
• Dataset: your semantic model
• Look out for:
• Always return a flat table (no nested structures)

• Avoid very large result sets (API limits ~100k rows per call depending on payload size)
  • Use measures vs raw columns when possible (performance + consistency)

3. Parse and Shape Output

The output comes back as JSON.

Add:

• “Parse JSON” action
• Use sample output from prior run to generate schema

Then:

• “Create CSV table” (simplest)
• Input: body('Parse_JSON')?['results'][0]['tables'][0]['rows']

4. Save to SharePoint

Use SharePoint connector:

• Action: “Create file”
• Site Address: your SharePoint site
• Folder Path: document library location
• File Name:

dataset_export_@{formatDateTime(utcNow(),'yyyy-MM-dd')}.csv

• File Content: output of Create CSV table

[Justas4478]

@andrewsommer I am trying to create flow.
I manage to get some of it working.

Create file fails to give name of file how I want it so it is just test name at the moment. 
But as well I get this error when it tries to add rows to a table in xlsx file.

I am doing this for the first time so I am not sure did i add somethign uneceserry or am I missing something.

[Justas4478]

@andrewsommer  Data in the visual is from multiple tables.

Some of the values are from measures that are calculated in the report.
So I am not sure if that would capture that information?

[andrewsommer]

We do this all the time with no issues.  In Power BI desktop use the performance analyzer to get the DAX query behind your visual.  You could also use DAX Studio to get to a query.  If your visual has any filters applied through slicers you should see them in the query as a TREATAS or filter arguments and any visual-level filters will be embedded in the SUMMARIZECOLUMNS

[Justas4478]

@andrewsommer At the moment when I extract data manualy from the visual it does have line at the end of data called: Filters applied.
Where do you host your power automate flows?
Do you have dedicated hardware or you use VM in Azure?