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 ...
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!

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!

  • tthierry's avatar
    tthierry
    Frequent Visitor
    7 years ago

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

      tthierry ,

       

      It is nice to hear that this approach helped you.

       

      Regards,
      Ruslan

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        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]
  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

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

      Hi Anonymous 

       

      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's avatar
        Anonymous
        Not applicable
        7 years ago

        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!