Have you ever wondered how many times your Power BI dataset has been refreshed? And if you are using Power BI Pro, you are limited to 8 refreshes a day.
This week’s blog post I am going to demonstrate how you can use the Power BI API to view all your data refreshes for a particular dataset.
If you need a recap here is a link to my previous blog post Using the Power BI API with PowerShell scripts, in which I demonstrated how to refresh your Power BI dataset using PowerShell and the Power BI API.
If you are looking for information on how to get the GroupID, DatasetID & ClientID, please refer to my previous blog post Using the Power BI API with PowerShell scripts
Example
For this particular example, I am busy working on a presentation for SQL Saturday Sydney (which I would recommend going to, if you are in Sydney on 07 July 2018)
Part of what I am doing is refreshing the data using an Azure Function, Microsoft Flow and Power Apps (Which will be another blog post), and I wanted to know how many times I have refreshed the dataset.
I need to know if I am going to hit my 8 times a day refresh limit when testing! This PowerShell Script and Power BI report came in handy as it allowed me to know if I was going to exceed my refresh limit.
As with all examples you can download the PowerShell file here: PUBLIC – Power BI Dataset Refresh with Output to CSV.ps1
NOTE: Before starting you will have to have updated the PowerShell script where YOU have put in your GroupID, DatasetID and ClientID
Updating PowerShell Script for Data Refreshes
The only actual change that I made was to put in the location of where I wanted to store my CSV File.
This is on line 93
Once I had updated the above to a folder and file location I ran the script.
Below is what it looked like once I opened the CSV File
It is interesting to see that it also tells you what Refresh Type was done. In this instance I was doing it via an Azure Function which was making the API call.
Power BI Report with output from CSV
What I then did was I saved the CSV file into my OneDrive for Business folder location. This enabled me to load the CSV file into an existing Power BI report.
As you can see below, I now know how many refreshes I have completed on my dataset.
Wrapping Up
I have demonstrated how I leveraged the Power BI APIs to download how many refreshes my dataset has had.
And then being able to report on this allowed me to be able to know how many refreshes I had left.
Once again here is the PowerShell Script that I used: PUBLIC – Power BI Dataset Refresh with Output to CSV.ps1
If there are any comments or suggestions, please leave them in the comments below.
Bonus – If you are interested in what the PowerShell Script is doing?
Below are additional details on how I got the data down from the Power BI API call
-
Lines 82 – 83
- This is where I am creating the URI and sending it to the Power BI API
-
Line 85
- This is where I am getting the output from the API Call and converting it to Json
-
Line 87
- I am then converting it back from Json. This might seem a bit counterintuitive, but my logic is when converting it from Json I am then able to Query the Json.
-
Line 89
- Because I am converting it from Json, I am now able to select specific Json properties.
- On line 89 I am then selecting the “value” property, which is the data that is returned from the Power BI API call.
-
Line 91
- I am selected the specific items from the “value” property
- This has all the details that I require for my data refreshes.
-
Line 93
- I am then exporting the data to a CSV file.