How to use Microsoft Power Query

hi everyone kevin here today i want to show you how you can use power query that comes with microsoft excel and power bi first off what is power query well it helps you both get and also clean up your data what do i mean by that well let's pretend that you're a business analyst and every month you're on point to analyze the monthly sales data now one of the pains of the job is well first off you have to get the data so you go to some database or you find the latest excel sheet then maybe you bring it into microsoft excel and then you have to clean it up maybe you have to remove some columns add some columns run some calculations and then finally you're ready to analyze it instead you can let power query do all of that for you and you can automate that entire process and best of all you don't even have to know any coding to do this i've included some sample data in the description if you'd like to follow along alright let's jump on the pc and let's see how we can use power query here i am on my pc and at the kevin cookie company i'm on point for analyzing our order data here you can see three years worth of ordered data and very soon 2020 will be dropping in this folder once again if you want to follow along i've included sample files in the description let's click into one of these files to see what work is involved here's the excel sheet and it's a pretty standard order data sheet i see order id i see the customer how many cookies were shipped and some additional information now the pain is at the kevin cookie company they're paying me the big bucks to do analysis but i end up spending most of my time just cleaning up data here for example in column b i don't know why they combine the customer id together with the customer name also when i look at cookie shipped they throw a dollar sign in front of this it should be a unit count not a dollar amount revenue costs look fine but i need to add another column for profit i also want to know how many days it takes to actually ship these cookies out of our warehouse i have to go through and i typically add columns it's a lot of manual effort and worst of all every time a new sheet comes out i have to do all this clean up again and again i'm really hoping power query can help me today to access power query in the latest version of excel simply go up to the top ribbon and click on data if you have excel 2010 or excel 2013 you need to install an add-in to be able to use power query i've included a link in the description where you can access that over on the left hand side power query falls within this category the get and transform data if we click over on get data we see all the different sources where we can pull data in from it's a vast collection of different places basically if you have data somewhere you'll be able to pull it into power query when i hover over from file here you'll see a few examples one of the neat ones to call out is you can now pull in data from a pdf you could pull in data from databases from cousteau from azure the list goes on and on of all the different places where you can get data i mentioned that i have my three files all in a folder and very soon the 2020 data will also fall in that folder so within the from file list i'm going to click on the option that says from folder next i see a prompt where i can specify the folder path where i have all of my excel sheets i could click on browse that'll open up a file picker where i can navigate to where those files are i've already copied and pasted the folder location so i'll simply paste that in and then click on ok next this opens up another prompt where i can see that excel or power query has found three different excel files in that folder i had the 2017 through 2019 data so that all looks good down below there are several different buttons what can i do with them well i have three different sheets here and i want to combine all of the data from those sheets so i'll click on the combine option and i want to transform the data what does transform mean and why would i click on that well transform allows me to modify the data before pulling it into microsoft excel or into power bi so as i was walking through the data there were a few different items for instance i wanted to get rid of the dollar sign in front of a column i wanted to split out the customer name that's what i could do with transform data i could also simply combine all the data and load it into excel or i could choose where i want to load it to but then i wouldn't have the opportunity to make changes to the data before i loaded in here too i also have the option to simply load the data as is or i could simply transform the data but because i have multiple sheets and i want to combine them i'll click on combine and transform data this opens up another prompt where i can now combine all of my files up above it allows me to choose a sample file and by default it simply selects the first file which is the 2017 data i could also change it to any other year but i think the 2017 data is fine so i'll simply leave it set to the first file down below i also see the worksheet within all of the different workbooks now all of my different workbooks have a sheet called order data so i'm going to select that over on the right hand side here i can see a preview of all of my data and it looks like it identifies it properly so now let's click on ok this drops us in power query and if you've never used power query before and you're on point for analyzing data and getting data you're going to learn to love this interface it might look a little bit intimidating at first but i'm going to walk you through step by step how you can use it and first i want to orient you to the experience let's start off over on the left hand side where we see all of our different queries you have a set of helper queries and here we see a sample file that we can transform down below you also have the final query that will apply against all of our data so when we bring it into microsoft excel it'll all be transformed and as we walk through this example that'll make a lot more sense in a moment over on the right hand side we also have all of our different query settings down below we see something called applied steps the way to think of this is this is kind of like running a macro and power query remembers every single transformation that we make on our data up on top just like with most office applications we see a ribbon with several different options we're going to use this to transform our data in the center of the screen i can see a sample of my data at the kevin cookie company we sell a lot of cookies so it's only showing me the top 1000 rows of our order information once i make all of my changes to this data and i transform it then we'll close it and load it and that'll apply these changes to all of my data beyond just the top 1000 rows let's go through and let's start transforming this data now i don't need the source name for the year that it comes from so i'm simply going to right click on this column and all click on remove that removes that column here now i see the order id and for the most part it all looks good but there's also an xxx that appears now it might seem a little odd to have that in here but anytime the kevin cookie companies say we send free cookies to different events or different promotions and so we mark that down in our order data but when i do my analysis i don't want to look at this so i usually go through excel when i filter it out let me click on this filter icon up on top and because it's a letter i'll scroll to the bottom of the list and then uncheck this this will remove it from my data set next i was complaining about the customer id being combined with the customer name i want to split these two i'm going to click on this column and then when i right click on it i can go down to the option that says split column and i want to split it by delimiter the delimiter is the space the dash and the space so let's click on this this opens up a prompt where i can specify a custom delimiter here it looks like there's a dash but i also want to insert a space in front and a space after that looks good let's now click on ok this now split those into two separate columns if you look at the column title though one of them is customer id.1 and the other is dot 2. that's not a very descriptive name let me double click to change the column header name and i'll change this to customer id because that truly is the customer id and then this next column well that's the customer name so let me double click on that and i'll type in customer name as we continue moving over to the right in the data i'm going to minimize the queries on the left hand side to allow us to see more of the data here next i want to calculate the profit and profit is the revenue minus the cost i'm going to select the revenue column and also select the cost column and then let's go up to the top ribbon and click on add column within add column in the center there's the option for standard and i have different types of calculations that i can apply i want to take the revenue and subtract the cost so i'll click on the option that says subtract this adds another column all the way to the right hand side of my sheet called subtraction i want to give this a better name so i'll double click on the column header and this is our profit and i'll type in profit i want this column to appear alongside the revenue and the cost so i'll simply drag this column over and place it right after the cost my revenue cost and profit columns well these are all currency and i want to apply that data type i'm going to click on control and select the cost column and the revenue column and then right click within this menu let's go down and click on change type and i'll change it to currency you now see this icon here indicates what the data type is over on the right hand side you'll see our ship date and the order date and there's always a little bit of lag between when a set of cookies is ordered to when we're able to ship it out of our warehouse i've heard some news that perhaps it's taking a little bit long in some cases so another column would help me know how many days it takes i'll select the ship date column and then i'll press ctrl and select the order date column i selected ship date first because i want to take the ship date and then i want to subtract the order date from the ship date column once i have both columns selected let's go up to add column on the ribbon and over on the right hand side i can click on the option that says date within the date menu there's the option to subtract days let's click on this this adds another column over on the right hand side and it subtracted the dates so here i could see how long it takes and wow five days to get cookies out of our warehouse that's unacceptable i'm gonna have to talk to our warehouse team to see why it's taking so long now right now this column is simply called subtraction i'm going to double click on this and give this column a name i called this column days to ship and that looks good just like before i'm going to click on this column and i'll drag it over so it sits right up alongside order date and ship date as i'm walking through every single step here over on the right hand side you can see a record or a history of every single step that i took in a sense it's like recording a macro in microsoft excel let's say for instance that i have this order status column and maybe i don't want it i can right click on this column and then remove it here you see down below that it says remove columns 1 but let's say that really i want to keep the order status there i can click on this x to remove that step here now if i go over to the right hand side you see that the column has returned along with simply removing a step that i recorded you could also click on an item and you could move it around if you want that transform step to happen earlier in the steps i've now gone through and i've made a number of transformations to my data i'm going to go back over to the left hand side and once again there are all sorts of different transformations you can make the way to think of this is as you're going through and you're transforming your data you're in a sense recording steps that'll automate bringing data like this in in the future now that i'm done with all of my transformations i'm going to click on home up on the top ribbon and next let's click on close and load this now drops me in microsoft excel and if i click on the order date here you can see that all three excel sheets have been combined into one it brought all three years worth of data together and the great thing is i didn't have to go through each individual sheet to make all of these changes here you see the customer id is separate from the customer name here you see that all of them contain the days to ship so here it's simplified merging all of this data into one sheet over on the right hand side i also have a pane for queries and connections and here i see power query if i right click on this i can go down to show the peak and here i can see a sample of the data once again but the thing that's really interesting is it's looking at this data source or this folder so if i bring additional files into this folder it'll bring that data in automatically and it'll apply all of the transformations to that data so i no longer have to get the data or clean it up all that happens for me automatically before i pull in additional data first i want to insert a pivot table and a pivot chart so i can start analyzing my data to do that i'll select within the table go up to insert and then i'll click on pivot table i get a dialog telling me to create a pivot table i'll stick with all the defaults and click on ok this opens up a pivot table if you've never worked with pivot tables before they make analysis so much easier i've included a link in the description that'll get you up to speed on how you can use pivot tables i want to track our profit by year and once again pivot tables make that so easy i could simply click on profit and i'll drag it down to values next i want to track the profit by the order date so i'll drag that into rows here immediately i can see what our profit was by year i now want to insert a visual that shows me what our profit is by year i'll click into the table and an option that says pivot table analyze appears on the top ribbon over on the right hand side i can insert a pivot chart let's click on that for the chart i want to insert a line chart this one looks good let's click on ok here now i can visualize what our profit is by year i was just notified that our 2020 data has arrived and it's true when i go into my folder where i had all of my previous data here i see 2020. now in the past i would have had to go through and i'd have to do all those same changes and transformations to the 2020 file but because i used power query and because i'm looking at this folder i don't have to do anything extra instead i'm gonna minimize the folder view here i see the three years i'll simply click into this table go up to pivot table analyze and then under this i can click on refresh all when i click on refresh all here i see that it automatically pulls in 2020. so all of the changes that i did to 2017 2018 and 2019 it automatically takes that power query and it runs it against 2020. here too if i go back to the original sheet with all of my data if i click on the order date here you see now that 2020 data has been included so power query not only gets my data but it makes all of the same changes and transformations to that data now i could spend my time on more valuable things like analyzing this data and trying to make sense of it alright that was a quick look at how you can both get and also clean up your data using power query if this video helped you please give it a thumbs up to see more videos like this hit that subscribe button if you want to see me cover any other topics leave a comment down below alright that's all i had for you today i hope you enjoyed and i hope to see you next time bye

Channel: Kevin Stratvert

Views: 290,330

Rating: 4.9800282 out of 5

Keywords: kevin stratvert, power, power query, query, excel, power bi, powerbi, excel pq, data, transform, automate, macro, microsoft excel, tutorial, how to, help, combine, extract, analyze, pivot table, pivot, applied steps, step, steps, clean, clean data, code, excel 2019, excel 2016, excel 2013, excel 2010, record steps, data model, get, get data, import

Id: 0aeZX1l4JT4

Length: 16min 34sec (994 seconds)

Published: Tue Nov 24 2020