Forum Discussion
Web.Contents: passing identical keynames in Query subparameter
Hi all
I have this api call:
http://localhost:8080/resto/api/reports/olap?report=SALES&from=01.01.2019&to=18.12.2019&groupRow=WaiterName&groupRow=OpenTime&agr=fullSum&agr=OrderNum
As you can see, keynames "groupRow" and "agr" meet twice in GET-method. I wanna to invoke this api in Power BI using this code:
Data= Web.Contents(
API_SERVER,
[RelativePath = "resto/api/reports/olap",
Query = [report="SALES", from="01.01.2019", to="18.12.2019",groupRow="WaiterName", groupRow="OpenTime", agr="fullSum", agr="OrderNum"]
]
)
But since Query is a record, so it is impossible to use identical record field names.
Is there a workaround? I dont want to use prepared full URL without RelavitePath specified because of URL access credentials (now I set credentianls only for API_SERVER which is http://localhost:8080 in the example above)
I found the final solution, and it is very easy :)
Thouse keynames duplicates are the array representations for HTTP request parameters, and they can be easily passed through Query subparameter as M-language List representation, e.g. from my first post example:
Data= Web.Contents(
API_SERVER,
[RelativePath = "resto/api/reports/olap",
Query = [report="SALES", from="01.01.2019", to="18.12.2019",groupRow={"WaiterName", "OpenTime"}, agr={"fullSum", "OrderNum"}]]
)The record fields of Query - groupRow and agr - are passed as Lists - and this works :)
3 Replies
I found a workaround:
http://localhost:8080/resto/api/reports/olap?report=SALES&from=01.01.2019&to=18.12.2019&groupRow=WaiterName&groupRow=OpenTime&agr=fullSum&agr=OrderNum
becomes as
http://localhost:8080?report=SALES&from=01.01.2019&to=18.12.2019&groupRow=WaiterName&groupRow=OpenTime&agr=fullSum&agr=OrderNum
in Web.Contents invocation, the RelativePath remains the same ("resto/api/reports/olap") and Query subparameter goes away at all.
I discovered that this workaround is good for PBI Desktop, but is too bad for PBI Service sheduler ((
The question remains open
I found the final solution, and it is very easy :)
Thouse keynames duplicates are the array representations for HTTP request parameters, and they can be easily passed through Query subparameter as M-language List representation, e.g. from my first post example:
Data= Web.Contents(
API_SERVER,
[RelativePath = "resto/api/reports/olap",
Query = [report="SALES", from="01.01.2019", to="18.12.2019",groupRow={"WaiterName", "OpenTime"}, agr={"fullSum", "OrderNum"}]]
)The record fields of Query - groupRow and agr - are passed as Lists - and this works :)