Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
leemat
New Member

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

1 ACCEPTED SOLUTION
Jonvoge
Solution Supplier
Solution Supplier

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

View solution in original post

4 REPLIES 4
Jonvoge
Solution Supplier
Solution Supplier

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

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
Community Support
Community Support

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

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?  

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

Top Solution Authors
Top Kudoed Authors