Currently i've to develop a dataset that cascades data from different REST APIs. All APIs are provided by the same solution. On my local Power BI Desktop solution, the refresh is working like a charm. But on server it fails.
Objective
The overall objective is, to get a table that contains all task output!
Tasks can be related to a project or completly independend.
Following scenario:
1. I load a initial data set of SystemIDs i am using to iterate.
2. Iterate across the initial data [SystemID] and calling the rest api:
#"getProject data" = Table.AddColumn(letSource, "result", each Json.Document(Web.Contents("https://restapiService.com", [RelativePath="/index.php?/api/v2/get_projects/" & [SystemID], Headers=[#"Content-Type"="application/json"]]))),
3. Use the data of Step 2. and use field [ProjectID] for getting project tasks [ProjectTasks] like:
#"getProject tasks" = Table.AddColumn(letSource, "result", each Json.Document(Web.Contents("https://restapiService.com", [RelativePath="/index.php?/api/v2/get_Project_Tasks/" & [ProjectID], Headers=[#"Content-Type"="application/json"]]))),
4. Additional to that i've a dedicated report that collects all independend tasks across topics by a Power BI queriy: "ALL_Tasks" this is done by an independend RestAPI call like:
#"getALL_tasks" = Table.AddColumn(letSource, "result", each Json.Document(Web.Contents("https://restapiService.com", [RelativePath="/index.php?/api/v2/get_all_tasks/" , Headers=[#"Content-Type"="application/json"]]))),
The query "ALL_Tasks" i've used the the combine function to get by the end all "Tasks" in one table:
#"Appended runs" = Table.Combine({letSource, ALL_Tasks}),
5. Now i need to get the output of the tasks by iterating over the now combine Tasks of Project related and independend tasks by calling the rest api:
#"getTasks output" = Table.AddColumn(letSource, "result", each Json.Document(Web.Contents("https://restapiService.com", [RelativePath="/index.php?/api/v2/get_TaskOutput/" & [TaskID], Headers=[#"Content-Type"="application/json"]]))),
-----------------------------------------------------------------------------------------
Tried solution:
I've tried to get all rest calls in one Query.
Dataset refresh has failed on the server with the same issue i got by seperating into following query:
query_init:
execute only Step 1.
query_1
execute only Step 2 and Step 3
query_3
execute only Step 4
query_4
execute only Step 5.
By the end both tries result in the same or a similiar error:
[Unable to combine data] Section1/query_4/Expanded result1 references other queries or steps, so it may not directly access a data source. Please rebuild this data combination.. The exception was raised by the IDataReader interface. Please review the error message and provider documentation for further information and corrective action. Table: query_4.
The last of the cascading query faild.
I've also considered Ken Puls proposal to seperate the the external access in dedicated queries...
Power Query Errors: Please Rebuild This Data CombinationThe Excelguru Blog
I am looking for your advice!
//joerg
