Forum Discussion

tthierry's avatar
tthierry
Frequent Visitor
7 years ago
Solved

API Calls from column

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?

Need Help
  • zoloturu's avatar
    zoloturu
    7 years ago

    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!

10 Replies

  • zoloturu's avatar
    zoloturu
    Memorable Member
    7 years ago

    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's avatar
      tthierry
      Frequent Visitor
      7 years ago

      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's avatar
        zoloturu
        Memorable Member
        7 years ago

        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!

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    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.