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

[Anonymous]

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]

Hi @Anonymous ,

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.

Best regards,

[Anonymous]

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

Regards,

Pkasa

[v-lid-msft]

Hi @Anonymous ,

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,

[Anonymous]

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

[v-lid-msft]

Hi @Anonymous ,

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

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

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,

[Anonymous]

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

[v-lid-msft · Accepted solution]

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,

[SofBL]

to get all datasets and the last refresh into files that can later be imported you can also use the power bi cmd lets. this makes automation possible and you do not have to use a parameter with bearer token.

## once in a lifetime you got to do this:
## Install-Module -Name MicrosoftPowerBIMgmt
## somtimes in a lifetime you got to do this:
## Update-Module -Name MicrosoftPowerBIMgmt

## create two folders c:\temp\datasets and c:\temp\refreshes

## login to the platform
Login-PowerBI
#Get a token
$headers = Get-PowerBIAccessToken
#clear previous
Remove-Item c:\temp\datasets\*.* -Force
Remove-Item c:\temp\refreshes\*.* -Force

# List the full list of workspaces
$workspaces = Get-PowerBIWorkspace | Select-Object -Property Name,Id

foreach ($workspace in $workspaces){
#concaternating the workspaceid caused spaces in the url and filepath, solved with this ForEach-Object though it does not feel quite elegant
$filepath = (ForEach-Object {'C:\temp\datasets\datasets{0}.csv' -F $workspace.Id })
$exportheader = "WorkspaceId,DatasetId,Name,ConfiguredBy,UpstreamDatasets,CreatedDate,IsOnPremGatewayRequired,Description"
$exportheader| Out-File $filepath -Append
$uri = (ForEach-Object {'https://api.powerbi.com/v1.0/myorg/groups/{0}/datasets' -F $workspace.Id })
try {
$datasets = Invoke-RestMethod -Uri $uri -Headers $headers
foreach ($dataset in $datasets.value) {
$exportlines = "$($workspace.Id),$($dataset.id),$($dataset.name),$($dataset.configuredBy),$($dataset.upstreamDatasets),$($dataset.createdDate),$($dataset.isOnPremGatewayRequired),$($dataset.isRefreshable),$($dataset.description)"
$exportlines| Out-File $filepath -Append
if ($dataset.isRefreshable -eq "True" ) {
try {
$refreshfilepath = (ForEach-Object {'c:\temp\refreshes\refresh{0}.json' -F $dataset.id })
$urirefresh = (ForEach-Object {'https://api.powerbi.com/v1.0/myorg/datasets/{0}/refreshes?$top=1' -F $dataset.id })
Write-Output $urirefresh
Invoke-RestMethod -Uri $urirefresh -Headers $headers -Method GET -OutFile $refreshfilepath
}
catch{Write-Output "failed to collect refreshes of dataset id"}
}
}

}
catch {Write-Output "try failed"}}

[Anonymous]

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

[v-lid-msft]

Hi @Anonymous ,

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,

[Anonymous]

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

[v-lid-msft]

Hi @Anonymous ,

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-token, Before you register a Power BI app you need an Azure Active Directory tenant and an organizational user.

Best regards,

[jcullum]

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

[Anonymous]

Thanks jcullum, we will give it a read.