Dynamic DataSource Error - RelativePath and Query?

[leemat]

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

[Jonvoge · Accepted solution]

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

[leemat]

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,

[v-cgao-msft]

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
    DataSet

Best Regards,
Gao
Community Support Team

If 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

[leemat]

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?