cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
tthierry
Frequent Visitor

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?

1 ACCEPTED SOLUTION

Accepted Solutions
zoloturu Memorable Member
Memorable Member

Re: API Calls from column

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!

View solution in original post

9 REPLIES 9
Highlighted
zoloturu Memorable Member
Memorable Member

Re: API Calls from column

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.

 

api repeat 1.PNG

 

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

 api repeat 2.PNG

 

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

 

api repeat 3.PNG

 

 api repeat 4.PNG

 

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

tthierry
Frequent Visitor

Re: API Calls from column

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 Memorable Member
Memorable Member

Re: API Calls from column

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!

View solution in original post

tthierry
Frequent Visitor

Re: API Calls from column

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 Memorable Member
Memorable Member

Re: API Calls from column

@tthierry ,

 

It is nice to hear that this approach helped you.

 

Regards,
Ruslan

MariPrydz
New Member

Re: API Calls from column

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
Frequent Visitor

Re: API Calls from column

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.

rbastidas
Frequent Visitor

Re: API Calls from column

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!

amkhullar Helper I
Helper I

Re: API Calls from column

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]

Helpful resources

Announcements
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Join THE global Microsoft Power Platform event series.

Join THE global Power Platform event series.

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors