Iterating through Products attached to Deals

Hi there,

I have used the API to pull information to MS PowerBi. I was able to iterate through the pagination of the deals, but with the products I have not found a way. I have cheated and hardcoded a high number into the iteration right now. Do you have any ideas how to pull the last deal id and feed it into the loop?

Starts = List.Generate(()=>1, each _ < 1200, each _ + 1), 
#"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(""&[Column1]&"/products:(id,deal_id,product_id,item_price,sum,currency,add_time,comments,name,quantity)?api_token=xyz",[Query=[api_token="xyz", limit="500", start="0"]])))


1 Like

Hey @marko.hilzendegen
Welcome to the community :wave:

I see the intent however I would request you to consider pagination

You need to fetch entries by pages

Hey @Hem,

pagination does not help her, as pagination yields the count of records, but it does not count deleted ones for example, so technically our count is lower than the highest id, and is the end point is


the count does not help. I would need to find the highest id so that i can wild card the {id} in the iteration.

if i was able to pull all products, pagination would work, but you can only pull the product information (quantity etc) per deal. Another stupid decision made by pipedrive along with the many other stupid decisions.

Here are screenies:

JSON with summary:

Highest id in BI:


Hey @marko.hilzendegen
I see your frustration and it seems to have a reasonable origin. Couple of questions:

  • Which entity are you trying to retrieve - Deals, Products, or both?

    • For Deals, you can use the status query parameter that can retrieve deleted and all_not_deleted entries
    • For Products, you do not have a status query parameter.
      • However, you can listen to deleted.product webhook events. You might need a middleware that keeps track of deleted products.
      • You can also reach out to Pipedrive support for the initial snapshot of deleted products
  • Do you rely on deals/{id}/products just for the sake of getting the deleted products? (and thus you have to take a hacky approach) or are you trying to find products associated with deals as the end goal? :thinking:

    • Deleted products still appear in that endpoint. However, you only need a Deal ID in that case (and from the previous section you can see that you can retrieve all deals, deleted + not deleted)
    • However, It is tricky to build a product master data from that endpoint but again this depends on your end goal.

Overall, I think that you would possibly require a middleware and it may not be a direct approach. I also agree that it is definitely tricky and there’s some room for improvements which I have taken note of :slight_smile: It will be shared with the concerned team.