Custom REST calls

[smpa01]

@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

[v-cboorla-msft]

Hi @smpa01 

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

[gbrueckl · Accepted solution]

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