API Calls from column

[tthierry]

Hi

So here is the challenge, I have an API in the format https://api.workflowmax.com/job.api/get/[id]/customfield?apiKey=[apiKey]&accountKey=[accountKey]

The ID changes for each job and jobs are added all the time.

I have created a column in the format "https://api.workflowmax.com/job.api/get/"&[id]&"/customfield?apiKey=[apiKey]&accountKey=[accountKey]" which returns the API for each instance.

I'd like to get the data using these APIs for each instance but am stuck. I have looked at Dynamic API nested calls, nested API with parameters, multiple API calls as source, and loop API. Nothing seems to be the answer.

Anyone able to help please?

[Anonymous]

Hi i've a curl comand with insecure option to access our splunk(returns unauthorized coz of wrong user name pass)

curl -u user:pass -XPOST "https://splunkeda-api.something.com:8089/services/search/jobs/1" --insecure
<?xml version="1.0" encoding="UTF-8"?>
<response>
<messages>
<msg type="ERROR">Unauthorized</msg>
</messages>
</response>

IF i run the curl without insecure oprion it gives certificate error

curl -u user:pass -XPOST "https://splunkeda-api.something.com:8089/services/search/jobs/1"
curl: (77) schannel: next InitializeSecurityContext failed: SEC_E_UNTRUSTED_ROOT (0x80090325) - The certificate chain was issued by an authority that is not trusted.

So basically am writing a powerquery to fetch data from this api :
url="https://splunkeda-api.something.com:8089/services/search/jobs/1",
param="something",
Source = Json.Document(Web.Contents(url,[Query = [start = param]])),
gives error
An error occurred in the ‘’ query. DataSource.Error: The underlying connection was closed: Could not establish trust relationship for the SSL/TLS secure channel.

How can i mention insecure option(verify=False in python requests) in power query

Please help.

[zoloturu]

Hi @tthierry,

This is a pretty usual case. I have done it many times. Let me share my experience.

1. You need to have column(s) which will store parameters. For instance Column1 and Column2.

2. Then you create a custom function in Power Query (M). Which will receive Column1 and Column2 as parameters and output will be a result of your API call. 

it should be like below example:

(Param1 as text, Param2 as text) =>
let 
    Concat = Param1 & Param2,
    Result = Text.Length(Concat)
in
    Result

 

Where

* Param1 and Param2 are aliases for function input parameters (free text names)

* Concat and Result steps you need to replace with your API call via Web.Contents

Here is an article about how to create custom functions in general - https://blogs.msdn.microsoft.com/mvpawardprogram/2013/08/19/creating-power-query-functions/.

3. Invoke this function to each row.

Menu -> Add column -> Invoke Custom Function, select function name and parameters

 

Regards,
Ruslan
-------------------------------------------------------------------
Did I answer your question? Mark my post as a solution!

[tthierry]

Hi @zoloturu

Thank you. I am not trying to concatenate the two columns as I already achieved this with "https://api.workflowmax.com/job.api/get/" & [id] & "/customfield?apiKey=[apiKey]&accountKey=[accountKey]" 

What I want is to call the API that I have generated in a sinilar way you expand a column that has table in it.

Any chance you know how to play that trick please?

Thanks

[zoloturu · Accepted solution]

Hi @tthierry,

I've tried this earlier. You need to follow below logic:

let
...
PreviousStep = ... ,    

Step = Table.AddColumn(PreviousStep, "NewColumnName", each Json.Document(Web.Contents("https://api.workflowmax.com/job.api/get/" & [id] & "/customfield?apiKey=[apiKey]&accountKey=[accountKey]"))),

in Step

Regards,
Ruslan
-------------------------------------------------------------------
Did I answer your question? Mark my post as a solution!

[MariPrydz]

Hi Ruslan, 

I was looking for this exact same thing, but I'm a powerBI noob; where do you run this script? From a blank query? 

Thanks a lot! 

/Mari

[tthierry]

Hi @MariPrydz 

Yes. Basically I created a function to call the API and I created a query that calls that function for each API that is created based on my database.

[Anonymous]

Did you have issues with the id being a number and the api a text? I have the same challenge that you initially had. 

thanks!

[tthierry]

Thank you so much. I had to tweak it but it worked. The tweak was to use "each" instead of

each Json.Document

 Sorry for only accepting the answer now, I was working on other things in tyhe mean time. Thanks a lot.

[zoloturu]

@tthierry ,

It is nice to hear that this approach helped you.

Regards,
Ruslan

[Anonymous]

I Am also trying a similar scneario where i want to get output of API as a column in a new table.

let 
    Source = Table.SelectColumns(#"Sub Tasks",{"JIRAID"}),
    WorklogUrl = "https://jira.company.com/rest/api/2/issue/",

    GetJson = (Url) =>
        let 
            RawData = Web.Contents(Url),
            Json    = Json.Document(RawData)
        in  Json,
        
    GetWorklog = (JIRAID as text) =>
        let Url   = WorklogUrl & [JIRAID] & "/worklog",
            Json  = GetJson(Url),
            WorkLog = Json[#"worklogs"]
        in  WorkLog,
            
    JiraWorklog = Table.AddColumn(GetWorklog, "Worklog", each GetWorklog() )
in
    JiraWorklog

But i get this error at last step

Expression.Error: We cannot convert a value of type Function to type Table.
Details:
    Value=[Function]
    Type=[Type]