Solved: PowerBI Service: List of Power BI Reports/Data set... - Page 2

Anonymous · ‎11-20-2019

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?

v-lid-msft · ‎12-03-2019

Hi @Anonymous ,

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"

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

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

Helpful resources

Microsoft Fabric Learn Together

Power BI Monthly Update - April 2024

Fabric Community Update - April 2024