cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
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

pkasa Frequent Visitor
Frequent Visitor

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

Hello v-lid-msft, Thanks for the solution. It has worked like a charm. However, is there a way to Automate the Token for the Parameter ? 

The way it is in right now is still dependent on the Manual input of the token and Manual Refresh. We would like to Schedule couple of times a day without a need of providing the token manually.

 

 

Regards,

PKasa

Community Support Team
Community Support Team

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

Hi @pkasa ,

 

Sorry for late reply, We can get access token according to this document: https://docs.microsoft.com/en-us/azure/active-directory/develop/v2-oauth-ropc#authorization-request

 

But because write credential into queries is very dangerous ( all the user who can edit the report may be able to get account information in queies), so we suggest you to generate access token by another application and update to parameter to previous quires

 

 

 

let
    // Attribute for Queies
    appClientID = "Your_Application_ID",
    appScope = "https://analysis.windows.net/powerbi/api/.default",
    username = "Your_Username",
    password = "Your_Password",
    tokenRequesetBody = "client_id=" & appClientID & "&scope=" & appScope & "&username=" & username & "&password=" & password & "&grant_type=password",

    // GetAccessToken
    tokenRequestResponse = Web.Contents("https://login.microsoftonline.com/", [RelativePath="organizations/oauth2/v2.0/token/",Headers=[#"Content-Type"="application/x-www-form-urlencoded"], Content=Text.ToBinary(tokenRequesetBody)]),
    AccessTokenResponse = Json.Document(tokenRequestResponse),
    AccessTokenBear = AccessTokenResponse[access_token] as text,
    
    
    Source = Json.Document(Web.Contents("https://api.powerbi.com/v1.0/myorg/groups", [Headers=[Authorization="Bearer " & AccessTokenBear]]))[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 " & AccessTokenBear]]))[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 " & AccessTokenBear]]))[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 " & AccessTokenBear]]))[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 " & AccessTokenBear]]))[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"

 

 

Please be careful about the permission to this report if you want to use above queries. We are trying to get access token without build-in password inside queries and will update here if we work out.


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 v-lid-msft,

                           I completely agree with you that inserting username and password is not the right way to do this and I am against it as you are. I saw from the below url by default Microsoft is giving the token based on my login is there a way to import this into Power BI and capture the Token. Look for Request Preview Section in the below url, this will have the token, after sigining into the Org account. Not sure if this helps you.

https://docs.microsoft.com/en-us/rest/api/power-bi/datasets/getrefreshhistoryingroup#code-try-0

 

 

Thanks for all your help on this.

 

Regards,

PKasa

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

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