The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now
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?
Solved! Go to 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,
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,
Hi v-lid-msft, do we need to have any special permissions or access when calling the Rest API?
Regards,
Pkasa
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,
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,
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,
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:
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
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,
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"}}
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
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,
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
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,
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