You're here ooh go back here i want to ask you all a question uh before i dive into this material we're nine minutes in we're doing great on time you're you're all giving me an hour of your time and i feel like that's a that's a pretty big amount of time to give me especially in all these different time zones that we're at in the very hectic world that we live in what's a perfect end result that i can give you in this hour are you here to learn about a specific topic are you trying to complete a project.
That you have in mind um just give me a sentence or two of why you signed up for this training are you really focused on figuring out how to act you access the get and transform group trying to learn as much as i can that attitude right there is amazing trying to learn as much and you learn end up learning a lot when you have that attitude want to dazzle my executives make the most of excel.
Pivot tables and charts all day long how to storytell yeah storytelling yeah numbers do tell stories at the end of the day a number or report will tell a story review demo of a pivot table all right let's slowly dive into this here let me clear this off my screen and let me pull up this excel file that i have here and let me just zoom in here i want to ask you all a question well i'm not even going to ask it i'm just.
Going to say it we have an hour together i have an excel file that i'm going to be using i'm going to put the link to this excel file inside the chat if you want access to it okay not required i'm not going to be pausing but i just figured some of us might appreciate that um really quickly let me upload this to my onedrive here and this is not required some of us are on one monitor and i'm well aware of that.
But this link will give you a chance to maybe download this file if you uh need to let me go ahead and copy a link you can also just look at the file it will be completed at the end of the day then you have a completed version of it so you kind of can walk away with something no problem everybody and here's the class file coming inside the chat alrighty so if you click on it remember it's a view only file you're going to have to go to the file tab and hit.
Unpivot and right now this looks like a very typical data dump that i'd see from a database where it's showing orders from looks like it's just the month of january the po number the vendor and it looks like we're selling certain products and these products have different quantities that we've sold it looks like we're not tracking revenue maybe revenues in a different table the price of things are in different table it's probably a pretty large database i'm imagining.
However when i'm looking at this data it looks like there's a lot of blank values and it's already summarized it's already summarized in a sense that i've broken it up based off the products typically when i want to prepare data to view inside of pivot tables i try to strive to go for something known as raw data i need raw data and usually raw data has all similar data.
In one column so right now i have all these different products and separate columns i can use a pivot table to create this with raw data but if my raw data comes out like this it's kind of fixed it's really hard to manipulate it and analyze it even further so let me introduce this tool that's also available in power bi if we have any business intelligence users i'm going to grab i'm going to highlight this entire data set and you got to realize these data dumps can be large in.
Real life so instead of instead of using my mouse i'm going to use the control key in the letter a as long as my cell address box is inside of this data set it's going to grab the surrounding data set i'm a big fan of shortcuts and i feel like with the the amount of data that i see these days i kind of need to use shortcuts i kind of need because it's just kind of impossible to grab them maybe the data sets that i've seen now let me go over to this data tab here.
And earlier in our slides when i asked everyone if we've used the get and transform group a ton of us said we have not and this is the get and transform group uh previously labeled as power query where i can use a table of data and i can prepare it and i what i'm going to do is unpivot the data because right now it's already pivoted so that i can turn into raw data that i can use in a pivot table report let me click on from table arrange this will launch a new window that might be new to some of us we've never seen.
This before i'm going to pull it from my second monitor
This is a window and let me know if you've seen this before now now this is this is where we're actually in power query it says it on the left hand side the power query editor let me know if you've seen this before sometimes people just don't know what it's called has the answer changed still no it's okay it's okay this is what tradings are for cool cool cool cool so i'm just really quickly going to do an example of preparing this data a.Little bit and putting it back into excel using power query and right now i'm simply going to select the apple states column now a quick way to select columns is just hold down the shift key and it'll select everything through so i've selected all the columns through so if i have one column selected and i hold down the shift key it's also the same thing for your computer's files if you select a file and hold down the shift key it selects everything through the selection.
Now something that i can do with this data here before loading it back into excel is i can go over to my transform command group what was the command to get there what was the command to get there i'm just going to really quickly because i'm sure there's probably 20 or 30 other people asking that right now too i selected the table the unpivot table and i hit from table range and it pulled up the power query editor and that's how i got here.
I'm gonna go back to my power query editor i hope that helped and now that i'm in the power query editor i made sure to select the columns that i want to transform you got it now when i click on these columns here i'm going to go to the transform tab because a tool that i get to use is unpivot columns and look at what unpivot columns does it's actually going to merge the similar data types together i'm going to get all of my attributes my apples pears oranges.
Products and these are my quantities that were sold inside these sales quantities and the only difference i've kind of seen between power bi and excels is this little icon here looks a little different but let me close and load this into excel again here this is going to be data oh i have to change the data type for my po number that's okay i'll hide that for now but this is going to be data.
Let me zoom into it for you yeah absolutely absolutely yeah here we go this is i'm zoomed into about 265 percent for you but this is essentially the data in raw form where it did show me the products but now i've got all the quantities and put them together there's no blank rows i can now use this table to summarize as a pivot table now.
This one at the demo on pivot here i'll go through it one more time i can do that definitely but i do want to show pivot tables too okay i'll go through it one more time for everybody but let me grab this data here and let me go to my data tab and add it to the power query editor by going to the get and transform group here so the big key thing is you have to load this data to the get and transform group.
So that you can unpivot it now here is where i actually selected the columns that had the products and the quantities that we've sold and by selecting them i simply mean that i selected the first column and i went all the way to the right like the cha cha the joshua slide dance uh that i think that's what it's called yeah elementary school's fading away from me now from here.
Does this excel no i believe you have to add the power pivot add-in if you have excel 2013 yes i do have office 365 so i have the latest updates for it let me really quickly finish this here let me go through it one more time and i'm going to use the transform command group to unpivot these columns to unpivot these columns here and then from there i can simply rename these top columns the headers of the columns i'm going to name this the products.
And i'm going to go over to values here and i'm going to rename this quantity and i did notice that my po number
Showed up a little funky last time it's because it's showing up as a number here let me change it over to text there and i'm just going to go back to my home and i'm going to close and load this data back into excel again pretty nifty tool now what i like about powerpivot or power query we should call it uh the.Get and transform group is if i go back to this data and i add another value i get the option to refresh the table and if i refresh the table it re-includes those new values that i've added in look at vendor z so this is an updated system that you can maintain where the raw data shows up and then you can use this data within a report if you need to.
But the report itself stays the same oh it's just simply so i can show the number because it turned into a uh x it turned into an exponent a mandy let me show you this previous table did it um oh i fixed it so it changed it back it was showed into like the letter e and an exponent going up so i changed it from a decimal to a text so i can see the actual peel number in the cell cool now i'm going to close the queries and connections window.
And i actually have a much larger data set that i want to use to build a pivot table nifty tool indeed i can spend three hours on it there's other things it can do it can merge you can append but a big thing that i like to do is prepare data for pivot tables with it now right here in this next sheet called complete data i have a much larger data set it's already been prepared there might be a few blanks here and there that's okay but it's already a table.
I have 26 columns put together they all have a header now i asked this in the very beginning but the whole point of getting data in this state of just having it in columns and rows in a table is so that you can summarize it with the pivot table how many of us are able to build a basic pivot table before i start a basic pivot table on your own.
You're going to see me do one here it's okay if you can't but you're about to watch someone build a pivot table from scratch using raw data so i hope i hope you can see this process and even if you're probably never going to do this it's really going to help you see this when someone sends you a pivot table they've built so let's check this out here when i'm inside of this table.
I can go over to my oh apologies where is it there we go when i'm inside the table i can actually access a special tab called the table design tab this only happens if your data set is selected so if i'm outside the table i don't even get to see that tab but i want to summarize this table so i'm going to go into the table design tab and if i zoom in just a little bit i can see that there's a tools command.
Group and the tools command group gives me an option to summarize with pivot table let me summarize this data which is rather large if i zoom into it it's tracking the order ids the order dates for an entire year it looks like from various different cities some of us are from these cities if i scroll through this oh look we also got shipping dates very similar data to what we just saw um shipping cities oh i got products we're selling mega.
Pints look at that categories and then over here we're tracking the revenue the shipping fees and the unit price so really quickly let me summarize this data because i can't figure out what's my top selling product from here i mean i can if i filter things but that would take forever look at how many things i'm selling so i'm going to clear this and i'm going to go to the table design tab and if i choose summarize with pivot table.
It's going to pull up my data set as the table and range because that's what i was inside when i selected summarize with pivot table and if you take it from me i recommend to build these reports these summary reports on new worksheets you don't want to touch the original data set you might get something known as a circular reference if your data set small you can't choose existing worksheet and select a location on the set on the spreadsheet to add the report but a best practice for me is definitely just adding them on new worksheets.
Which will generate a new worksheet in the workbook that you're currently in what's really cool is you can also use powerpivot special add-in i don't think we're going to cover it today but it's a special add-in where you can merge multiple tables together and then that way essentially reducing the prop reducing the chances of needing to do a vlookup or an x lookup because you can merge your data with the data model and then use that data model to build the pivot table kind of how we used power query to build that table yeah.
Let me hit ok here um here i'm going to go to the file tab i'm going to show you where it is you can add it after class okay if i go to the file tab because i already have it it's called power pivot power pivot it's up here but it's built in you just have to turn it on if you go to the file tab under the options here under the options is where you can find your add-ins.
Now in order to turn on powerpivot that's a com add-in a com add-in when you hit go you can see that i already have it on but that's a great tool if you have advanced pivot table building from csv files access databases salesforce pages yeah cool so we're not using powerpivot we already have all our data prepped up in this complete data sheet and that's why i was.
Able to just build a basic pivot table inside of my excel workbook on a brand new sheet called sheet4 let me rename this i'm going to call it pivottable one you choose the powerpivot add-in sorry richards i should have zoomed in and clicked on it myself the powerpivot add-in yes and that will pop up a brand new tab in excel uh for you.
Where you can add things to a data model yeah i have some youtube videos on that as well yeah so with this table here now that i have an area to build a report i want to talk a little bit about some details that i've learned when building pivot tables you got it gary i'm going to zoom in nice and large to this pivot table report hopefully we can see it nice and clear here.
Better good now right now i have an empty pivot table there's nothing it's not displaying any data yet but i just want to mention that my cell address box is inside of it that that is a fact that i want to mention because of that fact excel is giving me access to special items that weren't there like the pivot table analyze tab this tab was not there and it won't be there if i move my cell address box outside the pivot table.
Same with my design tab which can change the layout of my report if i go over to the design tab here it will let me change the layout choose if i want grand totals subtotals the style of my report but again that will disappear if i leave the pivot table report area and then last but not least i have this area where i build my report this is where i can actually utilize that raw data to tell my story and maybe who's my top seller what's my top selling product.
Now if i move my cell address box all three of these things will disappear and if i put it back it shows up but i know some people told me that they don't build pivot tables they use them sometimes the person that creates the pivot table will close the field list they'll close it and here i'll zoom into this little area here because there's a little x.
That lets you close the third area that i was mentioning and now even if you do click inside the area that third area won't pop up and most of the time when people have built your report this is how you're going to receive the file with the pivot table area closed out for you if you do need to edit or make changes to that report or even see what fields they use to create that report the pivot table analyze tab has the ability to turn that back on.
It's called a field list and if it turns off i spelled the typo there it's okay but it's the field list if it turns off i'm going to zoom in here there's an entire command group dedicated to turning it back on called the show command group i got you guys i got you all i should say actually i'm going to click on field list here and it comes back.
How would you know this how would you know this so this is this is a very common thing i get asked i have accepted the fact that excel word powerpoint all have contextual features they have contextual features and what does that mean it means that some things disappear depending on the context that you're in.
And some things reappear like i'm back in my report depending on the context i'm in so i always try to click on something as i'm looking for things an excel word in powerpoint because clicking on things makes things appear that you might need to use so if it's usually select to effect i have to select something to go affect it in word excel and powerpoint very rare where i first go click something and then highlight what i needed to do with it word excel powerpoint doesn't work in.
That fashion yeah great great question in itself but i hope that gave you a little idea of how to memorize you know how to how to pop up these areas select to effect yeah thank you i like it too right it's catchy it catchy it's easy to remember and it makes sense yeah so now that we have this here i want to build my report but in order to build my report i have to use the raw data.
Within this field list i'm going to zoom into this little area now and it's showing me every single field of data or every single column of data i had in my report my raw data and i get to use this to build my summary report i don't like the fact that there's a scroll bar here so in office 2016 they added in this little tool box for pcs that allow you to alphabetize that list.
So i can see it in a to z order i know exactly my address is on top my zip code postal codes on bottom that still leaves the scroll bar i still have to scroll through this i can use the search yes but i also like doing this i like changing the area itself i like changing the field list to be side by side and when i do this it's simply going to allow me to see all the fields that i have available to create my report before dragging me and.
Dropping them into the fields there it's just showing me all my data at once let me really quickly do that for myself i saw woot now anytime i open up an excel pivot table it will show me the pivot table field list side by side this is an application level setting for my computer and i really quickly want to build a very basic report that shows me my sales people and the revenue that each one is bringing in.
I think it'd be useful to see that so when we're building pivot table reports we get to use these fields and throw them into one of four boxes one of four boxes to create an area of my report now if i end up just throwing a column that has values like revenue into the values box it's going to sum up every single revenue into one cell revenue values.
So that's the total sum of revenue but what if i wanted to see it broken down based off each sales person that's not too useful right it's almost like it's almost like answering a question for you what's the sum of my revenue that data well the sum of revenue is there what's the average revenue i'm going to click on here the value field settings what's the average revenue that's the question i'm asking now i'm going to see the average oh i can change the report too it's from.
Sum to average what if i wanted to see average and sum i can throw it in twice let me throw these out again we're kind of playing around here right now getting familiar with this tool that's going to answer a lot of questions and when question gets answered you make decisions i'm going to throw revenue back in here so it's just the sum i'm going to leave it as sum and i want to see it how did you do average that's a good one it's a it's an advanced pivot table.