Calling curl from within Power Query?

Dan Bliss

New member
Joined
Dec 7, 2016
Messages
45
Reaction score
0
Points
0
Excel Version(s)
Office 365
From an online CRM, I hope to extract the names of contacts associated with each sales opportunity. The CRM has an API command to extract "parties" (contacts) associated with a given opportunity using curl. Here's the code which runs from a command prompt on a Windows 10 PC:
Code:
curl -H "Authorization: Bearer {Key}" -H "Accept: application/json" https://api.capsulecrm.com/api/v2/parties/{partyId}/opportunities
where:
{key} is a unique token which the CRM assigns to an account for secure access.
{partyId} is the unique key field identifying a party.


First I loaded the Party table, which has a unique field, party.ID (also name fields, address fields, email, phone, etc.)
Using the party.ID, I had hoped to write a Power Query expression, which included something like the following:

...
prestring = "curl -H ""Authorization: Bearer {Key}"" -H ""Accept: application/json"" https://api.capsulecrm.com/api/v2/parties/",
#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", Expression.Evaluate("prestring & each [party.ID] & ""/opportunities""", #shared))
in
#"Added Custom"

Not surprising that #Shared environment would not be able to access a cmd.exe environment: = Expression.Evaluate("echo", #shared) does not work.
Please note the quotes in the Expression.Evaluate(string) are not correct, but I ran out of patience trying to get them right.
Any thoughts on how to do this?
Maybe can't be done in PowerQuery?
 
Last edited:
Back
Top