Power BI and Pipedrive API - Pagination issue

[bxgalleg]

Hi everyone, I have been working on a connection between Power BI and Pipedrive API rest. The pipedrive API limit your query to 500 rows per page, so I inspired in this code to solve my issues. When I work with the entity "Deals", everything works find in the next code:

let 
Source = Json.Document(Web.Contents("https://mycompany.pipedrive.com/v1/deals?api_token=abcde12345",[Query=[api_token="abcde12345", limit="1", start="0", get_summary="1"]])), 
#"Converted to Table Record" = Record.ToTable(Source),
Value = #"Converted to Table Record"{2}[Value],
summary = Value[summary],
total_records = summary[total_count],

//This second part, tries to resolve the maximum limit value of 500 that pipedrive have
//Starts 0, 500, 1000, 1500 until the total records

Starts = List.Generate(()=>0, each _ < total_records, each _ + 500), 
#"Converted to Table" = Table.FromList(Starts, Splitter.SplitByNothing(), null, null, ExtraValues.Error), 
#"Changed Type" = Table.TransformColumnTypes(#"Converted to Table",{{"Column1", type text}}), 
#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Json.Document(Web.Contents("https://mycompany.pipedrive.com/v1/deals?api_token=abcde12345",[Query=[api_token="abcde12345", limit="500", start=[Column1]]]))),

//then is just branding and expanding
#"Expanded Custom" = Table.ExpandRecordColumn(#"Added Custom", "Custom", {"data"}, {"Custom.data"}),
    #"Se expandió Custom.data" = Table.ExpandListColumn(#"Expanded Custom", "Custom.data")
in
    #"Se expandió Custom.data"

This code works fine for the entity "Deals" and also "Organizations" but for some reasson, it does not work with the entity "Notes". I checked for the steps one by one to see the error in steps of the query when working with Notes and it if like the parameter (get_summary="1") is not available on the Notes entity. I'm not exactly sure of how this get_summary works (because as I said before, I inspired in someone else code); would appreciate some explanation of why I'm getting this error. Some print of the error in power bi (power query):

Same step for entity "Deals":

Same step for entity "Notes":

Note: The connection to the entity Notes works perfectly when I uses the web connector without the pagination query.

Thanks for the help!!

Byron - Ecuador

[bxgalleg · Accepted solution]

In this video I explained how I connect to Pipedrive API just using Power BI (Power Query): https://www.youtube.com/watch?v=gnhv4qfJ4yA&t=582s

[cwebb]

It's hard to say what's going on here - as far as I can see the error is telling you that the "Summary" field in the response from the web service isn't found. Maybe the structure of the response is different for Notes?