Hello everyone, it has been a while since I have posted any blogs. I am going to hop back into it with a simple one where I will show how to do an ETL Process on Web Data using Power BI. Here is a link if you would like to follow along: https://www.msn.com/en-us/money/watchlist?id=a1sj9c&tab=TopGainers. First up you are going to open Power BI Desktop. Once you get in you should see your Report View like this:
Then you are going to want to click one of two things on this screen. First is the Get data from another source button which is in the red box. Or you will click the Get data button which is in the green box.
Once here you will be prompted with the Get data dialog box. You’re going to want to click on Other in the red box.
Now you will see the top option of Web. Go ahead and click on that and then click connect down at the bottom of the dialog box.
Once you do that you will see the From Web dialog box. In here you are going to want to paste in that URL (or whatever URL you are trying to import from.)
Now you are going to want to click OK where you will then be taken to yet another dialog box, the Access Web Content dialog box. I am just going to stick with anonymous but if you need to sign into whatever web page you are accessing data from you can use the other options (Windows, Basic, Web API, Organizational Account). We’re just going to click on connect now.
Once in there, you will be prompted to possibly import tables found on the web page or even the code of the page or the displayed text. For this blog I am just going to use Table 1 and then I am going to click Transform Data (in the red box) to take us to the Power Query Editor directly.
Now for a couple different transformations. First I am going to show you one of the ways you can change the columns. You are going to want to right click on the column you want to change and click on rename which I put in the red box.
Now we are going to change the data types of a column. (In between here I realized that the data kept erroring out whenever I tried to change the data type. My solve was to remove the 3rd column after copying all of it’s data into Excel. Then I created a column from examples and then copied the data into that. Then I renamed the column and then put the column back in its place). One way that you can do that is by clicking the Data Type button at the top of the banner (in the red box), and then I selected Fixed decimal number for this value since it represents money (it has the red arrow pointing to it).
But what happened to all of the decimal places, we still want those that had to go to 4 values behind the decimal to be taken into account. So we are going to right click the column go to Transform, Round, then Round… (in the red box).
Now you’ll have this dialog box where you will need to enter how many decimal place you want so I am going to enter 4 and then click OK.
So now the displayed value has been rounded from 4 decimal values.
Now, all you need to do to get this data into your report is to click on the Close & apply button at the top left of the screen (in the red box).
And that’s a wrap. We started off by getting the data into Power BI (Extracting the data) from the website, then we made changes to the data (Transformed the data) and then we Closed the Power Query Editor and applied the changes (Loaded the data) into our report.
Thanks everyone for reading this. It was short and sweet but I hope it helped one of you. Have a great rest of your day.
Bailey McDonald
Data Engineer, Patriot Consulting
Email: bkmcdonald@patriotconsultingcorp.com | Blogs: Patriot Consulting Blogs
LinkedIn: Personal: BaileyMcDonald | Company: Patriot Consulting
