Feel good because you get to like really see the people and you'll get to see if they're happy or not because they got the mascot but still it was just a whole different ball game and i forgot how much i miss it like being we're getting closer and closer to normal you know things to progress yeah yeah i'm excited yeah this is great um i now also know we did change the topic up a little bit i know we were going to do tips and tricks today initially but joe got super excited about power pivot tables and he's like.
Yo we got to do this this weekend so we got an exercise files for you guys if it's in the video description uh let me actually drop it in the chat as well well later let's i have a thing i'll i'll drop it in chat just a few minutes here guys i'm on a different screen within our obs here just fyi but uh just some quick channel updates number one remember to follow joe on tick tock for regular excel updates the link to his tick tock channel is here in the description as well um how's tick tock going joe.
It's good so i do have my excel videos up there and then i also have my family stuff up there too so if you want to just see like what i do what an instructor does on his off days it's pretty much fixing cars and hanging out with my son and my wife so it's gotta get that excel stuff in there plus excel stuff though okay hello it's uh good to see you you're uh from england oh that's great we actually actually yeah we worked with a uk team to build out our uh new tms that we.
Launched last year so i definitely am used to working on uk time it must be the evening for you guys uh about what four or five pm um yeah um let's see oh yeah i was gonna get those uh i was gonna get that link for you guys real quick let me get that the exercise files but also i did want to give you guys a quick uh shout out and thank you to everybody again for watching liking and subscribing we had our best january and was actually our best month ever so i want to thank you guys for that again it means so much to.
Us for you know just like i said watching sharing subscribing liking commenting it it all it all helps us tremendously we we keep setting record months so you guys are helping us grow more and more and create more and more videos um and it going into that the update for the channel is yeah we're creating tons more videos i've been interviewing people all week to find some new instructors to teach some new topics and stuff like that um we will be branching out into some apple products uh i have somebody potentially that could uh do that i'm super excited about.
It's still relatively new um but we will have all those videos coming out um by i will say q2 they'll start rolling out um i'm not gonna make promises on that though um again and like i said let us know what you guys want to learn drop it in the chat the comments everything like that like honestly when i see something that is you know that i know is it being used in the workplace and everything like that we uh we actually go out and we seek those to create those titles and stuff like that let's see lou has.
To do sql query it actually was sharepoint i might have misspoke on that it was the sharepoint fundamentals class that we split into two the sql querying class that was going to be two classes but um the amount of content it only kind of made sense for us just to release it in one video so the sql query for beginners was initially supposed to be two videos but we condensed it into one um so that's why there was a little bit of confusion there and again that's why we don't always say like you know for sure this is gonna be the date and i always.
Say it's gonna be in flux because sometimes we look at it and we're like well you know it's about you know three just about three hours let's just make that one video for you guys um yeah the sql query and basics uh query yeah that's that's the one that's going to be learned anytime lou that's going to be the only one in there um for now i am trying to get a sql service report sql reporting services ssrs that's the way i know it i can't say the whole thing but it's going to be a much more in-depth and robust class and again i'm currently looking for.
Somebody the person i originally had lined up they no longer can make the video series but i'm getting somebody else to do that so we'll have a much more full-on you know working with sql server going through all the types of queries being able to read all the transact sql uh command lines and everything like that and you know just it's gonna be a much more robust class it's gonna be based heavily on the recently um there was a it was an old microsoft class called one zero nine nine zero um they're trying to they're doing a big push trying to get you guys all to use.
Azure uh hardcore um but i know there's tons of people who are still using sql server and everything like that so um we will have a class that focuses on that um kind of a lot to say about the updates on the on the channel um but yeah that's uh that is that is it that is uh where it's all at where it stands right now so again sql query got condensed into one video sharepoint fundamentals got split into two and so that's where we're at and then if you if you're looking for it.
To be on our learning time subscription site we add all the videos at the end of the month uh as one full series and uh
Actually there'll be some surprises on the learn anytime side that for anyone who doesn't know learn anytime is our subscription based service it's ad free so you don't have all the ads that i know run on our channel but we are adding we're going to be adding exams to there pretty soon and then also we uh potentially have a whole new series of videos that we'll be launching on there exclusively not on our channel as well so another reason to sign up for.Learning anytime um so i hope that answers everyone's questions there um any yeah so i hope that answers your question lou and i hope that uh uh answers everyone uh let's see all right um let's see well that's it from my side joe yeah i i saw um zine said can we do like a video on protecting cell ranges and how to allow editing so what i was thinking of and i'm gonna talk to ben with it too i i did see that.
We are starting these like i'm starting to see a lot in youtube these little short videos like youtube shorts and um i mean i think that could be something that could be a youtube short it's really not too in-depth on how to do that so to protect the cell range and then allow like certain things to be edited so that they can't have full rights to you know editing your worksheets but they have certain you know rights too uh so that's something i i could you know create later on you could do it on your tick tock joe oh.
Or tick tock too yeah yeah i mean i think that's pretty good we could essentially guys we could take these questions and we could have joe just tick talk them if you if you want joe you guys can i mean i yeah if it's something that's in a short amount of time that's going to take me three minutes to explain which i think that's one of amazing i definitely uh feel free to ask and yeah we'll get those out for you too yeah and then for the keyboard shortcuts so i am going to be doing those i promise it's just i got really excited about powerpivot um if you've ever used pivot.
Tables what what it does is it takes data and it puts it into a table with interchangeable rows and columns and what that allows you to do is then look at the data in different ways to answer questions based on that data so today i thought we'd do power pivot tables but before we get into that of course i am super excited because i never have any new mugs but um shout out to a couple of friends of mine for getting me these mugs and uh yeah look at this.
This is such a cool hug it's one of my favorites you gotta read it backwards but yeah that was specifically for my friend kim so shout out to kim thank you so much that's awesome i believe i almost forgot about the monks oh so i did drop the linkedin exercise files guys just in there so if you want to get that opened up i'll give you a second here um i've had this one on the show before i'm sure but someone i'm using today i haven't used it in quite a while it's the happy cat one the only problem with this one and i think i've demoed it before is that these run.
Into your mouth when you try to drink so it's it's not the most ideal thing to to drink coffee from um let's see so just say hello to everyone azeem hello blaise katana great to see you again i saw sue in here as well good to see you again from uh yeah i see freezing central texas it's pretty cold in california too so it's uh really cold stahill great to see you um arjun great to see you as well sufian great to see you as well i'm glad you guys are joining us um are you ready to.
Get started uh on power pivot tables here joe let's do it all right so let me know when you're ready to share that screen and we'll get that up for everybody all right screen is being shared all right here we go boom awesome hello joyce i hope uh south africa is uh i think it's uh warm now right because it's should be summer there since you guys in the southern hemisphere and motif uh hello from pakistan yeah that's another global audience today i love it i'll see.
You guys on the other side and let joe do his thing here so um yeah today we're going to be talking about power pivots as i explained pivot tables allow us to analyze data and and answer questions on data right so what happens though when you have three different data sets right so for instance we have this data set over here that has the products and the price so how much is each product this one over here has the sales reps in what region they actually sell in and then this one's more of a factual.
Table where it has the specific dates that each sales person sold what product they sold and if they gave a discount
And how many units they sold the problem is i need to start to ask questions about this data but since it's in three different places there's no way for me to really put all three of these into the pivot table right away i'm gonna have to do some data mining now i know that we've taught like vlookup next lookup on this channel before but essentially what i want to do is skip the vlookup and x lookup and the.Reason why is because we can use powerpivot to pull all three of these datasets into our power pivot create relationships between them combine them and then we'll be able to load them into a power or a normalized pivot table so that's what we're going to do today i i really think this is just so important to know so the first thing we need to do though is turn on power pivot and it's actually one of excel's add-ins so the first thing we have to do is go over to the file tab.
Go to the options so we can get excel options now once we're here you'll see in our little navigation list here we do have add-ins so we're going to navigate over to the add-ins and then once you're here you're gonna see just a bunch of stuff here like all the different add-ins and active application add-ins uh you'll even see microsoft powerpivot but that's not what we're gonna do it's just telling us where it is which is nice so we can see it's a clm add-in com is component.
Object model that's what it stands for so it's you know just a nice add-in for us to use so i'm going to click on it go to com add-ins click on go now you'll notice that we have quite a few of them we have like data streamer for excel we have inquire we have power maps if you want to use that um and then we have the last option which is power pivot so i'm going to click that on and i'm going to click ok and now you'll notice that i do have what's known as the powerpivot tab.
There it is and there's a couple of things in here so first off we can manage the data model which is where we're going to put all three of these data sets we also can create measures kpis key performance indicators we can add to the data model we can detect relationships if things are really named correctly for instance like product and product sales rep and sales rep it'll actually detect those.
Relationships if it's the same um formatting it's the same header name it'll be able to do that for you and then of course some settings on those powerpivot environments now the first thing we have to do is load these three tables in and it's always a good idea to name your tables the reason being is because when you name a table and you have a table design it usually says like table 1 table two table three i don't like that personally because then i get confused on which tables which so i just named this table.
Price table i named this table region table and i named this one the fact table because that's where all our facts are so i already did that for you so if you're following along with the file you don't have to rename them now the first thing i'm going to do is click into this one table here it doesn't matter where you click you just click into a table go to pivot and you're going to add it to the data model by clicking this little button here.
Now it's going to open up power pivot for excel so it's very similar to any microsoft product we do have our tabs up here not as many but we do have some tabs and we also have uh commands and command groups so we have our one table here but we're not finished yet we have to go back into excel and we have to go grab the other two tables now the thing is is to go back to excel you'll see this little excel where it says switch to the workbook right up top here so it's like a little excel icon.
And we're gonna load the next table and once again it's just click on the table anywhere on the table and add to data model boom now we have two so we have price table at the bottom here and we have our region table and we're going to do the same thing we're going to go back to excel and just load in that third table click anywhere add to data model and boom now we have all three tables here we have our price table our region table and our fact table and see how it says it at the bottom if you didn't name.
Your tables it would have been table 1 2 and 3 and well you wouldn't know exactly what's in there so it's always important name your tables so the next thing we want to do is we want to create a relationship between all three of these data sets right based off of something that they have in common now to do this i want to make it a little easier for me so right now we're what's in a data view and i want to be in a diagram view so all the way to the in the home tab here all the way to the right you'll be in the view command group and you're going to select.
Diagram view now once i go into the diagram view i have my three data sets and you can make them a little bigger or smaller you can move them around and it's just an easier way to just look at the data right you see the headers and you see the name of the table now i have to figure out what is the relationship between those two data sets with my facts table and really the relationship is between my price table and my fact table we have the products so you'll see here we have the products here and we have the products here and what we're trying.
To do is grab this price and just bring it over here so since there's a relationship i can now take the products click and drag it to products and it creates a one to many relationship now a lot of people ask me why is it one to many that asterix means money well sometimes it could be in many to many sometimes it's a one-to-one sometimes it's a many-to-one one to many and the reason being is let's pretend that i have a list right over here i have this list with all my sales reps.
Then gosh endless but then over here on my fax table i have all the different sales we've made so i made two sales so i'm listed twice ben made one sale here sorry about josh made two sales all right what happens is is now there's many versions of our names here but there's only one unique version of our name here so that's where we get the one to the many.
All right so hopefully that helps to kind of understand those relationship connections now the next one we have to think of is how can i get the region table to my fact table and well i see that we want to pull the region into this table and the only relationship is sales rep to sales rep and once again that's a one to many and this one is perfect about the example i just showed we have the sales reps listed but then when they're making their sales they might have made like four or five sales so their name is listed four or five times.
So now we have our relationships they look great we can go back to our data view now i'm going to be on my fact table because this is where i want to pull the other facts and i want to add a column for first off the price and second off well the actual region so in order to do this i'm going to click on add column i'm going to use what's known as a dax expression a data analysis expression and it's very similar to a function in excel.
But they're quite different like there's different ones so we do have an if act expression which is nice it's very similar to the if function but the one that i want to use today is related and what this does is it returns a related value from another table very similar to vlookup or x lookup right where you're searching by a lookup value in a list and then it returns that that information we're doing that but since they're related we can now just pull that information quickly.
So i'm going to say equals related and i want to pull well what do i want to pull the price and i'm going to pull it from the price table so i'm going to double click on that and press enter and what it's going to do is it's going to now pull in that price you'll see doublers are 79.95 sunshine is 19.95 doublers down here 79.95 so it worked perfectly and the only thing i really need to do is just rename this column and i'll just put price.
Now we're going to do the same thing except we're going to pull the information from our region table right i want to pull the region in here so now that it's related simple enough i can do equals related and pull what i want because we created those relationships i want the region press enter and there it is we have our regions pretty cool so it does work and function the same way as x lookup or vlookup but right now we're getting it already uh primed and ready for a pivot table right.
So now all i have to do is rename this one i'll call it regions and now what i can do is i can take this fact table i can use this pivot table button here and i can load it straight into a pivot table and i'll put it into a new worksheet and click ok and there we go now you'll see we have all three of our tables we really don't even need to use the region or a price table unless we wanted to because we already incorporated everything into the fact table now we can just simply create.
A pivot table see like our sales rep and one what region they were selling in and another and then how much uh they made total in each region and there we go pretty cool i actually don't like it like that i want the sales reps over here and let's do the regions over here so it's a nicer looking table and this is just a really great way once again to take multiple data sets combine them together put them into a pivot table so we can quickly start to ask.
Questions about our data now once here of course you can play around with the pivot table any way that you want uh design wise we can for instance maybe i want i don't know banded rose so it looks a little nicer here i want to change the look of it i want it to look that looks good i want to make sure that all of my numeric values are well currency so i'm going to do ctrl shift to the right control shift down to select my entire currencies.
There we go and then i'll just go to home tab change it to a currency there we go and then another thing we can do here if we wanted to um we could i don't know change it up even more maybe by the way name your pivot table that's important we could go to report layouts maybe show in an outline view so it's a little more compact we can turn off grand totals turn them on if we.
Don't care about the grand totals over here because it's like one price each we're more than welcome to do that as well so you can just turn them off for both the rows and columns so that we just have our data and subtotals as well we can turn them on or off we can include filtered items things like that once we're done creating this pivot table also don't forget we can drill down so if you double click it pulls a report for all of ben's information and then we can go to the sheet and just call it fens sales.
Or when we go back to our data again i can go take a look at well let's see what did bethany sell so i could double click and it lists all of bethany sales so this is even more amazing because it creates a new sheet where now we can just pull this stuff apart and i could call it bethany so as you can see something that i think is really important for you to know taking multiple data sets combining them together creating relationships using dax expressions and then loading them into pivot tables.
And uh with that said i hope you enjoyed thank you so much boom game changer joe game changer i love it we did get one question right off the bat um from wasif um it says any shortcut to fill in blank cells on a pivot on a pivot so while there's no shortcut to fill in blank cells on a pivot table because it's already a like it's already pulling that data from an original data source what you could.
Do is just go back to the original data source select all the blank cells and then replace them with data and i typically like to do like n a for non-applicable or none or having something there rather than nothing is always good in data especially when you're analyzing it because those blanks can get a little tedious and you're like well what's a blank mean does it mean something messed up the data or doesn't mean that it's just not applicable to that data set so yeah i would go back to the original data just select all the blanks which you can do um you know by.
Clicking on replace and go on advanced options and then just fill in whatever you want whether it's mma or none and then and then refresh your pivot table once you refresh your pivot table it will include it in your pivot table as a value i hope we answered your question there um see any other questions guys we got actually this is one of the few times we have a lot of time left as far as usually we're running it right up against the clock but this time we got a little bit extra time so you guys have any questions about excel or just.
What we just demoed or anything like that or you want us to talk about the pivot tables a bit more oh here's one from blue let's go if you get data from an external source when you refresh the pivot does it auto update so it depends right um if it's if it's constantly updating the original data source yes you do still have to click refresh um if you're manually putting it into excel and it's just raw data then no then you have to reselect your data what really is important when you're.
Using pivot tables is that we create tables off of pivot tables because the table expands right so every time you add new data the table expands so if you are doing it manually the quick fix is just turn it into a table so every time you pull in new fresh data from that external source you could just click the refresh button instead of reselecting every time um but if it is just coming from a external source then and and you know it's updated data you.