cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
pkasa Frequent Visitor
Frequent Visitor

PowerBI Service: List of Power BI Reports/Data set's with Failed Refreshes

Hello there, Is there a way that we can get an daily extract of the list of Report names/Dataset's with failed Refrsehes from PowerBI Service? 

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: PowerBI Service: List of Power BI Reports/Data set's with Failed Refreshes

Hi @pkasa ,

 

We have located the issue in our query, please try to use the following query

 

let
    Source = Json.Document(Web.Contents("https://api.powerbi.com/v1.0/myorg/groups", [Headers=[Authorization="Bearer " & TokenParameter]]))[value],
    test = Table.FromList(Source,Record.FieldValues,{"id","isReadOnly","isOnDedicatedCapacity","name"}),
    #"Removed Other Columns" = Table.SelectColumns(test,{"id", "name"}),
    AppWorkSpace = Table.AddColumn(#"Removed Other Columns","Data",each let id = [id] in let
    Source = Json.Document(Web.Contents("https://api.powerbi.com/v1.0/myorg/groups/",[RelativePath= id &"/datasets", Headers=[Authorization="Bearer " & TokenParameter]]))[value],
    test2 = Table.FromList(Source,Record.FieldValues,{"datasetid","datasetname","addRowsAPIEnabled","configuredBy",
      "isRefreshable",
      "isEffectiveIdentityRequired",
      "isEffectiveIdentityRolesRequired",
      "isOnPremGatewayRequired",
      "targetStorageMode"}),
    #"Removed Columns" = Table.RemoveColumns(test2,{"addRowsAPIEnabled", "isEffectiveIdentityRequired", "isEffectiveIdentityRolesRequired", "targetStorageMode"}),
    #"Filtered Rows" = Table.SelectRows(#"Removed Columns", each ([isRefreshable] = true)),
    #"Added Custom" = Table.AddColumn(#"Filtered Rows", "Refresh History", each Json.Document(Web.Contents("https://api.powerbi.com/v1.0/myorg/groups/",[RelativePath=id &"/datasets/" & [datasetid] & "/refreshes",Headers=[Authorization="Bearer " & TokenParameter]]))[value]),
    #"Expanded Refresh History" = Table.ExpandListColumn(#"Added Custom", "Refresh History"),
    #"Expanded Refresh History1" = Table.ExpandRecordColumn(#"Expanded Refresh History", "Refresh History", {"datasetid", "refreshType", "startTime", "endTime", "status", "requestId"}, {"Refresh History.id", "Refresh History.refreshType", "Refresh History.startTime", "Refresh History.endTime", "Refresh History.status", "Refresh History.requestId"}),
    #"Filtered Rows1" = Table.SelectRows(#"Expanded Refresh History1", each ([Refresh History.status] = "Failed")),
    #"Reordered Columns" = Table.ReorderColumns(#"Filtered Rows1",{"datasetid", "datasetname", "Refresh History.status", "Refresh History.startTime", "Refresh History.endTime", "configuredBy", "isRefreshable", "isOnPremGatewayRequired", "Refresh History.id", "Refresh History.refreshType", "Refresh History.requestId"})
in
    #"Reordered Columns"),
    MyWorkSpace = Table.AddColumn(#table({"id","name"},{{"Null","My_Workspcae"}}),"Data",each let
    Source = Json.Document(Web.Contents("https://api.powerbi.com/v1.0/myorg/datasets", [Headers=[Authorization="Bearer " & TokenParameter]]))[value],
    test = Table.FromList(Source,Record.FieldValues,{"datasetid","datasetname","addRowsAPIEnabled","configuredBy",
      "isRefreshable",
      "isEffectiveIdentityRequired",
      "isEffectiveIdentityRolesRequired",
      "isOnPremGatewayRequired",
      "targetStorageMode"}),
    #"Removed Columns" = Table.RemoveColumns(test,{"addRowsAPIEnabled", "isEffectiveIdentityRequired", "isEffectiveIdentityRolesRequired", "targetStorageMode"}),
    #"Filtered Rows" = Table.SelectRows(#"Removed Columns", each ([isRefreshable] = true)),
    #"Added Custom" = Table.AddColumn(#"Filtered Rows", "Refresh History", each Json.Document(Web.Contents("https://api.powerbi.com/v1.0/myorg/datasets/",[RelativePath= [datasetid] & "/refreshes",Headers=[Authorization="Bearer " & TokenParameter]]))[value]),
    #"Expanded Refresh History" = Table.ExpandListColumn(#"Added Custom", "Refresh History"),
    #"Expanded Refresh History1" = Table.ExpandRecordColumn(#"Expanded Refresh History", "Refresh History", {"datasetid", "refreshType", "startTime", "endTime", "status", "requestId"}, {"Refresh History.id", "Refresh History.refreshType", "Refresh History.startTime", "Refresh History.endTime", "Refresh History.status", "Refresh History.requestId"}),
    #"Filtered Rows1" = Table.SelectRows(#"Expanded Refresh History1", each ([Refresh History.status] = "Failed")),
    #"Reordered Columns" = Table.ReorderColumns(#"Filtered Rows1",{"datasetid", "datasetname", "Refresh History.status", "Refresh History.startTime", "Refresh History.endTime", "configuredBy", "isRefreshable", "isOnPremGatewayRequired", "Refresh History.id", "Refresh History.refreshType", "Refresh History.requestId"})
in
    #"Reordered Columns"),
    ALlData = Table.Combine({AppWorkSpace,MyWorkSpace}),
    #"Expanded Data" = Table.ExpandTableColumn(ALlData, "Data", {"datasetid", "datasetname", "Refresh History.status", "Refresh History.startTime", "Refresh History.endTime", "configuredBy", "isRefreshable", "isOnPremGatewayRequired", "Refresh History.id", "Refresh History.refreshType", "Refresh History.requestId"}, {"Data.datasetid", "Data.datasetname", "Data.Refresh History.status", "Data.Refresh History.startTime", "Data.Refresh History.endTime", "Data.configuredBy", "Data.isRefreshable", "Data.isOnPremGatewayRequired", "Data.Refresh History.id", "Data.Refresh History.refreshType", "Data.Refresh History.requestId"})
in
    #"Expanded Data"

 

4.PNG5.PNG6.PNG

 

If you want to set Privacy Level as private, we need to spreate two kind of refresh history into two queries.

 

The Token paramater need to be updated before the access token expired, then you can refresh in Power BI Service.


Best regards,

 

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

13 REPLIES 13
jcullum Regular Visitor
Regular Visitor

Re: PowerBI Service: List of Power BI Reports/Data set's with Failed Refreshes

There is the Power BI REST API's.

 

https://docs.microsoft.com/en-us/rest/api/power-bi/

 

I haven't used them personally.  You need to read through the doco and understand the restrictions, but it should do what you need

Community Support Team
Community Support Team

Re: PowerBI Service: List of Power BI Reports/Data set's with Failed Refreshes

Hi @pkasa ,

 

We can also use power bi to show the refresh fail history by calling the REST API, 

 

let
    Source = Json.Document(Web.Contents("https://api.powerbi.com/v1.0/myorg/datasets", [Headers=[Authorization="Bearer PLACE_YOUR_TOKEN_HERE"]]))[value],
    test = Table.FromList(Source,Record.FieldValues,{"id","name","addRowsAPIEnabled","configuredBy","isRefreshable","isEffectiveIdentityRequired","isEffectiveIdentityRolesRequired","isOnPremGatewayRequired","targetStorageMode"}),
    #"Removed Columns" = Table.RemoveColumns(test,{"addRowsAPIEnabled", "isEffectiveIdentityRequired", "isEffectiveIdentityRolesRequired", "targetStorageMode"}),
    #"Filtered Rows" = Table.SelectRows(#"Removed Columns", each ([isRefreshable] = true)),
    #"Added Custom" = Table.AddColumn(#"Filtered Rows", "Refresh History", each Json.Document(Web.Contents("https://api.powerbi.com/v1.0/myorg/datasets/" & [id] & "/refreshes",[Headers=[Authorization="Bearer PLACE_YOUR_TOKEN_HERE"]]))[value]),
    #"Expanded Refresh History" = Table.ExpandListColumn(#"Added Custom", "Refresh History"),
    #"Expanded Refresh History1" = Table.ExpandRecordColumn(#"Expanded Refresh History", "Refresh History", {"id", "refreshType", "startTime", "endTime", "status", "requestId"}, {"Refresh History.id", "Refresh History.refreshType", "Refresh History.startTime", "Refresh History.endTime", "Refresh History.status", "Refresh History.requestId"}),
    #"Filtered Rows1" = Table.SelectRows(#"Expanded Refresh History1", each ([Refresh History.status] = "Failed")),
    #"Reordered Columns" = Table.ReorderColumns(#"Filtered Rows1",{"id", "name", "Refresh History.status", "Refresh History.startTime", "Refresh History.endTime", "configuredBy", "isRefreshable", "isOnPremGatewayRequired", "Refresh History.id", "Refresh History.refreshType", "Refresh History.requestId"})
in
    #"Reordered Columns"

 

Then you can shape it and create visuals, publish it to the service and keep it update every day.

 

We use GetDatasets API to get all the dataset id and information from My Workspace. Then get all the refresh history using the Get Refresh History API, If you want to get from other workspace, you can use the getrefreshhistoryingroup API and getdatasetsingroup API.

 

5.PNG

 


Best regards,

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted
pkasa Frequent Visitor
Frequent Visitor

Re: PowerBI Service: List of Power BI Reports/Data set's with Failed Refreshes

Thanks jcullum, we will give it a read.

pkasa Frequent Visitor
Frequent Visitor

Re: PowerBI Service: List of Power BI Reports/Data set's with Failed Refreshes

Hi v-lid-msft, do we need to have any special permissions or access when calling the Rest API?

 

Regards,

Pkasa

Community Support Team
Community Support Team

Re: PowerBI Service: List of Power BI Reports/Data set's with Failed Refreshes

Hi @pkasa ,

 

Calling this API need the read or readwrite premission on datasets. And please following those documents to get the Azure AD Access Token: https://docs.microsoft.com/en-us/power-bi/developer/register-app and https://docs.microsoft.com/en-us/power-bi/developer/walkthrough-push-data-get-tokenBefore you register a Power BI app you need an Azure Active Directory tenant and an organizational user.


Best regards,

 

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Community Support Team
Community Support Team

Re: PowerBI Service: List of Power BI Reports/Data set's with Failed Refreshes

Hi @pkasa ,

 

How about the result after you follow the suggestions mentioned in my original post?Could you please provide more details about it If it doesn't meet your requirement?


Best regards,

 

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
pkasa Frequent Visitor
Frequent Visitor

Re: PowerBI Service: List of Power BI Reports/Data set's with Failed Refreshes

Hello  , Part of it worked they way you have described. I have tried it and it worked for datasets only that are under my control. I could not see all the report datasets w/ Failes Refresh Schedules in the App workspace.

 

Have not Published and tested it though.

 

Regards,

 

 

Community Support Team
Community Support Team

Re: PowerBI Service: List of Power BI Reports/Data set's with Failed Refreshes

Hi @pkasa ,

 

First of all, we need to create a parameter to save the token,

 

16.PNG17.PNG

 

Then we use a query that will gain all the history within the workspace this token have access to.

 

let
    Source = Json.Document(Web.Contents("https://api.powerbi.com/v1.0/myorg/groups", [Headers=[Authorization="Bearer " & TokenParameter]]))[value],
    test = Table.FromList(Source,Record.FieldValues,{"id","isReadOnly","isOnDedicatedCapacity","name"}),
    #"Removed Other Columns" = Table.SelectColumns(test,{"id", "name"}),
    AppWorkSpace = Table.AddColumn(#"Removed Other Columns","Data",each let id = [id] in let
    Source = Json.Document(Web.Contents("https://api.powerbi.com/v1.0/myorg/groups/"& id &"/datasets", [Headers=[Authorization="Bearer " & TokenParameter]]))[value],
    test2 = Table.FromList(Source,Record.FieldValues,{"datasetid","datasetname","addRowsAPIEnabled","configuredBy",
      "isRefreshable",
      "isEffectiveIdentityRequired",
      "isEffectiveIdentityRolesRequired",
      "isOnPremGatewayRequired",
      "targetStorageMode"}),
    #"Removed Columns" = Table.RemoveColumns(test2,{"addRowsAPIEnabled", "isEffectiveIdentityRequired", "isEffectiveIdentityRolesRequired", "targetStorageMode"}),
    #"Filtered Rows" = Table.SelectRows(#"Removed Columns", each ([isRefreshable] = true)),
    #"Added Custom" = Table.AddColumn(#"Filtered Rows", "Refresh History", each Json.Document(Web.Contents("https://api.powerbi.com/v1.0/myorg/groups/"& id &"/datasets/" & [datasetid] & "/refreshes",[Headers=[Authorization="Bearer " & TokenParameter]]))[value]),
    #"Expanded Refresh History" = Table.ExpandListColumn(#"Added Custom", "Refresh History"),
    #"Expanded Refresh History1" = Table.ExpandRecordColumn(#"Expanded Refresh History", "Refresh History", {"datasetid", "refreshType", "startTime", "endTime", "status", "requestId"}, {"Refresh History.id", "Refresh History.refreshType", "Refresh History.startTime", "Refresh History.endTime", "Refresh History.status", "Refresh History.requestId"}),
    #"Filtered Rows1" = Table.SelectRows(#"Expanded Refresh History1", each ([Refresh History.status] = "Failed")),
    #"Reordered Columns" = Table.ReorderColumns(#"Filtered Rows1",{"datasetid", "datasetname", "Refresh History.status", "Refresh History.startTime", "Refresh History.endTime", "configuredBy", "isRefreshable", "isOnPremGatewayRequired", "Refresh History.id", "Refresh History.refreshType", "Refresh History.requestId"})
in
    #"Reordered Columns"),
    MyWorkSpace = Table.AddColumn(#table({"id","name"},{{"Null","My_Workspcae"}}),"Data",each let
    Source = Json.Document(Web.Contents("https://api.powerbi.com/v1.0/myorg/datasets", [Headers=[Authorization="Bearer " & TokenParameter]]))[value],
    test = Table.FromList(Source,Record.FieldValues,{"datasetid","datasetname","addRowsAPIEnabled","configuredBy",
      "isRefreshable",
      "isEffectiveIdentityRequired",
      "isEffectiveIdentityRolesRequired",
      "isOnPremGatewayRequired",
      "targetStorageMode"}),
    #"Removed Columns" = Table.RemoveColumns(test,{"addRowsAPIEnabled", "isEffectiveIdentityRequired", "isEffectiveIdentityRolesRequired", "targetStorageMode"}),
    #"Filtered Rows" = Table.SelectRows(#"Removed Columns", each ([isRefreshable] = true)),
    #"Added Custom" = Table.AddColumn(#"Filtered Rows", "Refresh History", each Json.Document(Web.Contents("https://api.powerbi.com/v1.0/myorg/datasets/" & [datasetid] & "/refreshes",[Headers=[Authorization="Bearer " & TokenParameter]]))[value]),
    #"Expanded Refresh History" = Table.ExpandListColumn(#"Added Custom", "Refresh History"),
    #"Expanded Refresh History1" = Table.ExpandRecordColumn(#"Expanded Refresh History", "Refresh History", {"datasetid", "refreshType", "startTime", "endTime", "status", "requestId"}, {"Refresh History.id", "Refresh History.refreshType", "Refresh History.startTime", "Refresh History.endTime", "Refresh History.status", "Refresh History.requestId"}),
    #"Filtered Rows1" = Table.SelectRows(#"Expanded Refresh History1", each ([Refresh History.status] = "Failed")),
    #"Reordered Columns" = Table.ReorderColumns(#"Filtered Rows1",{"datasetid", "datasetname", "Refresh History.status", "Refresh History.startTime", "Refresh History.endTime", "configuredBy", "isRefreshable", "isOnPremGatewayRequired", "Refresh History.id", "Refresh History.refreshType", "Refresh History.requestId"})
in
    #"Reordered Columns"),
    ALlData = Table.Combine({AppWorkSpace,MyWorkSpace}),
    #"Expanded Data" = Table.ExpandTableColumn(ALlData, "Data", {"datasetid", "datasetname", "Refresh History.status", "Refresh History.startTime", "Refresh History.endTime", "configuredBy", "isRefreshable", "isOnPremGatewayRequired", "Refresh History.id", "Refresh History.refreshType", "Refresh History.requestId"}, {"Data.datasetid", "Data.datasetname", "Data.Refresh History.status", "Data.Refresh History.startTime", "Data.Refresh History.endTime", "Data.configuredBy", "Data.isRefreshable", "Data.isOnPremGatewayRequired", "Data.Refresh History.id", "Data.Refresh History.refreshType", "Data.Refresh History.requestId"})
in
    #"Expanded Data"

 

We just need to change the value of paramater instead change the query

 

18.PNG

 

After you publish this report to Power BI Service, we can use the Update Parameter API to update the token in this report ( because the tooken will expire)


Best regards,

 

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
pkasa Frequent Visitor
Frequent Visitor

Re: PowerBI Service: List of Power BI Reports/Data set's with Failed Refreshes

Thanks v-lid-msft, everything worked on the desktop as you showed. The trouble started when I deployed this to Service.

 

I got the below warning under settings for this Dataset in service:

 

You can't schedule refresh for this dataset because the following data sources currently don't support refresh:

  • Data source for "Refresh Failures"

Discover Data Sources

 

 

And under Parameters the below warning appeared:

 

Parameters haven't been defined for this dataset yet. If you want to set parameters, use the Query Editor. Learn more

 

 

Regards,

Pkasa

Helpful resources

Announcements
Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power BI Desktop Update - February 2020

Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors
Top Kudoed Authors