cancel
Showing results for 
Search instead for 
Did you mean: 
Reply

Get Value From Web API Using Value From Column

Hey guys,

 

I need help with getting result back from a web api. The data which I need to pass to the api is stored in a column and I need a new column to be populated with the result from the web See the example below (BSID is what is available and Group is what I need to get from the Api using the URL https://api.macvendors.com/"BSID")

 

BSID - Group

FCA13E  -  Samsung Electronics Co.,Ltd

8841fc - AirTies Wireless Networks

 

Example of a call would be

https://api.macvendors.com/FCA13E

 

Thanks

 

Moiz

6 REPLIES 6
ssugar Member
Member

Re: Get Value From Web API Using Value From Column

In the Query Editor, add a Custom Column (Add Column > Custom Column) with the following:

Csv.Document(Web.Contents("https://api.macvendors.com/" & [BSID]))

 

After that, you'll want to expand the column, then merge the column with "Space" as the separator, and finally rename the column to Group. 

 

My pbix file: https://github.com/ssugar/PowerBICommunity/raw/master/community-sol-279591.pbix

Highlighted

Re: Get Value From Web API Using Value From Column

This seems to work, the only issue I am having now is that the example was 2 rows whereas in reality there are 1000s of rows.

ssugar Member
Member

Re: Get Value From Web API Using Value From Column

Shouldn't be a problem running this against 1000s of rows.  I've run something similar across tens of thousands of rows.  The main potential issue you'll face is rate limiting from the API.    If you run into something like that, try this:

https://blog.crossjoin.co.uk/2015/04/30/using-function-invokeafter-in-power-query/

Community Support Team
Community Support Team

Re: Get Value From Web API Using Value From Column

Hi Moiz,

 

Could you please mark the proper answers as solutions?

 

Best Regards,

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: Get Value From Web API Using Value From Column

Hi Dale,

 

Haven't found the proper solution as yet but also did not get time to look into it further.

 

Moiz

ssugar Member
Member

Re: Get Value From Web API Using Value From Column

Could you provide us the raw data (1000s of rows) that you're looking to do this for?  Then we could hopefully help you get to a working solution.