How to set up automatic data dump from Power BI visual to excel

[siddrow]

Hi

I don't want to create a Power Automate button within my dashboard, I just want to set up a flow where the data automatically dumps out from my Power BI dashboard into an excel file (which will be saved in a Sharepoint folder) daily at 7am. I don't want one separate file saved down for each day, just the one file to be constantly updated daily. The data also needs to be in an excel format, not PDF. I've doing some googling and have tried to follow a few videos with no success.

Please help 😁

[lbendlin]

Needs more details.  What is your Power BI capacity - shared or Premium?  Do you have access to the dataset to run DAX queries?

[siddrow]

Hi

Thanks for your quick reply. The Power BI dashboard was created by myself, so I have full access to the dataset. I have premium and have also downloaded Power BI report builder. Just not sure the best way to go about this or how to do it really. 

[lbendlin]

Use the standard Power BI Query action or create your own custom connector.

[siddrow]

Hi

I'm not familiar with this at all. This will be the first time using Power Automate. Will this allow me to set up a recurring data dump from my Power BI dashboard to an excel file? 

[lbendlin]

Yes. No dashboards will be harmed in the process. You can run queries against the dataset and store the result in an Excel file.

[siddrow]

thanks. I managed to find a youtube video to follow to create the action for run query against dataset, however it didn't result in it saving a CSV file down like it was supposed to. I followed this video step by step https://www.youtube.com/watch?v=wy1BE1TJ_jc but it saved a file down that wouldn't open. I also tried to copy the table code like she did, however that did not work either. I then tried to get the DAX code from performer anaylzer, however as the dataset is so big (5K lines+) it would create a new code as I scrolled down my table visual. 

Are you able to give me a step by step of how to set up the action please? 

[lbendlin]

- Open the pbix file of the report containing your visual

- go to the page with the visual you want to extract from

- Start Performance Analyzer, Start Recording 

- copy the query for your visual

- examine the query in DAX Studio or your favorite text editor, clean it up to remove all the sorting and rollups etc

- paste the query code in the flow step.  Make sure to set "Nulls included" to "yes"  (very important)

- after you run your flow query step you need to use a JSON parser flow step to read the results of the query "row"  by "row" (apply to each)  and then write the results to Excel or CSV.  The raw JSON is probably not useful for you.

This is where the guidance has to stop as the last step (and the following steps) will depend on the query results.

[siddrow]

Thanks for that, I will give it a try. In regards to the DAX code, how does that work if my table in Power BI is constantly updating? Wouldn't that mean I would have to get a new DAX code every time? Just to give you an idea of my dataset in PBI - it is pulling info from multiple MS Lists which are all project framework and are constantly updated by multiple project teams daily, so the info is always changing and every now and then a new project gets added which means another 180+ lines are added to the dataset.

Is there a code that I can use for the headers only which will pull all the data is those columns regardless of how many lines? I hope this makes sense.

[lbendlin]

A DAX query is like any other query language - it doesn't really care about the data.  All it cares about is that the syntax is correct and that results can be returned.  Whenever your dataset changes your DAX query will then return updated results.

If you want rollups or groupings then by all means craft your DAX query accordingly.

If you haven't noticed yet - your Power BI visual is an unwitting participant in the crime. All you do is steal its query.  Technically you can write your own query without ever harming a visual.

[siddrow]

okay, so technically I wouldn't need to scroll to pick up all lines and get the DAX codes for each scroll section right? I could just run the performance analyzer and grab the first DAX code that comes up?

For the step below, I have never used JSON. Is this easy to do?

- after you run your flow query step you need to use a JSON parser flow step to read the results of the query "row"  by "row" (apply to each)  and then write the results to Excel or CSV.  The raw JSON is probably not useful for you.

[siddrow]

Hi

I have followed your steps but got stuck at scheme - nothing shows up when I click into "generate from sample". I also tried to select "row" in the "select and output from previous steps" but it automatically changed to "first table rows". 

I've also read in other posts that I can get the JSON from the "run query against dataset" step, however there's nothing that looks like JSON format in there for me to use, only my DAX code.

Any help on this is much appreciated 🙂

[lbendlin]

The JSON will be the result of your query, ie AFTER you run the flow step. You already got halfway there with your "Row" variable. You need to expand that a bit more.

To give you a general idea here is the format of the returned JSON

{"results":[{"tables":[{"rows":[...]}]}]}

Meaning you have to drill down a bit before you get to the actual data.

You can practice with DAX Studio too if you like.

[siddrow]

Thanks for your help. Ended up finding an easier way to do it

- run a query against a dataset action

- create csv table

- update file (used this so it was updating the 1 file, instead of saving down a new file each time it ran).