Web APIs and Power Query

How to use web APIs and power query in Power Desktop BI to get data into an Excel worksheet for further analysis. Watch the video below:

Get Jason Data Using Web APIs into Excel Using Power Query

The full form of API is Application Programming Interface. An API works as an agent between two software applications and allows them to interact with each other. For example, if you check the weather on your phone you are interacting via an API. It’s the same when you use Google maps. You must have heard of Akismet which is used to protect your WordPress website from spam. The Akismet API key is free. So, to an API to connect to databases of other enterprises we need an API key. Sometimes the key can be free as in the case of Akismet or NASA. But for using Google or Bing maps and Google or Microsoft translate you need to pay for using their databases via an API key. That is one reason you should keep your API key a secret.

You can also imagine an API key as the gatekeeper of a cinema hall. The ticket with the seat number is the API key. The gatekeeper allows you to enter the cinema only if you have purchased the ticket aka API key.

Now the data that other enterprises provide the user can be in different formats like HTML, XML or mostly JSON (JavaScript Object Notation). Now Excel VBA cannot parse (understand or translate) JSON and therefore you may need to install the JSON converter for Excel VBA. You can get the VBA Jason Tool from here. If you wish to avoid all these hassles you can use power query in Excel as shown in the video.

If you try to get an API key from Twitter, you would wish you had never tried that!

APIs open paths to integrate data and services across platforms and devices

Further reading:

Next-Generation APIs