From the course: Power BI Data Methods (2019)

Connecting to web tables

From the course: Power BI Data Methods (2019)

Start my 1-month free trial

Connecting to web tables

“

- [Tutor] We have access to vast online libraries of free public data. We can download this data manually, but web connections eliminate the potential for manual errors and allow us to easily refresh data sources. Tread carefully though, because we do not have direct control over even established external data sources, which could move, delete, or change the format of the data without our knowledge. We have the capability to open web pages as different file types, including text files, CSV documents, Excel workbooks, PDF files, JSON files, HTML pages, and XML tables. HTML stands for hypertext markup language. It serves as the standard markup language for creating web pages. Tags that label pieces of content such as headings and tables represent the HTML elements. When we go to a webpage we do not see the HTML code, but rather the content rendered by this code. Let's see how the web code works for the NOAA Weather Service Station list. We see that this file has a TXT extension on the end. If we right-click on the page and hit inspect it pulls up the code behind the website. We see the HTML code that renders the webpage even though the interface file appears in a .TXT format. We can see where the data element tags go in this webpage when we open up the body. If we connect to a webpage not set up in a format the Power BI has the native capabilities to render we may run into issues. The CDC web portal we looked at earlier also supports other files as well. Here we see the zip code to GEOID mapping in a CSV file format where we see commas separate the data fields. We can also look at the population by GEOcode we earlier downloaded. We will come back to this later when we set up an Excel workbook connection in Power BI. Let's go into Power BI and set up a web data connection. We can type in web, or we can go and look at other where the web option presents itself. Click on the web option. We see where we can put the URL of the web link to the data. We can copy and paste the NOAA station list into our Power BI file. We hit OK. Click connect. Notice essentially that we see a data preview with the data in two columns set up by the fix with delimiter set at position 41. We hit transform data to see what the data table looks like. We still see the commas separating the data fields. So what we need to do is go back into the source step by double-clicking on it to open up the gear wheel. Notice that the file opens up as a CSV document, so we changed this to text file, hit OK. We now see all the data in a single column. We can drag the arrows to expand the column to see all the data points. We will come back later to transform the data to separate it into separate columns. We're going to save this file as NOAA station list, and save it. There we have it, we've connected the NOAA station list online to a Power BI file. We can also set up web connections to the US Census population Excel file we set up earlier by downloading the Excel file. In the US Census Power BI file hit added queries, and we add a new source as a web connector. Let's first set up this mapping of the GEOcode by population. If we double-click on the all GEOcodes link it will download the data. However we can also point it to the path for this file and Power BI will open it up as a web connection. I'm going to copy this URL link and paste it into the URL, and then add the Excel extension on the end. And we hit OK, and connect. One second, we need to go back in and add an S here. So we choose to reconnect again and add an S to the GEOcodes. Again notice the navigator shows us two views to connect to, and this time we're again going to select the second option, which will give us the table without the headers and the unnecessary rows on top. We hit OK. We now see this data connection loaded in the same way that we loaded an Excel file except the connection is not to an Excel file on our desktop, but rather one online. Let's rename this GEOID to county and put a web at the end so we know that it's a web connection, and we're going to use the web connection from here on out for this US Census data. We also need to establish the connection to the zip code to GEOID mapping URL. Copy this web link, and again go into the Census file and select a web data connection again. Paste the link in and hit OK. We see that the Power Query editor in Power BI has already picked up on the commas that separate the fields. We hit OK. And we're going to rename this query population by zip code. And there we have it, we've set up two additional web connections in this particular US Census file, which we will transform from here.

Contents