Dynamic Query Parametrisation help

[PavelVitek]

Hi!

I am having trouble with parametrisation of this query. It still is considered as dynamic data and can not be refreshed via PBI portal.

Any help would be appreciated.

let
    baseuri = "https://app.caflou.com/",
    
    RelPath = "api/v1/00000/timesheets/",
   headers = [Headers=[Authorization="AuthToken"]],
   

    initReq =      Json.Document(Web.Contents("https://app.caflou.com/", [RelativePath="api/v1/0000/timesheets/", Headers=[Authorization="AuthToken"]])),
  
     initData = initReq[results],
     initNumber = initReq[page],
   
    
  gather = (data as list, uri) =>
        let
            //get new offset from active uri
            
        newOffset = Json.Document(Web.Contents(uri, headers))[next_page],
            
           
            //build new uri using the original uri so we dont append offsests

            newBaseUri = "https://app.caflou.com/",
            newUri = "api/v1/0000/timesheets?page=" & Text.BetweenDelimiters(newOffset, "page=", "&")  &"&per=20" ,
            //get new req & data
            
            newReq = Json.Document(Web.Contents(baseuri,
            [RelativePath = "api/v1/0000/timesheets?page=" & Text.BetweenDelimiters(newOffset, "page=", "&")  &"&per=20",
            Headers=[Authorization="AuthToken"]])),
            newdata = newReq[results],
            //add that data to rolling aggregate
            data = List.Combine({data, newdata}),
            //if theres no next page of data, return. if there is, call @gather again to get more data
            check = if newReq[next_page] = null then data else @gather(data,baseuri & newUri)
        in check,
    //before we call gather(), we want see if its even necesarry. First request returns only one page? Return.
    outputList = if initReq[next_page] = null then initData else gather(initData, baseuri & RelPath)
   
in
    outputList


  

Thank you!

Kind regards,

Pavel

[PavelVitek]

HI @ppm1 ,

That may be correct, I was not able to find out if the PBI app has issues with all mentions of dynamic queries or only those with which they retrieve datas. My main concern is, that I am unable to fix it due to lack of my capabilities, which is also why I am asking for help.

Thank you,

P.

[ppm1]

I didn't check the whole thing but I do see that you create a dynamic url in this step.

check = if newReq[next_page] = null then data else @gather(data,baseuri & newUri)

Pat

[PavelVitek]

Hi @Anonymous ,

The error is this:

[Anonymous]

Hi @PavelVitek ,

What error message about refreshing did you get? 

For now, maybe these similar blogs could help you.

1. Setting a scheduled refresh on a Dynamic Data Source in Power BI 
2. Web.Contents(), M Functions And Dataset Refresh Errors In Power BI 
3. Using The RelativePath And Query Options With Web.Contents() In Power Query And Power BI M Code 

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.