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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.