Power Query Overview - Automate Data Tasks in Excel & Power BI

hey there it's John from Excel campus and in this video I'm going to give you an overview and demo of power query and explain how it can help save you a ton of time with your job so this is the power query editor here but before we dive into that I want to explain what power query is exactly so power query is a data automation tool it allows us to import data into Excel from external sources this could be databases CSV files Excel files and even the web and it has a ton of features that allow us to cleanse and prepare the data we can also combine and stack data this is called an append in power query or join datasets together create relationships between them it's called a merge and power query something you might use something like a vlookup for we can also group and summarize data with power query something similar to a pivot table and power query is available on Excel 2010 or later for Windows and it's also built into power bi so it's a tool we can use very very frequently if power query was a superhero I like to think of them as Superman because it's someone we can call on all the time to do a ton of different tasks in Excel however power query has gotten a bad rap it's gone through a name change and I also like to think of power query as Superman and an ugly Christmas sweater so sorry to all your Superman fans out there and Chewbacca fans as well but power query has not only gone through a name change it's also fairly difficult and confusing when you first see it the user interface is a bit challenging so today I'm going to help demystify it a bit and hopefully help you learn exactly what power query is the process behind it and how you can use it in your job so there's really three steps to power query and the first step is that we're going to again get data from almost any data source this could be Excel CSV files any types of databases you can connect directly to with power query and even websites and web sources we then transform the data with power query and again this is a tool that's now built into Excel it's built by Microsoft and it's part of Excel and power bi so we transform the data with the power query editor which was that user interface that I showed at the beginning and we'll dive more into that and then we output the data to excel or the data model so that's really the three-step process now there's kind of a four step here which makes power query and magical and that's that we can automate our entire process / just refreshing the query and we're going to see an example of that today and this is where the time savings can really come in and add a ton of value to your job so let's go ahead and dive in and look at a demo of power query alright so for this task we are going to combine a bunch of CSV files with power query so I have this file here that explains the steps we're going to do and the first thing we're going to do is combine the CSV files so I have those in a folder here there's a file for each division and each of these files contains order data so each row within this data set here contains data for an order a specific order for this division as you can see there's a lot of rows here and we want to combine all of these files together or stack them on top of each other and we're going to use power query to automate this process completely so first thing we're going to do is open power query now if you're using Excel 2016 or later it will be on the data tab of the ribbon for using Excel 2010 or 2013 you'll install an add-in and it'll say power query right here I have another article that explains how to do that more detail and I'll put a link to that in the description below this video so here is the power query section again called get and transform data and we're going to click this drop down to get data and as you can see there's a lot of different places we can get data from and get it from files we can get data from different databases your online services other services like the web or other sources so there's a ton of places we can get data from for this particular example we're going to get data from a folder we're going to combine all the files in that folder so I chose from folder that's going to prompt me for the folder path I'm going to open Windows Explorer again I'm just going to click up here in the address bar and you can right-click copy or control see back over to excel and then paste that right here and hit okay you can also browse from it from it from that window if you'd like and then that's going to just bring up this preview window that shows a list of all those files there in that folder and down here we have some options and on the combine drop-down we're going to choose combine and transform data so that'll take a second here to load up and evaluate the query and then we'll see this combined files window here's where you can choose some options for the CSV file if needed power query is usually pretty good at figuring all this out so at this point we'll just hit OK and that will take a few seconds to load up the power query editor and within the editor here there's a lot going on this is a new window that comes up on top of Excel we have our queries over here on the left side and this is our data query power query creates some additional queries here when we do the combine I won't worry about those right now but some pretty cool stuff there so I'm going to collapse that pane and here we have a preview of our data so in the first column we see the source name of the file and then we have all the columns of data from these files and again these are already stacked on top of each other here so if we hit the filter drop down here load more we can see that we have all the data here for all of our different files and again we have a preview here over here on the right side and the query settings pane we have the name of the query and then the applied steps so power query has already applied some steps for us but as we take some additional steps here keep an eye on this pane over here because this will list out the additional steps we take and then up here at the top we have a lot of different buttons for different transformations or actions we can take on this data so the first thing we want to do to clean up our data here is to remove a few columns over here on the right side we have some purchaser information and these columns out here to the right we don't actually need these for our reporting so I'm just gonna left click this first column hold the shift key left click the last one to select all of those and then on any column I'm going to right-click it and choose remove columns the right-click menu here it also has the buttons a lot of these buttons up here and Toolbar as well I'm just gonna right click remove now when I do that as you'll see those columns have now been removed we've also added a step over here to remove columns but this is not actually removing the columns from the source data from the CSV files it's important to know and understand that with power query it's not touching our source data it's just doing these transformations here in memory in the power query editor and it's going to run those transformations and output new data to excel or the data model so we're not actually touching those files it's just good to know we can also remove rows or delete rows and you do that by filtering in power query so I don't need product ten in my results so in the product filter drop down here for the product column I'm just gonna uncheck ten because we didn't do not need ten hit OK and that will remove all the rows for product ten now one last transformation we're going to do is clean up this order date column so if you can see here these are dates but power query does not recognize these as a date datatype if we go here to the data type click this menu and try and convert this to a date you'll see we get some errors here and we don't want that power query is just not recognizing that format as a date however there's a very cool feature in power query that allows us to do this transformation very easily so the first thing I'm going to do though is I'm going to delete that step and any of these steps over here that we can create or that we've created we can also delete so I'm just gonna click the red X there to remove or delete that step and now what I'm going to do with this column selected I'm going to go to the add column tab up here and again more buttons here with different transformations and we're going to choose this one column from examples I'm going to hit the drop down here and choose from selection so from the selected column the order date column and this is a very magical feature of power query it should be called something better than column from examples because it's a really cool feature that allows us to do data transformations so here in column one we can see this windows come up I also get some instructions here enter sample values to create a new column so in column one I'm just going to double click here in the first cell and all I'm going to do is type the date like I would normally see it or a date format that Excel would recognize so this is currently day month year now I'm in the US so I'm going to do month day year so five slash 24 slash 2018 I'll hit enter then I'll go to the next cell and do the same thing so this vo 7/14 slash 2018 and I hit enter again and once I do that power query has recognized the pattern of the transformation that I'm trying to do this text transformation and as you can see here it fills down all the rest of the dates for me so it's basically done this transformation it's also created a formula and you can see that formula right here for this transformation so this might look similar to an excel formula however it uses a formula language called M the letter M or M code in power query so a different formula language but it's a functional based language very similar to excel and it's created all of that work for me so I don't have to do any of this work I'm just going to hit OK here to confirm that change and that'll add my new custom column here now I can double click on the column header just quickly rename that to date and then I can even go now if I don't need this original date column anymore I could right click and remove that column again it's not going to affect anything there the new date column will still show the results here and that's because power query goes in order of these steps that you see here on the right side and you can also click any of these steps to jump back and see a preview of what the data look like at that ad that's specific steps as very cool tool here and this is our automation all of these applied steps so the next thing we're going to do we're done with our transformations we're going to go to the Home tab and we're going to click this close and load button this is a split button and we're just going to click the top half of it here to do a closed and load and what that does is it's going to add a new sheet to our workbook since this is a brand-new query and then output the data in an Excel table so here is the results of our query in this new sheet so now let's talk about updating and refreshing the date and this is really where the power of power query comes in with full automation so we've essentially fully automated this process and what I mean by that is we can we have this folder with these five files in it now let's say we get some new data from a new division or maybe you get data every week or every month with a new file and you just want to add it to this existing table well this is extremely easy with power query so I have a folder here with some more data I'm just going to copy these files here and then we'll go back over to my data folder and then we can paste them in there I'm going to hit control V to just paste those two new files in the folder so we now have two new files for division six and seven all we need to do to update our query with that data is right-click the table and hit refresh right click refresh that's going to rerun the query and now output this new table with our data so if we hit the filter drop down here up sorry that's off your screen I'll just go down to the bottom and you can see we now have data for divisions seven and division six above that so just a simple refresh will rerun all the steps in our query and output it to this table so it's important to know that when you do the refresh power query is going to essentially replace all the existing data in the table with the new outputted data from the Refresh of the query so if you were to replace all of the files in this folder here or delete some of the files and then refresh you would only see the data in the table for the files that you have in the folder but again the amazing part here is that we've fully automated this process in a matter of minutes and now the instructions for updating this is simply to export your files from whatever system they come from and save them in this folder and then right click refresh your queries keyboard shortcut for that is alt f5 and of course there's a refresh button up here on the data tab as well and if you have any pivot tables that use this as their source data you can refresh those as well and all of your reports and formula based reports will be updated as well so the reason I showed a picture of a donut at the beginning is I like to think of power query as one of these donut machines if you've ever been to Krispy Kreme or any other donut shop you might have seen a machine like this where you just kind of put ingredients in one end and outcome these perfect donuts on the other end and that's exactly what power query does as well it takes our data and not only automates that process but does it with consistency which saves us a ton of time and also helps reduce errors and this can also be used as an alternative to VBA it does not by any means replace VBA but there are tasks you can use power query for instead of EBA and of course it's packed with a ton of features but it has its own nuances as well there's always a lot to learn with every Excel tool and power query is no exception so within the data analysis process and this is kind of a three step process again where we get in transform data use power query for that calculate and analyze main pivot tables and formulas and then visualize and present with charts and dashboards where do you think you spend most of your time in this process yeah well if you said number one you are correct recent Forbes articles survey data analysts and round that 79% of our time is spent just collecting and cleaning up data so our job really looks like this or it can look like this especially if you're doing the data cleanup process manually so power query can save us a ton of time with this step and allows us to then focus on the more exciting things frees up our time to focus on the more exciting and fun things like calculating and analyzing the data and presenting it some of the more creative aspects of our job so power query is just one step in the what I call the modern Excel blueprint we have a ton of tools at our disposal today and I currently have a free webinar going on that explains all of these tools in more detail it's called Excel blueprint it's a free webinar and I'll put a link in the description below this video where you can get registered for that and check out this training that goes through all these tools which will help save you a ton of time with your job and also make you an excel hero in your organ is station so I hope you've enjoyed this of course if you have any questions please leave a comment below as well thanks again for watching have a great day and I'll see it in the next video [Applause]

Channel: Excel Campus - Jon

Views: 224,383

Rating: 4.9741254 out of 5

Keywords: excel, excel campus, Power Query Overview, power query, excel power query training, excel tips and tricks, power query functions, power query tutorial, power query function power bi, microsoft power query, power query explained, how to use power query, introduction to power query, power query for beginners, data automation in excel, get and transform, get and transform data in excel, power query combine multiple excel files, combine csv files in excel

Id: vq9AgAtSvQg

Length: 15min 51sec (951 seconds)

Published: Thu May 14 2020