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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
PavelVitek
New Member

Dynamic Query Parametrisation help

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

 

4 REPLIES 4
PavelVitek
New Member

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
Solution Sage
Solution Sage

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

Microsoft Employee
PavelVitek
New Member

Hi @v-stephen-msft ,

The error is this:

PavelVitek_0-1672139421285.png

 

v-stephen-msft
Community Support
Community Support

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.

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors