Forum Discussion
Dynamic DataSource Error - RelativePath and Query?
Hi I am trying to publish this report to powerbi online but i cannot get it to refresh getting the error that it cannot refresh dynamic datasources. I did some research and the issue seems to be that i am not using relative path to dyanmically query the datasource. I am new to using relative path and could use some guidance as I can't seem to get it to work right.
Here is the original working code i am trying to covert to use relative path:
let
// API Credentials
oauthSite = "https://<Redcated>.docebosaas.com/oauth2/token",
clientId = "<Redcated>",
clientSecret = "<Redcated>",
// User Credentials
email = "<Redcated>",
pwd = "<Redcated>",
// Assemble Token URL
tokenURL = "client_id=" & clientId & "&" & "client_secret=" & clientSecret & "&" & "grant_type=password&scope=api&username=" & email & "&" & "password=" & pwd,
binaryTokenURL = Text.ToBinary(tokenURL),
// Call for Token
tokenData = Json.Document(Web.Contents(oauthSite, [Headers = [#"Content-Type"="application/x-www-form-urlencoded"], Content=binaryTokenURL])),
// Extract the token from the returned table
token = tokenData[access_token],
site = "https://<Redcated>.docebosaas.com",
ver = "/analytics/v1/",
page = "reports/",
idReport = "2b5d6a72-0a66-43ef-9b68-70742e03e569/",
csv = "export/csv",
endPoint = site & ver & page & idReport & csv,
// Retrieve Execution ID using Token
Source = Json.Document(Web.Contents(endPoint, [Headers=[Authorization="Bearer " & token, Accept="application/json"]])),
data = Source[data],
executionId = data[executionId],
endpoint2 = site & ver & page & idReport & "exports/" & executionId & "/results" & "?pageSize=1000",
// Retrieve Paginated Data
GetPages = (url as text) as list =>
let
GetPageData = (url as text) =>
let
SourceGet = Json.Document(Web.Contents(url, [Headers=[Authorization="Bearer " & token, Accept="application/json"]])),
dataGet = try SourceGet[data] otherwise null,
nextTokenGet = try SourceGet[nextToken] otherwise null,
Encoded = try Uri.EscapeDataString(nextTokenGet) otherwise null
in
[Data = dataGet, NextToken = Encoded],
initialData = GetPageData(url),
accumulatedData = List.Generate(
() => initialData,
each [NextToken] <> null,
each GetPageData(url & "&nextToken=" & [NextToken]),
each [Data]
),
result = List.Combine(accumulatedData)
in
result,
GetToken = (url as text) as text =>
let
GetPageData = (url as text) =>
let
SourceGet = Json.Document(Web.Contents(url, [Headers=[Authorization="Bearer " & token, Accept="application/json"]])),
dataGet = try SourceGet[data] otherwise null,
nextTokenGet = try SourceGet[nextToken] otherwise null,
Encoded = try Uri.EscapeDataString(nextTokenGet) otherwise null
in
[Data = dataGet, NextToken = Encoded],
initialData = GetPageData(url),
accumulatedData = List.Generate(
() => initialData,
each [NextToken] <> null,
each GetPageData(url & "&nextToken=" & [NextToken]),
each [NextToken]
),
tokens = List.Last(accumulatedData)
//NEED TO FIGURE OUT HOW TO GET THE LAST PAGE/TOKEN AND COMBINE WITH THE accumulatedData
in
tokens,
GetLastPage = (url as text) =>
let
SourceGet = Json.Document(Web.Contents(url, [Headers=[Authorization="Bearer " & token, Accept="application/json"]])),
dataGet = try SourceGet[data] otherwise null
in
dataGet,
GetPagesDelay = (url as text) => Function.InvokeAfter(() => GetPages(url), #duration(0, 0, 0, 15)),
GetTokenDelay = (url as text) => Function.InvokeAfter(() => GetToken(url), #duration(0, 0, 0, 15)),
LastDataUrl = endpoint2 & "&nextToken=" & GetTokenDelay(endpoint2),
DataSet = List.Combine({GetPagesDelay(endpoint2),GetLastPage(LastDataUrl)}),
in
DataSet
Hi leemat
As an example of creating a Web data connection using URL and Relative Path, you can see an example in the below that I use to extract Strava data:
let RelativePath = "/" & AthleteID & "/stats", Payload = [RelativePath = RelativePath, Headers=[Authorization = "InsertTokenHere"]], Source = Web.Contents("https://www.strava.com/api/v3/athletes", Payload), JSON = Json.Document(Source) in JSON
_____________________________________________________
I hope my comment was helpful.
If your question was answered, please mark your post as 'Solved' and consider giving me a 'Thumbs Up'.
Find me on LinkedIn, Sessionize, or my blog Downhill Data
4 Replies
- JonvogeMost Valuable Professional
Hi leemat
As an example of creating a Web data connection using URL and Relative Path, you can see an example in the below that I use to extract Strava data:
let RelativePath = "/" & AthleteID & "/stats", Payload = [RelativePath = RelativePath, Headers=[Authorization = "InsertTokenHere"]], Source = Web.Contents("https://www.strava.com/api/v3/athletes", Payload), JSON = Json.Document(Source) in JSON
_____________________________________________________
I hope my comment was helpful.
If your question was answered, please mark your post as 'Solved' and consider giving me a 'Thumbs Up'.
Find me on LinkedIn, Sessionize, or my blog Downhill Data- leematNew Member
This worked great for a single page of the data. Would it be alright to ask you another question?
It seems to be failing (returning nothing) when trying to extend to additional pages. Am I somehow breaking the code when doing the following?GetPages = (url as text) => let GetPageData = (url as text, optional nxtToken as text) => let executionPath = executionId & "/results" & "?pageSize=1000" & nxtToken, Payload = [RelativePath = executionPath, Headers=[Authorization="Bearer " & token, Accept="application/json"]], SourceGet = Json.Document(Web.Contents(url , Payload)), dataGet = try SourceGet[data] otherwise null, nextTokenGet = try SourceGet[nextToken] otherwise null, Encoded = try Uri.EscapeDataString(nextTokenGet) otherwise null in [Data = dataGet, NextToken = Encoded], initialData = GetPageData(url), accumulatedData = List.Generate( () => initialData, each [NextToken] <> null, each GetPageData(url , "&nextToken=" & [NextToken]), each [Data] ), result = List.Combine(accumulatedData) in result,
- AnonymousNot applicable
Hi leemat ,
Try using this base url:oauthSite = "https://<Redacted>.docebosaas.com", oauthEndpoint = "/oauth2/token",Like:
let oauthSite = "https://<Redacted>.docebosaas.com", oauthEndpoint = "/oauth2/token", clientId = "<Redacted>", clientSecret = "<Redacted>", email = "<Redacted>", pwd = "<Redacted>", tokenURL = "client_id=" & clientId & "&" & "client_secret=" & clientSecret & "&" & "grant_type=password&scope=api&username=" & email & "&" & "password=" & pwd, binaryTokenURL = Text.ToBinary(tokenURL), tokenData = Json.Document(Web.Contents(oauthSite & oauthEndpoint, [Headers = [#"Content-Type"="application/x-www-form-urlencoded"], Content=binaryTokenURL])), token = tokenData[access_token], ver = "/analytics/v1/", page = "reports/", idReport = "2b5d6a72-0a66-43ef-9b68-70742e03e569/", csv = "export/csv", endPoint = oauthSite & ver & page & idReport & csv, Source = Json.Document(Web.Contents(endPoint, [Headers=[Authorization="Bearer " & token, Accept="application/json"]])), data = Source[data], executionId = data[executionId], endpoint2 = oauthSite & ver & page & idReport & "exports/" & executionId & "/results" & "?pageSize=1000", ... ... ... in DataSetBest Regards,
Gao
Community Support TeamIf there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!How to get your questions answered quickly -- How to provide sample data in the Power BI Forum -- China Power BI User Group
- leematNew Member
Thank you for the response. Using your solution I am still getting the same error. I think it has to do with passing a url into the WebContents that has variables within it (i.e. it is a dynamic url) doing some base research it appears to achieve this and have it be refreshable in the powerbi service, I may need to use RelativePath.
Would you happen to know how to construct the endpoint and endpoint2 urls within the Web.Contents function using RelativePath?