cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Community Support
Community Support

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

Highlighted
pkasa
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
Community Support

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

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
June 2020 Community Challenge: Can You Solve These?

June 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

Announcing the New Spanish Forum

Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section.

April 2020 Community Highlights

April 2020 Community Highlights

Info on our Super Users, MBAS content and badges, and updates to our support articles. - Read the full Community Highlights.

MBAS Gallery 2020

MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.