Over 2 days last week, we were introduced to PowerBI. PowerBI is a business intelligence tool made by Microsoft to prepare, analyse and visualise data. PowerBI shares similar functionality to Tableau and Alteryx. It incorporates features of both into a single product and more. For example, data modelling is a useful feature available in PowerBI but not Tableau or Alteryx.
In the beginning, it was difficult to get started in PowerBI after being trained in Tableau and Alteryx for 3 months and being so used to their user interfaces. However, there was one feature in PowerBI that I wish existed in Alteryx. This feature is easily extracting HTML tables from a webpage directly into PowerBI in a table format. This feature is extremely easy to use and saves a lot of time.
Last week, we were challenged with creating a PowerBI report in a short timeframe. I took advantage of this feature to save time sourcing my data. In this blog post, I will demonstrate how to use PowerBI to extract HTML tables into a format ready to analyse.
The first step is to find a table on the web, preferably one that has a simple format. For example, I was looking to analyse cryptocurrency prices over the last year and found the following table on the web with no download option.
To get this data into PowerBI, copy the URL. Then open PowerBI and select the Web option under Get Data.
A window will pop up and request a URL. Paste in the URL that was previously copied and click OK.
PowerBI will go to the website and detect any tables present in the webpage. This process may take a few seconds.
Once PowerBI has finished loading, the tables detected will show up in the left sidebar. Check the tables you want to import. If there is no data transformation or cleaning, press Load. Otherwise, click Transform data to clean and transform the data. Once the table is loaded, you can create your report in PowerBI.
This feature saves a lot of time compared to the regex solution that is commonly used in Alteryx to scrape tables from the web. It also doesn’t require any knowledge of regex which is useful for beginners. I hope you found this feature helpful.