Analyzing Data from just about any site using Power BI
I stumbled upon an interesting application called Kimono that lets you convert data available on any website to an API. One you define what data you want to extract from a page and make it available as an API that returns JSON, you can then use Power Query to pull data into Excel and do some interesting analytics. For testing this out, I decided to use a web page that I use often to monitor weather conditions in Lake Washington: it’s a weather station in Sail Sand Point listed in Weather Underground: KWASEATT232
I typically use the temperature, windspeed and gusts data to make decisions on windsurfing in the area. The first step was to create an API that would return the current data using Kimono. To do this, I created an account at https://www.kimonolabs.com/
Once I did that, I installed the Bookmarklet as documented here: https://www.kimonolabs.com/learn/getstarted .. The bookmarklet (the Chrome extension if you are using Chrome), lets you define the data that you want to extract from a page and make available through the API.
Once I had the bookmarklet in place, I went to the Weather site and clicked on KImonify. Check out this video to better understand how to define the fields:
I used the processed detailed in the video above to select Temperature, Windspeed, Gusts, Sunrise and Sunset times as fields for my API. This is what the fields looked like:
And this is what the JSON data looks like:
When I save the API, Kimono turns it into a custom API that can be called from Power Query for Excel
This is the API that I created: https://www.kimonolabs.com/api/a4q3pu3o (you will need to create your own account on Kimono to get a key to use the API)
My next step was to launch Excel 2013 and test to see if the API can be called from Power Query:
The Power Query Editor lets me select and transform the results if needed:
Here is an example of the data from the website extracted via an API and pulled into Excel using Powr Query. This data can be refreshed automatically in near real time.
There are some interesting possibilities of pulling in product & price data into Excel using this method. Would love to hear of other ideas. Here is a retail example:
[View:https://www.youtube.com/watch?v=zhiFltpNWIY]