Power Query only show 100 items

[Anonymous]

Hi everyone!
I'm trying to import some Leads data from a CRM API. I have around 1450 leads but I can import only 100 😞
I've tried to load into the dashboard with no success.

ALso tried to change the option on the footer of power query.

This is the API https://exactdev.docs.apiary.io/#/reference/0/lista-de-leads?mc=reference%2F0%2Flista-de-leads%2Flis...

Any tips ? 😞 

Thanks!!

[Anonymous]

"Kind of" solved. I inserted "max_results" to 2.000 and worked 🙂

[PhilipTreacy · Accepted solution]

Hi @Anonymous 

Great, you made progress.  You've modified my code slightly and there are a couple of things that need fixing up.  Let's work with the max_results approach.

Here's a sample PBIX file that contains all of the code shown below.

let
    url= "https://api.exactsales.com.br/v2/listarlead",
    DtCadastroInicio = "xxxxxxx",
    token_exact = "xxxxxxxx",
    max_results = 2000,

    Source = Web.Contents(
                
                        url,
                        
                        [ 
                            Query = [max_results=max_results, DtCadastroInicio=DtCadastroInicio],

                            Headers = [token_exact=token_exact]

                        ]
                    )
 
in
    Source

At the top of the query is the setup. The url only needs to be as shown here.  This is also where you specify values for the query parameters like max_results.  These query parameters are added to the API call a few lines further down inside the Web.Contents function.  Same thing for the Headers.  This way you can modify the parameters and never need to touch the Web.Contents code again.

NOTE: The API documentation shows that DtCadastroInicio shoudl be included in the API call so I've included it.  But you don't seem to have used it?

The above code should give you a maximum of 2000 results from the API.

To implement paging use this:

let
    url= "https://api.exactsales.com.br/v2/listarlead",
    DtCadastroInicio = "xxxxxxx",
    token_exact = "xxxxxxxx",

    GetPage = (Page) =>

        let 
            PageNum = Text.From(Page),
            Source = Web.Contents(
                
                        url,
                        
                        [ 
                            Query = [page=PageNum, DtCadastroInicio=DtCadastroInicio],

                            Headers = [token_exact=token_exact]

                        ]
                    )
        in
            Source,

 
    PageIndices = { 1 .. 5 },
    Pages = List.Combine(List.Transform(PageIndices, each GetPage(_)))
in
    Pages

Again, setup of parameters is at the top.

Then there's a function caled GetPage - you don't need tomodify this unlessyou want to add mor query parameters.

The bit that makes the API calls is the Pages = List.Combine ...... each GetPage(_)))  which calls GetPage for every number in the PageIndices list.  So it makes 5 calls and gets 5 pages of 100 results each time.  These are stored within Pages.

Up to you how you want to do it but I'd probably go with the max_results approach as there'll be less transformations to do with the results.

NOTE: I think you must already know this but when PBI asks you for credentials to connect to the site, choose Anonymous access.

In terms of courses, there are lots to choose.  Here are a couple

MOTH - Power Query Course

PQ Academy

Regards

Phil

---

If I answered your question please mark my post as the solution.
If my answer helped solve your problem, give it a kudos by clicking on the Thumbs Up.

[PhilipTreacy]

Hi @Anonymous 

You need to specify page numbers to get the next pages of data.

The API states that a call looks like this 

https: / / api.exactsales.com.br / v2 / listarlead? page = {page} & DtCadastroInicio = {DtCadastroInicio}

so you need to call the API however many times you need to get all the data.

You could use something like this which will get the first 5 pages of data because PageIndices holds the values 1 to 5.

let
    url= "https://api.exactsales.com.br/v2/listarlead?",
    DtCadastroInicio = "xxxxxxx",

    GetPage = (Page) =>

        let 
            PageNum = Text.From(Page),
            Source = Web.BrowserContents(url & "page=" & page & "&DtCadastroInicio=" & DtCadastroInicio)
        in
            Source,
 
    PageIndices = { 1 .. 5 },
    Pages = List.Combine(List.Transform(PageIndices, each GetPage(_)))
in
    Pages

This is just an example and you may need to add further query parameters to get what you need.

If you are having trouble getting this to work please post back with an example of your exact query string/HTTP API call and I'll get it working for you.

Regards

Phil

---

If I answered your question please mark my post as the solution.
If my answer helped solve your problem, give it a kudos by clicking on the Thumbs Up.

[Anonymous]

@PhilipTreacy , no success 😞

Btw, is there a book or course so I can learn this script language?

[Anonymous]

Hi Philip, thanks for the reply! I'll try here. I don't have much knowledge on how to express those strings, since it's my first time working with APIs.