Forum Discussion
Dynamically calling of API
Hello,
I need to dynamically call API based on table column value.
Table 1:
Id | TokenID |
1 | ABCD |
2 | EFGH |
3 | PQRS |
5 | XYZ |
We need to gather amount information from API against each ID and TokenID.
for example : API – [Https://api.com&id=1&TokenID=ABCD]Https://api.com&id=1&TokenID=ABCD) will return lets say 100.
With help of M Query we need to call API as:
This API will return Amount value and the expected output should be like-
Output:
Id | TokenID | Amount |
1 | ABCD | 100 |
2 | EFGH | 200 |
3 | PQRS | 300 |
4 | XYZ | 400 |
Request you to please help here who have worked with API.
Hi Anonymous
See this PBIX file for a working example.
You can create a function and call the API from inside that, passing in the ID and TokenID for each row.
The code within the function looks something like this
Source = Web.BrowserContents("https://website.com/id="&id&"&tokenid="&tokenid)And for each row you have code that calls the function like this
= Table.AddColumn(#"Changed Type", "API Call", each APICall(Text.From([Id]), [TokenID]))You will of course need to enter the correct website address in the function to reach your API.
You also make no mention of needing authentication to the API so no code for this is included.
Regards
Phil
6 Replies
Hi Anonymous
See this PBIX file for a working example.
You can create a function and call the API from inside that, passing in the ID and TokenID for each row.
The code within the function looks something like this
Source = Web.BrowserContents("https://website.com/id="&id&"&tokenid="&tokenid)And for each row you have code that calls the function like this
= Table.AddColumn(#"Changed Type", "API Call", each APICall(Text.From([Id]), [TokenID]))You will of course need to enter the correct website address in the function to reach your API.
You also make no mention of needing authentication to the API so no code for this is included.
Regards
Phil
Hi Anonymous
After you entered the address of the website you want to connect to, you'd have received this warning
When you cliked on the Edit Credentials button you'd have seen this box
There are several options here, the top one being Anonymous, meaning connect to the website without a username and password.
Did you choose a different option and enter a username and password?
Typically if an API uses authentication, but only allows Anonymous connections, you have to supply the username and password in the connection attempt to the API/website.
This is why in my initial reply I mentioned that you havdn't said whether or not you need to authenticate (supply a username and password) to access the API.
If you do, I can provide the code needed to do this. Please advise.
Regards
Phil
Hi PhilipTreacy
Previously I was able to call API into Power bi through web.
I cleared the permissions in datasource settings after that I am not able to call a Single row.
I am getting the following error.
I haven't given any credentials here but still it is showing couldn't authenticate credentials.
Pl help me how to resolve the issue.
Regards,
Keerthi
Hi Anonymous
Please supply your file/code so I can see what you are trying. You can change the URL to your API before you do this.
I'm still not clear if you need to use a username and password or some other type of authorization to access your API. Do you?
regards
Phil
Thanks PhilipTreacy ,
It is working good now