Hi, this is Wayne again with a topic “Intermediate Guide to Excel Get & Transform / Power Query”.
In this intermediate guide to excels, get and transform tool I’ll show you how to get data from the web and transform it in Excel. You can see here on sheet number three. I have a couple of website URLs and you should have access to this same document that I’m using. If you look in the description below the video there’s, a link for it and the links. Take you to these two wikipedia pages and both of these wikipedia articles have tables in them that I would like to bring in to my Excel workbook.
To do that. All I have to do is click on the URL to highlight it and then hold ctrl and tap C to copy the URL and then I’ll jump back into Excel and I’ll go to the data. Tab and I’ll look here and the get in transform data group and I’ll just click from web. It opens up this new window and I can simply paste in the URL that I copied from Wikipedia. I can click, OK and a window opens up.
This is the navigator and the navigator will help me to choose what part of that Wikipedia page. I would like to get and then transform in my Excel workbook. So look at what it gives me.
It says accolades. It says critical response document episodes and then it’s got a whole bunch of tables below that. Now, where did it get this information? Well, it got it from the URL that I provided. If I go back to Wikipedia, I can see this better.
If I go down to episodes, for example, there’s a list of each season of marvel’s agents of shield the number of episodes when it aired and the viewership. So let’s say that’s exactly the data I would like to import. I just need to take note of the label, for that section. Episodes go back into Excel and look for it there. It is, I can click on it and if it’s perfect the way it looks here, I can just click load and it will be added to my workbook, but I want to make some changes to the data so, instead of clicking load, I’m going to click transform Data that takes me to the power query editor window and let’s take a look at the data. The data looks pretty good to me, but there are some things that are confusing about it.
For example, this originally aired and then episodes is repeated and seasons repeated. If I look back at the Wikipedia article, it becomes more clear. This originally aired sell, is confusing things and is making it so that season gets repeated episode gets repeated, ranked probably gets repeated. So let’s look at how to fix that. So I don’t really need that.
First row of data: it’s repetitious, I don’t really need this, originally aired, sell and everything else is really repeated. So I’m going to click on Row 1 at the bottom of the window. It lists out everything, that’s included in Row 1 and then I’ll just go up here on the Home tab and I’ll.
Just click remove rows, there’s an option to remove top rows bottom rows or alternate rows and some other options below I’m gon na go with remove top rows. Now I need to specify how many rows just the one top row. If I were to put 2 it would remove the top two rows.
I’Ll just click. Ok, row number 1 is now gone now: let’s remove column 1. That’S all so unnecessary data, so I’ll make sure that I’ve selected column, 1 and click remove columns and look here’s another unnecessary column. Now this is pretty common whenever you’re pulling data from the internet or from other sources that are not natively from Excel. It’S common to have unnecessary data or repetitious data or other unnecessary artifacts come from that original data into Excel, and so that’s what we’re doing here is we’re trying to clean it up. So this is totally unnecessary. It’S just a repetition of column 2.
So again remove columns it’s gone. Let’S look at the rest of the data. Everything else looks pretty good now, just like I showed in my beginners guide to excel, get and transform over here on the right. I have a list of all of the applied steps that I’ve made as I’ve tried to clean up this data.
If I regret any of them, I can just click the red X to the left of that step, and it will undo that step in the previous video. I also showed how you can transform data by going to the transform tab and using tools like format, to change the format of whatever you have selected in these ways, or you can also use replace values to help clean up your data. Now, there’s one more change. I want to make before I add this data to my workbook, and that is this look.
The headers don’t quite match what I want. I would like the first row to actually act as the header, so to do that here on the Home tab home ribbon. I can just go here and click use first row as headers. Now the data looks just how I want it to look and I’m ready to add it to my workbook.
The way you do, that is just by going to the Home tab and clicking this close and load button now clicking the top part of that button will almost instantly add this data, that’s from Wikipedia into my workbook. If I click the bottom part of that button, it gives me an option to close and load the data into other places or in other ways. For now, though, I’ll just click close and load and the table that was in Wikipedia has been transformed and then loaded into my workbook, and it was added as a new sheet she at 6:00. I can now right click on that and rename it. I could name it something like episodes of agents of shield tap enter and this data is ready to be used in Excel thanks for watching.
I hope you found this tutorial to be helpful. If you did please like follow and subscribe and when you subscribe click the bell so that you’ll be notified whenever I post another video, if you’d like to support my channel, you can do that through my patreon account and you’ll see a link to that in the Description below .