Forum Discussion
Creating a report using MS API call on PBI Service
Thankyou, na12063, oussamahaimoud and tayloramy for your responses.
Hi jight,Based on our understanding, the issue may be related to the authentication pattern currently being used inside Power Query. The access token is being dynamically generated within the M query using Service Principal credentials and is then passed into the Web.Contents() function.
Since you already have Fabric capacity available, one possible approach would be to use a Fabric Notebook or Data Pipeline, or an Azure Function or Logic App to handle the Service Principal authentication and Power BI REST API calls externally. The results can then be stored in a Lakehouse or Warehouse table. After that, you can connect Power BI to the stored data instead of calling the REST API directly from Power Query.
For refresh monitoring, you can use APIs such as the Get Refresh History API. Additionally, tenant wide monitoring APIs require Power BI or Fabric admin permissions, whereas workspace level APIs can still work provided the Service Principal has access to those workspaces and the required API permissions have been granted.
Please refer to the links below for further reference:
Datasets - Get Refresh History - REST API (Power BI Power BI REST APIs) | Microsoft Learn
Power BI REST APIs for embedded analytics and automation - Power BI REST API | Microsoft Learn
Embed Power BI content in an embedded analytics application with service principal and an application secret - Power BI | Microsoft LearnWe hope the information provided helps resolve the issue. Should you have any further queries, please feel free to contact the Microsoft Fabric community.
Thank you.
Hi jight,
I would recommend first using a notebook to get all the data you care about in one place, and then report off of it.
FIrst you can list all the items in a workspace using the list items endpoint:
https://learn.microsoft.com/en-us/rest/api/fabric/core/items/list-items?tabs=HTTP
Then you can filter the response to only datasets, and from there you can get the refresh history:
https://learn.microsoft.com/en-us/rest/api/power-bi/datasets/get-refresh-historyOnce you have this data all in a table, then building the report in power bi is easy.
Hi jight,
What API data are you after?
I would recommend either using a pre built accellerator like FUAM as mentioned by na12063, or using a notebook to pull data specifically from the API and store it in a lakehouse.
What sort of licensing do you have? Do you have a capacity and Fabric, or just Power BI? That will also change what is possible.
This is for my organisation so I can't install something easily. I got Pro license and both capacity and Fabric but I'm only using PBI for this.
I'm trying to create a status report for all our DFs and models' refresh status. I don't have admin access to extract refresh data from the MS server directly, so the alternative I can think of is extracting it using API.
Jaimy
Hi jight,
I would recommend first using a notebook to get all the data you care about in one place, and then report off of it.
FIrst you can list all the items in a workspace using the list items endpoint:
https://learn.microsoft.com/en-us/rest/api/fabric/core/items/list-items?tabs=HTTP
Then you can filter the response to only datasets, and from there you can get the refresh history:
https://learn.microsoft.com/en-us/rest/api/power-bi/datasets/get-refresh-historyOnce you have this data all in a table, then building the report in power bi is easy.