Power BI and Pipedrive API - Pagination issue

bxgalleg — Fri, 03 Jun 2022 14:53:47 GMT

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

Re: Power BI and Pipedrive API - Pagination issue

cwebb — Sat, 04 Jun 2022 11:19:17 GMT

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?

Re: Power BI and Pipedrive API - Pagination issue

bxgalleg — Mon, 26 Sep 2022 19:43:53 GMT

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

topic Re: Power BI and Pipedrive API - Pagination issue in Developer

Power BI and Pipedrive API - Pagination issue

Re: Power BI and Pipedrive API - Pagination issue

Re: Power BI and Pipedrive API - Pagination issue