Forum Discussion
Post ProdigyAutomate table extract from report & upload file to share point
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
- 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)
- 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']
- 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
Super UserYour 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
- 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)
- 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']
- 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
Justas4478andrewsommer 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?
- 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.