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