Dataset Refresh Report with Power BI Rest API and Power Automate | Track all your refresh Status

for this power bi report I want to get this refresh history data using power bi rest API and load it into a streaming data set to create refresh history report in our power bi dataset refresh settings we have one option to get refresh failure notification email but there is no such option to get a refresh success notification one simple workaround to get this notification is to subscribe to the report here we get option to schedule the subscription email and we can select this option mentioned as after data refresh this way you will get the email notification but it is limited to Once in a day there are a few more options available where we can customize our email subject and message for example I can mention subject as refresh success report and message as this data set is refreshed successfully click on Save and now if you will refresh your data set you will get the email notification but as said this is limited to only one email a day alright to know about power bi rest apis we need to open this Microsoft documentation page where we will get all information about rest apis since we are looking for data set refresh history I will click on this option kid refresh history and group group is nothing but the power bi workspaces here is the API URL and I can click on try to test if this works or not letter I will register my service principal to run these apis to get this data set ID and group ID I can go to my power bi workspace and select my data set so here is my PBI demo workspace and I will select this data set now from the URL address bar I can copy the group ID and data set ID after that I will click on run button and we can see the status code 200 and below that we will see the refresh history output in Json format you can verify the start time and time or the refresh status from your refresh history page now I need to register an app so that I can use a service principle to use these apis to push data inside my data set but before that I need to create a push data set for that I will create a power bi streaming data set inside my workspace click on new and select streaming data set then select API and click on next I can name it as my refresh history and I will Define my column names for that I can go back to the sample result page and copy all these column names and its data type this will basically create a blank data set inside our workspace where we will push data using our rest API one important thing to remember here is we have to enable this option so that it will store the historical data of refresh history click on create to create this data set here is my streaming data set and let me create a blank tabular visual on top of it which we are going to view as a report of refresh history so I have created this table Visual and I can name this report as report history track I will save this report I know there is no any data at present and we need to use our rest API to feed data into it so let's go ahead and open this link to start registering our app here I need to choose my embedding solution if my app is for customers outside organization I will Select First Option or if it is for internal I will use the second option first of all we need to sign into Power bi and then keep an appropriate name for the app for this demo I am using this name as my refresh history I need to also mention the home URL and for this demo I will use the same documentation URL now I need to provide different type of access to this app you can go back to the API documentation page and you will see here the required scope you can provide those access I will select all of these access type for this app scroll down and click on register it will create the client ID and the secret which will be used inside your applications to run you can also go to the Azure portal inside Azure active directory and you will find this app registration option here you will see you registered apps or you could also register your app from this space one important thing to do here is you need to go inside API permissions and Grant admin consent if required if your API need tenant level access for read write you need to add these permissions from the space to run the API I am going to use power automate you can use either Powershell command curl or any other method to run these apis based on your requirements you will get both the commands from the streaming dataset API info option I am selecting this manually trigger a flow option but if required you can create an automated flow or schedule flow as well click on next step and search for HTTP because at this place I will use my API details which will run on the trigger let's fill all these details first of all select a method which in our case is a get method next is to add the API URL which we can get it from the documentation page which we just tested it also has the group ID and the data set ID of my actual data set the authentication type as Azure ad Authentication click on this Advance option this is the place where I will enter my client ID tenant ID and other details of my service principle I can get all these details from here so I will copy one by one and paste it in my power automate flow in the audience option I will enter an URL analytics.windows.net power bi slash API save this step and add another step to the flow in this step we want to get the data from above API run so I will use Json to store the output for Content I can select body and to define the schema of this Json I can again go back to the API page where we tested the API and we can copy this result for this purpose click on generate schema and paste the copy result here because we need the same format after this again click on next step and now we will add our final step here this step will push this entire data to our streaming data set which we created so I search for Power bi and then I will select the option which says add rows to the power bi data set here I can select my PBI demo workspace where I created the streaming data set and we know the default table name is real-time data we know that our streaming data sets table is currently blank and it has only column names in it we created this schema for our streaming data set and same is visible here now I can map each of these fields with the previous step output of the Json file which will be filled by the API run as that you can see I have these fields from the previous step and I can map it one by one to my streaming data set table if you need other fields you need to Define in your push data set and then map it from here after this step we don't need any further workarounds so I will save my power automate flow these four steps are going to do all the magic as said you can also apply an automated flow or a schedule flow based on your requirements now it's time to trigger this flow and see if this is able to push data inside our streaming data set or not so I will click on run this flow and as that you can see my flow has run successfully let's go back to our power bi report and check the result so I will refresh the browser and here we go we can now see the refresh history inside this visual now if we have automated flow we can continuously track the refreshed history of my data set keep in mind that this is in another data set which is tracking the refresh history of my actual data set this solution is very useful and you can utilize power bi rest apis for various other requirements thanks for watching please subscribe to our channel for many such interesting topics in power bi post your feedbacks and suggestions in comment box foreign foreign [Music]

Channel: Power BI Helpline

Views: 15,925

Keywords: power bi, power bi tutorial, power bi desktop, power bi training, power bi dashboard, learn power bi, microsoft power bi, power bi desktop tutorial, dax, powerbi, power bi helpline, power bi refresh, power bi refresh data, power bi dataset refresh, power bi dataset, dataset refresh, power bi dataset refresh api, power bi rest api dataset refresh, refresh dataset, Power automate in Power BI, rest api power bi, Power automate

Id: hC7-PWIHMsY

Length: 8min 28sec (508 seconds)

Published: Sun Jul 23 2023