How to easily automate boring Excel tasks with Power Query!

talk to anyone who has used power query and they'll tell you how amazing it is stories of automating tasks that used to take three hours now taking only three minutes is not uncommon or an exaggeration now if you haven't heard of excel's power query tool or you've heard of power query but you're not sure if it'll be useful to you then stay tuned because i'm going to showcase what the fuss is all about before we get started i want to reassure you that everyone on excel 2010 or later has access to power query because it's a tool built by microsoft for excel and power bi it's the same tool in both apps so you only need to learn it once and you can use it in either app now if you have excel 2016 or later you'll find the power query tools on the data tab of the ribbon in the get and transform group of tools if you have excel 2010 or 2013 then you can download the free power query add-in and your power query tools will have their own dedicated tab in the ribbon now power query isn't available in excel 2007 or earlier in power bi desktop the power query tools are on the home tab of the ribbon so why should you use power query well when you're spending a big chunk of your time gathering and cleaning data the time savings generated by power query can be huge a survey by kaggle of nearly 16 000 respondents shows that on average they're spending 40 percent of their time on these laborious tasks now keep in mind that the respondents of this survey are working in data specialist roles where the data probably isn't that messy but many of us performing more ad hoc tasks are likely to spend a lot more time getting and cleaning our data now there have been other surveys by crowdflower that indicate nearly 80 of an analyst's time can be spent on the data gathering and cleaning process personally i think that's at the extreme end but even 40 is a considerable amount of time to save the purpose of power query is to automate the getting and cleaning of data traditionally if you wanted to automate these laborious tasks you'd use excel's programming language vba but power query doesn't require programming knowledge because most of its tools are available from the gui with point and click ease as you'll see shortly power query can therefore reduce time consuming tasks that take hours down to as little as a few seconds power query can get messy data from a huge range of sources including tables in your excel worksheet other excel files text or csv files even multiple files in a folder or tables of data from pdfs as well as json and xml data sources and databases including sql access analysis services and even your own proprietary systems with odbc or oledb connectors you can also get data from azure services odata and the web and from cloud-based systems like sharepoint exchange salesforce and dynamics and if you're using power query in power bi there are even more data sources available from there we can clean and transform the messy data into the perfect tabular format using the intuitive gui full of common data cleaning tasks at the click of a button and this allows us more time for the fun part of analyzing and visualizing the data let's switch over to excel and i'll run through a practical example in this folder i've got three excel files containing sales data for the last three years they could equally be csv or text files power query can get data from those file types too now the data in these files is a bit messy so let's just take a look at the first one we can see here that the headers are spread over two rows which is no good for pivot tables and power pivot in column l we've got the shipping mode and containers stuffed into one column which will make it difficult to analyze the data by those two separate criteria we have order quantity sell price and discount but we don't have a column for the sale amount so we'll need to add that i'd like to know the days it takes from order to actually shipping the product so we'll need to add a column for that and my sales person data is a little formal for the purpose of the report that i want to build so we're going to tidy up this data before we combine it into one table that can be loaded into excel or powerpivot so i'm going to close this file we'll go back to the browser i'm going to right click copy the address because i need the folder path then on the data tab of the ribbon i'm going to get data from a file from a folder i'm going to ctrl v to paste in the folder path you can browse to it if you need to click ok and power query goes away and grabs a list of all the files in that folder we can see them here down the bottom i'm going to combine and transform the data remember i need to use the transformation tools because my data is a bit messy but if your data was in the perfect format you could simply skip that step and combine and load so we'll go combine and transform data power query has gone away and it's grabbed the first file as the sample file but i could choose a different one if i prefer down here we've got a list of the sheets there's just one sheet in each file and over here we get a preview of the data so that looks good i'll click ok and power query has gone away and it's combining the data from those three files into one table and that's what we see here if i scroll across you can see all of the data and if i click on the drop down here and load more you can see the data for all three files is available in this one table so on the left here we see the final query called data that's combined all of the files and then above that are all the helper queries the power query has automatically generated for me in order to combine the files on the right hand side in the query settings under applied steps you can see all of the steps it's taken in this particular query so a bit like a macro recorder it's recorded those steps for me now i need to make some changes to my data and i'm going to do that in the sample file so this is just the first file for 2017 data i'm going to collapse the query pane just to give me a bit more room the first thing i need to do is fix the headers it's automatically promoted the headers we can see that in the step here i need to delete that step so that i've got my headers in regular rows then i can transform the data and transpose it that way i've got my two headers in separate columns selecting them both i can right click and merge the columns into one we're going to have just the space as the separator the column name doesn't matter i'm not going to keep that anyway so click ok now i can transpose the data back and i can use the first row as my headers so we've tidied up the headers next i want to separate the shipping mode and the container so again on the transform tab we're going to split the column by delimiter in this case i've got a space a hyphen and a space as a delimiter but you can see there are lots of options for different ways to split your data i'm going with delimiter and in here i want a custom delimiter remember space hyphen space i can split it at any of these there's only one instance of it but if you had multiple instances of a delimiter then you have options here to handle that i'll click ok and now i have my shipping mode and container in separate columns it's going to double click to rename them so we'll cut that out and then i'll paste it in there and we'll get rid of the dot one and press enter so that's done notice in the right hand side it's recorded all of the steps that i'm taking and we can use them on any new files that are added to that folder i don't have to recreate any of these steps again next i want to add a column for my sale amount so i need the order quantity times the sale price times the discount percent we're going to add a column and we're going to multiply them now you've probably realized that it should be the order quantity times the sale price times 1 minus the discount percent so i'm just modifying the formula this is a formula bar just like we have in excel what you see here is a power query formula and it's quite similar to excel in a lot of ways so press enter now we get the correct amount let's rename the column sale amount the other thing i'd like to do is round this number so let's transform it round and round we'll round it to two decimal places and that's better let's move it over here so that it's in with the related columns the next thing i want to do is split out just the sales person's first name so i want to find the text between the salutation and their surname now in excel this would be quite tricky with formulas but in power query it's dead easy i'm going to add a column from examples and i'm going to use the selected column and here i'm just going to type in robert i'm just giving it the first example and you can see it's correctly detected my pattern and if i'm happy with that i simply click ok notice up here it's giving me a preview of the formula that it's written in order to split that text out so i didn't even need to know how to write the formula i'll click ok let's give it a new name we'll call this salesperson and now i'll go back and delete this column just select it and press the delete key i don't need it anymore the next thing i want to do is calculate the number of days it took from order to shipping so selecting the ship date first and then the order date just holding down control or shift i'm going to add a column and i want to subtract the days so it's out of my column here we'll call this days to ship and let's bring it over with the dates so they're together and pop it in there and the last thing i want to do is remove not specified from the order priority i'm only interested in analyzing the data that had an order priority so i'm just going to deselect it click ok and now i only have data that relates to orders that have been given a specific priority now notice at the bottom it showed me 999 plus rows what i see here in the power query editor is only the first 1000 rows of data power query only gets a preview of the data so that it can quickly respond to transformations that you apply but don't worry because when it closes and loads the data it will get the complete data set based on the transformations that you apply here in the editor now we've performed all these transformations it's recorded all the steps so i can use them again on any files that i add to that folder in the future but this is only on the sample file remember we're working in the sample file if we go to the data query we get an error and that's because the last step here for change type is trying to change the type based on the specific column names and these are the column names before i made the changes and fix the column headers remember it's now order id and customer id and so on so all i need to do is remove this last changed type step now we can see our data i don't need this column here with the file name so i'm simply going to select it and press the delete key and then what we need to do is fix the data types all of our columns at the moment it's using the any data type so just with shift held down i'm going to scroll across to the right and select all of the columns and then on the transform tab we can detect the data type now this isn't formatting often people get confused between applying a data type and applying formatting formatting is done in excel in the grid data types are done in power query and we set the data type so excel knows what type of data we're putting into those cells so here one two three is a whole number abc is text this is the date icon you can click on these and choose a different data type if it's incorrectly detected it i'm just going to quickly scroll across and make sure it's detected the data types correctly this one here is a percentage so i'm just going to choose percentage data type and i'm going to replace the data type in the current step let's keep scrolling across and the rest looks perfectly fine so we're ready to close and load we've got all of the data relating to the three files all of the transformations that i made to the sample file have been applied to all three files so i don't need to do any more cleaning i'm ready to simply close and load i'm going to choose close and load two this brings up a dialog box where i can select where i want to load the data now in earlier versions of excel you don't have pivot table report or pivot chart here i can choose to load it to a table which is an excel table in the grid or a pivot table report or pivot chart which will load the data into the pivot cache and that will make it more efficient for excel i can just create a connection which will allow me to reference this query in other queries or i can load it to the data model now if you load it to the data model you only want a connection you don't want to also load it to a table i'm going to load it to a pivot table report and i'll click ok remember this is loading it to the pivot cache and there's my pivot table ready to build let's bring the field list over here so we can see it beside the pivot table so here i want to see the data by order priority i want to see the order date it's automatically grouped it for me if we go into group months quarters and years we'll leave it at that that's fine and i want to know the sale amount so there's our data let me just format the numbers i have a touch of ocd about this i like to see commas and no decimal places when we have big numbers like that let's insert a pivot chart we'll put a line chart in and let me just do a little bit of formatting we'll put the legend at the top okay so there's my data for the three years i've got it with power query i've analyzed it in a pivot table and a pivot chart but what happens when i get data for the next year well this is where power query really ends it's worth so let me go and grab the file here's my 2020 file i'm just going to control x to cut it out we'll go in here and ctrl v to paste it in so there's my new file now all i need to do is come into my excel file that contains my query and my pivot table on the data tab i'm going to simply refresh all and there's my 2020 data power query has grabbed the new file from the folder it's run through all of those transformation steps and it's added it to my pivot cache and at the same time my pivot table and pivot chart have updated so you can see that if you're spending a lot of time gathering and cleaning and massaging data that power query can be a life changer i think you'll agree it's amazing i haven't needed to learn any programming languages everything was point and click easy so you can imagine how impressed your boss will be when you're able to get your work done in a fraction of the time well i hope you're excited to give power query a try whether that's in excel or power bi if you have excel 2010 or 2013 you'll find the power query download link in the video description along with a link to my course for those who want to get up to speed with power query fast you can download the file for this lesson from the link here and have a go yourself if you like this video please give it a thumbs up and subscribe to my channel for more and why not share it with your friends who might also find power query useful thanks for watching [Music]

Channel: MyOnlineTrainingHub

Views: 1,414,354

Keywords:

Id: L4BuUzccLpo

Length: 17min 10sec (1030 seconds)

Published: Wed Oct 14 2020