Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Hi @jbruewer ,
As Ken Puls's blog mentioned, you cannot combine an external data source with another query in power query. You can try to follow the blog's suggestion to re-bulid your query.
In addition, you can also refer this blog and comments under it:
Web.Contents(), M Functions And Dataset Refresh Errors In Power BI
Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thanks v-yingjl,
for your contribution 👍. I've already tried this approach and recognized that i've been limited by the syntax check fo the Query:
#"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"]]))),
as you see the URL looks like:
"https://restapiService.com/index.php?/api/v2/get_TaskOutput/1234"
I have a standard REST API call without a Query option like "q=1234" or "q=/api/v2/get_TaskOutput/" & [id]".
The query parser request this syntax an i am not able to change it according the proposed pattern.
I am still looking forward to get some hints or new ideas to solve this issue.
All help is appriciated!
//joerg
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.