Help with dynamic data source refresh and RelativePath+Web.Contents

[dor-rog-tech]

Hello!

I have dataset which came from my app online in this platform:
tadabase 

They have documentation regarding the connection between their database and powerbi.
The connection works very well on the desktop, but when I want to refresh on the service, it gives me the error message of:

This dataset includes a dynamic data source. Since dynamic data sources aren't refreshed in the Power BI service, this dataset won't be refreshed. Learn more: https://aka.ms/dynamic-data-sources.

From a search here around the forum, I found that the formula of RelativePath with Web.Contents needs to be inserted in some way.

Can anyone help me figure out exactly where?

let 
    apiUrl = "https://api.tadabase.io/api/v1/data-tables/<REPLACE-WITH-YOUR-TABLE-ID>/records",
    perPage = 100,
    GetJson = (Url) =>
        let Options = [Headers =[
    			          #"X-Tadabase-App-Key"= "<REPALCE WITH YOUR APP KEY>", 
                          #"X-Tadabase-App-Secret"= "<REPLACE WITH YOUR APP SECRET >",
                          #"X-Tadabase-App-id"="<REPLACE WITH YOUR APP ID>"
			          ]
		          ],
            RawData = Web.Contents(Url, Options),
            Json    = Json.Document(RawData)
        in  Json,

     GetPage = (Index) =>
        let Page  = "page=" & Text.From(Index),
            Limit   = "limit=" & Text.From(perPage),
            Url   = apiUrl & "?" & Page & "&" & Limit,
            Json  = GetJson(Url),
            Value = Json
        in  Value,

     getRecords = (page as number, AccumData as list) =>
            let
                Data = Function.InvokeAfter(()=>GetPage(page), #duration(0,0,0,2)),         
                Result =      
                    if page >= Data[total_pages] 
                    then Table.FromRecords(List.Combine({AccumData, Data[items]}))
                    else @getRecords(page + 1, List.Combine({AccumData, Data[items]})) 
            in
                Result,
 
    Records  = getRecords(1, {})
in
    Records

Thank you!

[GilbertQ]

Hi @dor-rog-tech 

This is an example of what you are looking for: Power Query - Dynamic Data Source and Web.Contents() - Hat Full of Data

[dor-rog-tech]

Thanks!

I hope I will understand what to do,

But can you please help me figure out excatly which line should I change? 

[GilbertQ]

Hi @dor-rog-tech 

I would expect it to be in line "apiUrl" and put in the Relative Path  in the section [] as in Chris Webb blog post.

[dor-rog-tech]

Hi @GilbertQ 

I tried using RelataivePath as an Option and followed the instructions in the articles you sent but there is still an issue and this time it is another one:

Here is a current query:

let 
    apiUrl = "https://api.tadabase.io/api/v1",
    relativePath = "/data-tables/<REPLACE-WITH-YOUR-TABLE-ID>/records",
    perPage = 100,
    GetJson = (Page) =>
        let Options = [
            Headers=[
                #"X-Tadabase-App-Key"="<REPLACE WITH YOUR APP KEY>", 
                #"X-Tadabase-App-Secret"= "<REPLACE WITH YOUR APP SECRET>",
                #"X-Tadabase-App-id"="<REPLACE WITH YOUR APP ID>"
			],
            RelativePath=relativePath,
            Query=[
                page=Page,
                limit=Text.From(perPage)
            ]
        ],
            RawData = Web.Contents(apiUrl, Options),
            Json    = Json.Document(RawData)
        in  Json,

     GetPage = (Index) =>
        let Page    = Text.From(Index),
            Json  = GetJson(Page),
            Value = Json
        in  Value,

     getRecords = (page as number, AccumData as list) =>
            let
                Data = Function.InvokeAfter(()=>GetPage(page), #duration(0,0,0,2)),         
                Result =      
                    if page >= Data[total_pages] 
                    then Table.FromRecords(List.Combine({AccumData, Data[items]}))
                    else @getRecords(page + 1, List.Combine({AccumData, Data[items]})) 
            in
                Result,
 
    Records  = getRecords(1, {})
in
    #"Renamed Columns"

As I understand PowerBi firstly will check the baseUrl and only then do a call. But in our case, an api requires custom headers (all 'X-Tadabase..." headers) even for the base URL

Am I right?

If so is there any way to provide these headers from outside a query?

Thank you

[GilbertQ · Accepted solution]

Hi @dor-rog-tech 

Set the options for the data source to skip the connection test and try that again?

[dor-rog-tech]

Thanks @GilbertQ !

[jp123]

Hi @dor-rog-tech,

Were you able to get the relative path and scehduled refresh to work on your Tadabase data source? 

I cannot get it to work with my code. Can you please help and show me where I should place the RelativePath code? Here is my query... Any assisteance would be greatly appreciated!

let 
    apiUrl = "https://api.tadabase.io/api/v1/data-tables/xxxxx",
    perPage = 100,
    GetJson = (Url) =>
        let Options = [Headers =[
    			  #"X-Tadabase-App-Key"= "xxxx", 
                          #"X-Tadabase-App-Secret"= "xxxx",
                          #"X-Tadabase-App-id"="xxxx"
			          ]
		          ],
            RawData = Web.Contents(Url, Options),
            Json    = Json.Document(RawData)
        in  Json,

     GetPage = (Index) =>
        let Page  = "page=" & Text.From(Index),
            Limit = "limit=" & Text.From(perPage),
            Url   = apiUrl & "?" & Page & "&" & Limit,
            Json  = GetJson(Url),
            Value = Json
        in  Value,

     getRecords = (page as number, AccumData as list) =>
            let
                Data = Function.InvokeAfter(()=>GetPage(page), #duration(0,0,0,2)),         
                Result =      
                    if page >= Data[total_pages] 
                    then Table.FromRecords(List.Combine({AccumData, Data[items]}))
                    else @getRecords(page + 1, List.Combine({AccumData, Data[items]})) 
            in
                Result,