Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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?
Solved! Go to 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!
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.
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!
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 @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.
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!
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.
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]
User | Count |
---|---|
94 | |
84 | |
78 | |
75 | |
66 |
User | Count |
---|---|
115 | |
105 | |
93 | |
65 | |
60 |