Excel Forum

Can Excel call an API for row level data retrieval/validation?

Kombarris

Active Member

Members

Forum Posts: 3

Member Since:
July 4, 2016

Offline

June 6, 2024 - 1:26 am

Hi!

I would like to pass various arguments/parameters to an API based on row level cell values. For example, based on a customer’s postcode, retrieve the longitude and latitude, or based on cell value product or customer number, validate the data.

Some years ago, I recall using the Excel functions WEBSERVICE/ENCODEURL/FILTERXML to do this type of thing but after doing some searching, I’m struggling to find any recent tutorials showing how to use these functions. I assume that this is because Power Query has probably replaced these functions.

When I look at Power Query tutorials using web services, the examples are based on returning a list of data, but I want to call the API for each row I have in the spreadsheet and get validation at row level.

Does Power Query support this?

Any advice is much appreciated Smile

Cheers,

Martin

See Answer

Philip Treacy

Admin

Forum Posts: 1614

Member Since:
October 5, 2010

Offline

June 6, 2024 - 12:41 pm

Hi Martin,

Yes Power Query can do this. Create a new Custom Column and for each row call your API.

So if you have a column called Postcode, in your Custom Column code would look something like

=Web.Contents("https://www.api.com/?postcode=" & [Postcode])

That's a very simple example and your case may be more complicated. It depends on how you have to connect to the API, what authentication you need etc.

Another way to do this is to create a function and call the function for each row.

If you can post some more info about the API and how you have to connect to it, I can provide more help.

Further info

Getting Started with API's in Power Query • My Online Training Hub

Web.Contents - PowerQuery M | Microsoft Learn

Regards

Phil