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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

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
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.