Doubts about the URL - Githubissues

byrongdrums commented 2 years ago

Hi davser, thank you a lot for this code. I'm trying to adapt it to my case but don't know if it's exactly what I'm trying to extract.

Your URL example --> Source = Json.Document(Web.Contents("https://mypipedrive.pipedrive.com/v1/deals:(id)?api_token=12345",[Query=[api_token="12345", limit="1", start="0", get_summary="1"]])),

My URL example --> Source= Json.Document(Web.Contents("https://mycompany.pipedrive.com/v1/deals?limit=500&api_token=abcde12345")),

Don't understand the :(id) in your code and also the :(user_id,org_id), What does it do?.

What I need? --> Get all the deals one by one in rows and its fields (name, value, e.g)

Hope you can help me to understand the code, I'm really new in this API world by the way.

byrongdrums commented 2 years ago

This query works for me in case you're looking for all the deals (inspired in davser code):

Note: Sorry, my pbi is in Spanish on the steps name but don't think it's going to be a problem :(

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"

byrongdrums commented 2 years ago

I have proove this code for Deals and Organizations and works but for some reasson, I'm not able to make it work for Notes (for example), I'm trying to solve it, will be right back here if I solve it

davser / PipedriveTOPowerBI

Doubts about the URL #6