Forum Discussion

smpa01's avatar
smpa01
Community Champion
2 years ago
Solved

Custom REST calls

gbrueckl  came across this fantastic piece . I have tested it out and it is simply awesome.

 

I was wondering if there is  a way to utilize any of the RESTApi calls here. For example, what if I want to know the refresh history of each dataset. The request URL is following

 

https://api.powerbi.com/v1.0/myorg/datasets/{datasetId}/refreshes

 

 

Is it doable? How do I make adjustments to the following to get the refresh history forEach dataset

 

WITH cte_workspaces AS (
    SELECT explode(pbi_api_udf('/groups', '${pbi_access_token}')) as workspace
)
SELECT workspace.name, workspace.id, pbi_api_udf(concat(workspace.apiPath, '/datasets'), '${pbi_access_token}') as datasets
FROM cte_workspaces

 

 

 

data engineering
  • gbrueckl's avatar
    gbrueckl
    2 years ago

    something like this should technically work

    WITH cte_workspaces AS (
    SELECT explode(pbi_api_udf('https://api.powerbi.com/v1.0/myorg/groups', '${token}')) AS workspace
),
cte_datasets AS (
SELECT workspace.name AS ws_name, workspace.id AS ws_id, explode(pbi_api_udf(concat(workspace.apiPath, '/datasets'), '${token}')) AS dataset
FROM cte_workspaces
)
SELECT ws_name, ws_id, dataset.name AS ds_name, dataset.id AS ds_id,
    explode(pbi_api_udf(concat(dataset.apiPath, '/refreshes'), '${token}')) AS refreshes
FROM cte_datasets

     but I just tried it on my quite small environment and it errors out with too many requests

2 Replies

  • gbrueckl's avatar
    gbrueckl
    Most Valuable Professional
    2 years ago

    something like this should technically work

    WITH cte_workspaces AS (
    SELECT explode(pbi_api_udf('https://api.powerbi.com/v1.0/myorg/groups', '${token}')) AS workspace
),
cte_datasets AS (
SELECT workspace.name AS ws_name, workspace.id AS ws_id, explode(pbi_api_udf(concat(workspace.apiPath, '/datasets'), '${token}')) AS dataset
FROM cte_workspaces
)
SELECT ws_name, ws_id, dataset.name AS ds_name, dataset.id AS ds_id,
    explode(pbi_api_udf(concat(dataset.apiPath, '/refreshes'), '${token}')) AS refreshes
FROM cte_datasets

     but I just tried it on my quite small environment and it errors out with too many requests

  • v-cboorla-msft's avatar
    v-cboorla-msft
    Microsoft Employee
    2 years ago

    Hi smpa01 

     

    Glad that your query got resolved. Please continue using Fabric Community for any help regarding your queries.