Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
bxgalleg
Frequent Visitor

Power BI and Pipedrive API - Pagination issue

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":

bxgalleg_0-1654266908900.png

Same step for entity "Notes":

bxgalleg_1-1654266961310.png

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

 

1 ACCEPTED SOLUTION
bxgalleg
Frequent Visitor

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

View solution in original post

2 REPLIES 2
bxgalleg
Frequent Visitor

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
Advocate V
Advocate V

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?

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.