Power BI X Pipedrive : Expand pagination data doesn't work

[Wasabiii]

Hi everyone,

Started to use Pipedrive, and saw that the reporting is really rudimentary... so i use to collect data and put them into Power BI.

It's working thx to these tutorials : 

- https://www.youtube.com/watch?v=gnhv4qfJ4yA&t=582s

- https://community.fabric.microsoft.com/t5/Developer/Power-BI-and-Pipedrive-API-Pagination-issue/m-p/...

Now, i'm blocked because i don't collect all the datas from Pipedrive for the deals, as you can see below, for the leads (prospect), i can return exactly the number of rows (1045) but with deals, i return the first 500 rows, multiple times (6000 rows but 12 duplicates * 500).

Leads :

Deals :

And on Pipedrive, i have currently 582 deals, so the pagination doesn't work.

I have no idea where the problem is coming from, it's very vague to you but I hope someone has a solution for this problem.

I don't think I can give more details but if necessary, don't hesitate to ask me.

Thank you,

Camille

[janunez]

Hello @Wasabiii !

You can use our open source Power BI connector for Pipedrive.

All code is available for free on GitHub here:
https://github.com/audoxcl/Power-BI-Pipedrive-Connector

And here are the instructions on how to install and use it:
https://www.youtube.com/playlist?list=PLI3w6pdNV1sQfDFH3mKRbjYWH0zZjxEMX

If you have any questions or concerns, you can contact us here:
WhatsApp: +56 9 9675 0572
Email: info@audox.com

Javier Nunez
CEO & Founder
Audox
www.audox.com

[Anonymous]

Hi @Wasabiii ,

You can try the following steps to fix the problem:
1. Ensure that you are implementing paging correctly in the API request. You will need to adjust parameters such as start and limit to fetch subsequent datasets.
2. Ensure that the Power BI query is set up to handle paging correctly and avoid fetching the same set of records multiple times. You can use the start parameter to specify an offset for the next set of records.

Best Regards,

Ada Wang

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

[Wasabiii]

Hi @Anonymous 

I checked again, it's exactly the same script compared to my other request (Leads, person, activities...).

For pagination, i use this step : 

So, on this screenshot, there is just only one little error, before taking the screenshot i changed the column1 from 500 per 500 to 100 per 100 but i didn't changed the limit. Otherwise, doing this step with the other requests don't create this problem when retrieving the data.

Below the entire script : 

//A basic direct connector to pipedrive api

//The [Query] syntax is used because PowerBI doesn't work well with api tokens by QueryString

//This first part will get all the total deal records

let
Source = Json.Document(Web.Contents("https://REQUEST_URL//v1/deals?api_token=TOKEN",[Query=[api_token="TOKEN", 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]*10,

//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 _ + 100),
#"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://REQUEST_URL//v1/deals/collection?api_token=TOKEN",[Query=[api_token="TOKEN", limit="100", start=[Column1]]]))),

//then is just branding and expanding
#"Expanded Custom" = Table.ExpandRecordColumn(#"Added Custom", "Custom", {"data"}, {"Custom.data"}),
#"Expanded Custom.data" = Table.ExpandListColumn(#"Expanded Custom", "Custom.data"),
#"Expanded Custom.data1" = Table.ExpandRecordColumn(#"Expanded Custom.data", "Custom.data", {"id", "creator_user_id", "user_id", "person_id", "org_id", "stage_id", "title", "value", "currency", "add_time", "update_time", "status", "probability", "lost_reason", "visible_to", "close_time", "pipeline_id", "won_time", "lost_time", "expected_close_date", "label"})
in
#"Expanded Custom.data1"

I guess iwill contact the Pipedrive support, because if it succeed with other Request, maybe it's Pipedrive which create the problem. Or i still don't see my error 🫥

Thank you,

Camille

[Wasabiii]

To perhaps provide more details, below is my API request.

It is identical between leads and deals in the way it works