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.
Hello Experts,
We are building a Power BI connector to extract data from one of our systems that expose data via Rest API. Hoping that you can guide me in right direction with following issue
API structure as follows:
baseURI: https://api.myapp.com/api/rest/
API end point for document list: https://api.myapp.com/api/rest/documents/ => (returns a list of docIDs)
API end point for document users: https://api.myapp.com/api/rest/documents/docID/users
After getting response from List API, I am try to add a new column by invoking a custom function that accepts docID as parameter and then invoke document users API.
I get the following error: Expression.Error: Access to the resource is forbidden.
Tried changing the Privacy levels at Data source settings but no luck!
It works fine when I try to hard code any docID instead of passing as a parameter. Kindly find my code below and request you to help. Thanks!
Query1:
let
DefaultRequestHeaders = [
#"Accept" = "application/json", // column name and values only
#"Authorization" = "****",
#"client_id" = "****",
#"client_secret" = "****",
],
source = Web.Contents("https://api.myapp.com/api/rest/", [ RelativePath = "documents", Headers = DefaultRequestHeaders ]),
json = Json.Document(source),
docList = json[docList],
#"Converted to Table" = Table.FromList(docList, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"id"}, {"id"}),
#"Invoked Custom Function" = Table.AddColumn(#"Expanded Column1", "Query2", each Query2([id]))
in
#"Invoked Custom Function"
Query2:
let
GetUsers = (docId as text) as table =>
let
DefaultRequestHeaders = [
#"Accept" = "application/json", // column name and values only
#"Authorization" = "****",
#"client_id" = "****",
#"client_secret" = "****",
],
source = Web.Contents("https://api.myapp.com/api/rest/", [ RelativePath = "documents/"&docId&"/users", Headers = DefaultRequestHeaders ]),
json = Json.Document(source),
#"Converted to Table" = Record.ToTable(json),
#"Pivoted Column" = Table.Pivot(#"Converted to Table", List.Distinct(#"Converted to Table"[Name]), "Name", "Value")
in
#"Pivoted Column"
in
GetUsers
@ppraveenk , refer to these previous solutions if these can help
https://community.powerbi.com/t5/Desktop/Access-to-the-resource-is-forbidden/m-p/1093338
https://community.powerbi.com/t5/Desktop/Access-to-the-resource-is-forbidden/m-p/366485
Hi @amitchandak thanks for your response.
I tried clearing the credentials from Data source settings and it didnt work.
Believe that issue is not with credentials since I am able to invoke Query2 alone by providing a docID. Facing the issue only when I invoke Query2 as custom function with each record of Query1 response.
Since I am using Relative path in Web.Contents(), not sure if this issue has something to do with Privacy levels. Tried will all available privacy level options but in vain.
Clueless to what I am missing here and it would be really helpful if some guide me.
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.
User | Count |
---|---|
113 | |
99 | |
80 | |
70 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |