Get Value From Web API Using Value From Column

[moizsherwani]

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

[v-jiascu-msft]

Hi Moiz,

Could you please mark the proper answers as solutions?

Best Regards,

Dale

[moizsherwani]

Hi Dale,

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

Moiz

[ssugar]

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.

[ssugar]

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

[moizsherwani]

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]

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/