Power BI Beginner Tutorial

hello and welcome to power bi bootcamp I'm Chelsea Dolman and I'm really excited to show you all that power bi has to offer through this course we will introduce you to power bi as a program power bi as it relates to features in Excel and the online service called power bi online this has been designed as a hands-on style course with plenty of moments to pause and try on your own in order to follow along you will want to download the work files you will also need to download the power bi desktop program a recent version of Excel Excel 2016 of course would be nice but you can also use 2013 and 2010 so long as you are allowed by your company to use the add-ins necessary and if you have office online or your company has provided you with an office online account you can follow along with course sections discussing power bi online if you haven't done so already let's take a moment to pause the video and get the required software in the next video I'll walk you through the process through which you yourself might be able to add those elements if your IT team is not responsible for doing that if you don't already have all the software listed here you will want to make sure that you have it in order to follow along with the course if your IT team is responsible for downloading software on your devices take your laptop to them and tell them that you need all of these items if it's your responsibility to add software you can follow the following steps to add the necessary tools to download both the desktop application and power bi online you'll want to open up your browser and go to power bi dot microsoft.com this is your power bi landing page right in the middle you'll see a button that says get started for free that's what you're looking for this will take you to a window where you can download the necessary features on the left you can download the desktop application make sure that your version of excel has a matching bit version with the version of power bi you will be downloading if you don't already know what your excel bit version is you could find out easily by simply opening up any Excel window going to file choosing account and find the command called about excel at the top here it should list and tell you exactly which bit version you you're using I'm using 64 but you might have 32 just make sure that you download the appropriate version of power bi for whatever bit version you have on the right hand side you can choose to sign up and install or enable your power bi online features this is only necessary if you don't already have an office online account through your company if you have an office online account through your company it's likely that power bi is a feature that is already possible if not already enabled in this case you'll just want to go up here and click on sign-in once you click on sign-in it may ask you for your credentials just go ahead and type in your login and password and once you've done that it will take you to the power bi online suite now that you've actually gone here the power bi tile in your waffle should be enabled so anytime you're in office online you should be able to click on your waffle in the upper left hand corner and among all of your lists of other applications somewhere in there you should see power bi there's my power bi in addition to power bi desktop and power bi online we of course will also need the Excel Power bi features for this course you will want to have a modern version of Excel that means Excel 2016 2013 or 2010 depending on the version of Excel that you have you may need to download certain Adan's we've already talked about getting the desktop program and we've already talked about getting the online account so now we need to know what version of excel we have and what add-ins might be necessary if you have Excel 2016 no additional add-ins are necessary everything is already built-in however if you have Excel 2013 you'll need to download and turn on the power query add-in and if you have Excel 2010 you'll need to download and turn on power query and powerpivot you can do this fairly easily by opening up your browser and just googling power query add-in for Excel and putting your Year in there as well so here's my example for Excel 2013 and I'll see right here that it says download Microsoft power query for Excel now this is an an official Microsoft site you'll want to make sure that you choose an official Microsoft site that says Office Microsoft com I'll go ahead and click on it I'll click on download' now here of course I'm just going to make sure that I'm choosing the correct bit version for my version of Excel here's my matching version so I'll click on that box and I'll go ahead and click Next until it's downloaded once it is downloaded you will also need to open up Excel go into your file tab go down to options and add in the add in so find your Add Ins item here and these power bi features are in a collection of add-ins called com add-ins which you will find from this manage drop-down menu at the bottom then you'll want to click on go and at this time you can click on the box corresponding to the feature that you're turning on and click OK once you've done that you should be all set please take a moment to pause the video and ensure that you have all of the above necessary items to follow along with the course and then we'll get started with an overview of what power bi does the BI and power bi stands for business intelligence business intelligence tools analyze data to help businesses make better business decisions power bi is microsoft's new business analytics service what's even cooler is that it's free and it puts the work of analysis into the hands of the individual for a long time bi services were considered expensive and usually done by teams or outside services historically it took a lot of people and a lot of money to get it done but power bi is a self-service business intelligence tool meaning you or I or any one individual has business intelligence power at our fingertips the visuals in power bi are leagues beyond the visuals in excel even with all the new additions to Excel 2016 starting world plus nothing is more impressive than a dashboard the hub at the heart of power bi is offering power bi is also a competitor to tableau who has held the majority market share in the BI world for a long time tableau is amazing and it also costs money power bi is free and easy to use for anyone which is why it's getting tableau a run for its money this course is designed to disperse topics into four days each day consists of a few hours of video and a couple hours of lab work mixed in you can expect to spend five to six hours in each day's agenda of course it is totally up to you how you choose to direct your learning path that is the beauty of these online videos if you follow the straight path you will flow through the following topics in order day one is all about getting data ready for well-presented analysis the wonderful thing about power bi squaring feature is that you only need to set this up once with each data set you won't have to do this again and again every time you get new data like in Excel or other modern spreadsheet programs once the data is cleaned and organized you'll be ready to do some simple analysis using power bi is visual design canvas will step into the basics of report design and learn how to build customized and sliced visuals day two is when we get into data modeling if you've ever chatted long enough with a database person you've probably heard that the world would be a better place if we all used sequel instead of having thousands of Excel files roaming around not agreeing with each other the data modeling feature in power bi and Excel is a sequel engine for each program in many ways it's a fix for this long-standing problem it also fixes all kinds of long-standing annoyances like allowing pivot tables to have more than one table as a data source and you can build your own custom functions called measures at the end of day two you will be building beautiful dashboards and sharing them with your organization in day three many of the simple querying strategies that we learned in day one are looked at through the lens of real-world complications when these complications arise what tools can we use to address them there is an unpaid feature you will definitely want to know about and being able to create a custom calendar once and use it anywhere is going to come very much in handy we will also troubleshoot the very common problem in all data model technologies of complicated relationships something that we will see and fix firsthand day four is all about preparing for real-world usage there are strategies for handling the most common measure problems out there today including comparing a selected portion to an entire population this is a trouble spot in a lot of technologies easily tackled with a couple of Dax functions when preparing dashboards for real-world usage by consumers we've got some great tips and tricks including designing for mobile consumption creating custom filters and slicers and creating security measures to allow certain teams of people access to certain information and not other information we have a whole new way we're going to be collecting data analyzing data and presenting findings the future of Excel is power bi let's get started welcome to day one in the real world you are probably pretty familiar with your own data sets but in these training environments you're usually not as familiar with the data so I would like to start off by getting us a little bit more familiar with the data we are using the contoso data set it's a popular training data set created by Microsoft a long while back and it's used in a lot of trainings contoso is a fictional retail company that sells their products worldwide by the end of day 1 you will have transformed local data web data and a little bit of curiosity into studying visual analysis and business insights you will be able to make the reports displayed here our goals for day 1 are to analyze our company's wages we're gonna perform multiple analyses to discover some insights about that and we're going to visualize those analyses to make it consumable by other people in order to achieve that we're going to need to fetch data from the many different places where we keep it many different Excel files we have some in a text file we have some information online and we're gonna need to clean up that data so that we can analyze it we're gonna need clean it up normalize it organize it combine it all that stuff once it's in an analyzable format we're gonna use power bi to discover insights about that data we're gonna learn to use a few different very common and important visual types of power bi and organize our insights into a convenient report stay tuned and we'll get started if you haven't already heard of a query you'll want to be a little bit familiar with what they are cuz we're gonna be using them a lot a query is a request for data so when you're submitting a query you want to ask the following questions what data do you want to retrieve where does that data live how do you want to organize it or normalize it or combine it now this is power query specialty and where do we want to put it the feature in power bi that allows for querying is commonly referred to as power query power query was actually the name of the tool before Excel 2016 when Excel 2016 came around it is now referred to as get and transform and then of course in power bi is called something else as well so this gets a little bit confusing and so we will want to clear this up right from the get-go here and we'll talk about where power query is in each one of your features in power bi itself power query is built into the tool but it's not called power query it's called get data and it's also called edit queries so get data is the feature that's actually going to retrieve the data in the first place and then if later on you decide that you want to change something about that query you can always go to edit queries and that will allow you to modify existing queries if you're using Excel 2016 those tools have been rebranded so they're no longer referred to as power query or later we'll talk about power pivot as well they've been rebranded and now in Excel 2016 on the data tab of the ribbon on the left ish hand side you'll have a command grip called get in transform this is going to be the feature that allows you to submit new queries and also allows you to modify existing queries now if you're using Excel 2013 or 2010 you're going to need to download the power query add-in and once you add in that add in it's gonna show up as an extra tab of the ribbon called power query however you're getting to it in this course just remember that the feature commonly referred to as power query has actually been rebranded but you're really looking for the word query anytime you see the word query that's gonna allow us to edit and use power query for our first exercise what we're going to be doing is taking all this data that we have on all these different places and we're gonna be combining them inside of Excel later on we'll do an exercise where we do the exact same thing but we combine all the data inside a power bi it really doesn't matter which way you're doing it it's gonna work the same because remember it's the exact same engine it's just rebranded by Microsoft so the real question is what do you want your end result to look like if you want your end result to be populated in Excel then you're gonna want to issue your queries all from Excel however if you want to be able to do some nice visualizations in power bi then you probably want to issue all of your queries from power bi in our first scenario here we're gonna issue all of our queries from Excel now we have our data in many different places so we have some data coming from our North America division we have some data coming from our Europe division we have some data coming from our Asia division each bit of data is coming in as an excel file we want to combine that all inside of one excel file and then we're going to need to fetch some extra data that's not even in Excel we're gonna need to fetch some data that's hosted on an HTML page we're also going to need to grab some data that is hosted inside of a txt file we're going to need to combine this all inside of the same table and power query is going to be our magic tool to accomplish this so this is the part that you'll want to follow along with we're going to be querying some data and we're going to be placing it inside Excel I have Excel 2016 open and so this tutorial is going to be about how to fetch data from all those different places and put it inside of Excel 2016 I'm going to start from the data tab of the ribbon because that is where I have my power query feature also known as get in transform I'm going to issue a new query from scratch and I'm going to query from a file specifically from a workbook because I have three workbooks to query in this exercise if you have downloaded the work files all of those workbooks should be inside of a folder called student files if we click on student files you will see a file called geography Asia we're gonna start with geography Asia and then we'll do geography Europe and then we'll do geography North America but we'll just start with geography Asia first so I'm gonna click on that and then click on import so this is our first preview into power query this is the Navigator window that allows me to select different types of data that I have stored in different ways inside of that particular file you'll see that I have something called table one now inside of table one I have a lot of data in here what this means is that somebody actually went in here and grabbed a set of data that they put in here and then they chose to format it as a table it's possible to do this inside of Excel now normal data doesn't actually need to be formatted inside of a table if we click on geography Asia you'll see that there's no table reference here whatsoever this is just the loose data as it's stored inside of the excel cells in that excel file I'm gonna choose geography Asia and the only reason is that I like that the name is already there and if I click on the name right there I'm sure that the name is gonna be pulled in along with this query so it's gonna be a little bit more obvious to me what data I'm pulling in so I'm gonna choose geography Asia and then I'm gonna go and click on either load or edit for our first few examples we're gonna click on load and that's just gonna load it straight into Excel later on we're gonna choose edit which allows us to do all of the magic power querying features inside a power query but for now we're just gonna choose to load it straight into the Excel cells let's just make a couple observations first of all on the right hand side I have this new panel called workbook queries and up here I can see that I have one query so far that is geography Asia there are 115 rows loaded there and there are seven errors if I hover over this I can get a nice little preview of what the query itself actually looks like which looks really similar to the data as its populated there on the spreadsheet right if I look here at the columns I'll see that in the column called actual there are a couple cells here that say error error error error error that's probably where my seven errors over here are coming from often your errors are no big deal but sometimes they are a big deal and so you want to investigate them later in this circumstance I know that they're no big deal so I'm not going to worry too much about it notice that you have a couple of other options here in your peek preview you'll be able to come over here and see the different columns that you've imported the last refresh there the load settings that you have right now we're choosing to load it straight to a worksheet and then the data source for this you can also choose to view it in the worksheet or you can edit that query or if you click on the little ellipsis here you have a bunch more options all of these options that we've just covered are also viewable by simply right clicking on the query and then you get a menu of options that show the exact same thing all right we're gonna start off by just importing those three queries so we have two more queries to import geography Europe and geography North America I'm not going to create a video for this I'm gonna let you do this so this is your chance to get an exercise in the new tools you're gonna pause the video here you're gonna go to your data tab you're gonna start a new query from a file from a workbook and then in your student files first you're going to import geography Europe and then you're going to import geography North America once you've actually chosen the file you'll click on import and then in your Navigator window make sure to choose the sheet on the left hand side and click on load go ahead and do that and come on back I'm gonna do a couple of handy things here these are not necessary to making your queries work in the real world but they're just ways that I like to see my data if I have queries populated in three different sheets I really like for my sheets to be named appropriately so I'm going to rename my sheets geo Asia geo Europe and geo North America the other thing that I'm gonna do right now is I'm gonna save and save often it used to be that we had to remind ourselves constantly to save files lest we lose our hard-earned work not so much these days because we do have recovery features however we're doing so much cool stuff I don't even want to lose one step so I'm gonna make sure to go back to the 1990s and save and save often so I'm gonna click on my Save button and I'm going to save this file as geography I'm going to make sure to store it inside my student files folder and let's keep going so now that I've set this up so that visually it looks kind of nice I want to actually start to clean up the data so that it looks kind of nice let's make some observations about our data really quick in our geography Asia query we have a hundred and fifteen rows loaded and it looks like we have geography key geography type continent ID it looks like whoa I have a totally blank row there that's probably not good that's gonna mess up the number of rows that I have loaded right I really should only have one hundred and thirteen rows loaded if I have these two blank rows here in addition I'm gonna look at this column right here and say hey that's supposed to be state and region hmm there should probably be separated into separate columns now why would I know this well it has to do with some really common normalizing strategies that we want to employ inside of our data if we use these normalizing strategies we're gonna find it's a whole lot easier to analyze our data let's talk about some common normalizing concepts that we want to employ in order to make our data beautiful data we're gonna start by making sure that we reduce our data down to the smallest meaningful value this means that in that column where I have states and regions I'm probably going to want to separate that into two different columns let's take a look at some of our other strategies here removing unnecessary data eventually we're gonna have a lot of data in here and the more data we get the more work my computer has to do to keep up with all of it so if I can remove the data that I don't need my program is going to be a lot happier we're also gonna handle some errors different kinds of errors should be handled differently but they should all be addressed before moving on you want to make sure that none of your errors and there are actually errors that are gonna get in the way of the analysis that you want to do later we also want to make sure that we have compliant field names we don't want any field names with weird characters we also want to make sure to standardize our data we also want to standardize our data types this means that each column of data should have the appropriately applied data type meaning dates should have a date datatype text should have a text data type and numbers should have a number data type when we start doing some interesting data modeling down the line we'll want to make sure that we have created key or ID fields for every single table all of these normalizing strategies can be realized very easily with power query now remember the whole point of using power query for this is that it's going to memorize all of our normalizing strategies as we complete them meaning in the future if we get a new set of geography Asia data or geography North America data power query is gonna have memorized exactly what it needs to do with that data to normalize it let's get started before we get started normalizing I really want to make one point really clear you really don't want to normalize any of your data before you bring it into power query that's the magic of power query and power query is going to memorize it so if you're used to doing a lot of these normalizing strategies in Excel try and encourage yourself not to do those before you pop it in to power query and get power query to memorize it for you that way you won't have to do any of this by hand in the future that's the idea okay let's go ahead and get started the first thing we need to do is combine each one of these tables together and the reason that we need to do that is that this is exactly the same data it's just being submitted to us from different regions so we have a table called geography Asia it has geography a geography type city name actual state and region status locations employees if I go to each one of these other tables I'll see that the exact same data is populated here if it's the exact same data it can absolutely belong on the exact same table and if we put it in the exact same table that means we can throw it into a pivot very nicely very easily and start to do some very easy analysis on this data so that's the first thing that we're gonna want to do is combine all of these queries together in order to create a query from an existing query I can right click and choose reference what that means is that it's gonna use the results from the existing query and issue a new query using the results from geography Yuja meaning I don't have to start geography Asia all the way over from scratch right I can just use the results from geography Asia but the safety measure is that it's actually everything I'm gonna do next is stored in its own query separate from geography Asia so I'm gonna right click on geography Asia and choose reference that's going to create a separate query for me here and now we are in the query editor welcome all right in my query editor window I'm actually going to expand my query editor window so that we can see everything we got here the query editor window is so cool first of all on the right hand side you'll see any query settings that you've already applied including the queries name now I don't want this query to be named geography Asia - I actually want this to be called all regions because eventually what I'm gonna do is combine the data from all those other tables into this one table called all regions alright the next thing I want to do is take all this data that was the result of geography Asia and actually take the data from geography Europe and geography North America and pretty much add it to the bottom of the list just making one very large list the process by which you add one queries data to the bottom of an existing query is called a pending queries and that's exactly what we're gonna be doing so in the query editor on the Home tab of the ribbon on the right hand side I'm gonna look for the button called append queries that's going to allow me to add Europe and then later North America on to the bottom of this list alright I'm gonna click on append queries there and now I have the option to append queries alright so I already have geography Asia data here I'm gonna choose geography Europe now I actually have more than two tables I want to append together I have the existing one geography Asia plus geography Europe plus geography North America so I'm gonna click on this little button that says three or more tables and I'm gonna choose let's see Europe is right there North America I'm gonna click on and click on add and now all of those three are going to be appended together clicking on OK as I apply each one of these normalizing strategies you'll see on the right-hand side there's an area called applied steps and I can actually see each step as I apply it if I make some kind of a mistake and do something crazy and suddenly it looks all weird I can remove that step by clicking on that little X right there I'm not gonna do that right now because I like the way that this resulted and if I scroll down I can see some of the information here looking on the lower left hand side I can even see that there are 18 columns there are six hundred and seventy three rows imported that's the combination of all three of my queries so I know I'm on the right track all right this is good for now I'm gonna go ahead and close and load if I'm happy with the query as is I'm gonna close it and load it now keep in mind that your close and load button is on the Home tab of the ribbon so if you go to one of these other tabs you're not going to find it over there so go to the Home tab of the ribbon and click on close and load you have a couple of options and here you can either close and load just like normal or you can close and load to an extra special place we're going to start just with the basics and we're going to close and load if you haven't already done so please pause the video and make sure that you append all of these queries together we started by right-clicking on Asia we went down to reference and then we ended up in our query editor window we renamed our query all regions and then we went in and we appended queries together make sure that you've completed it and then come on back all right if you spent any time actually delivering this content to end users who are supposed to be able to look at it and understand it you'll know right off the bat the continent one isn't gonna make sense to anybody except for us now our continent names there's actually a catalog of continent IDs and continent names and that's maintained by somebody at our company and they host that data online occasionally we'll need to remove continents or add new continents as we move into different regions of the world so that data is is populated and maintained by a particular team meaning we can't actually populate that data here inside of our Excel spreadsheets or directly here inside a power query what we're gonna do is fetch that information from the online table where it lives so we're going to go ahead and query some all-night information I'm going to start with new query and this time we're going to go to from other sources and look right there we have a from web option the URL where we have this data populated I'm gonna type it in here just remember that if you're doing this in the real world you are gonna want to go to your browser and navigate to that site before you get here otherwise you're gonna have to populate every single character yourself so normally what people do is they go into Chrome or Explorer and then they'll actually go to that site copy the URL and just paste it right here however I'm gonna go ahead and type it in and I suggest that you do the same okay if you haven't already done so please make sure that you pause the video and type in the entire URL including this uppercase F remember that uppercase F is going to need to be uppercase because a lot of URLs are actually case sensitive once you've done that go ahead and click on OK the resulting Navigator window will allow you to choose from the different elements that you have on that HTML page the first item you'll see is the document this is just metadata about that HTML page but you'll start to see tables there most people populate data in HTML tables if they populate data in HTML tables you're going to be a lot happier of a camber however if they populate data just loosely outside of tables you can still query that information it just takes a little bit of extra work to get it into a nice clean table clicking on tables 0 here I will see that I've caught in that IDs that's was actually in my data and then I have matching continent names here so that's what I'm actually going to try it a fix to these continent IDs that are already in my data that way I'll have the matching continent name populated right next to the Contin ID and then when I actually create an analysis for my end users they don't have to look at continent 1 they can look at continent Asia and that's the whole idea alright I'm going to load this data in tables ear is a very confusing name for me so I'm not gonna want to name it table 0 I'm gonna want to double click on this get back into my query editor and name this something straightforward like continents and then I'm going to close and load if you haven't already done so please issue another query start from the data tab you're going to issue this query for the online data that we have here and you're going to go and you're going to go into new query from other sources from the web from here you're going to type in the entire URL you're going to click okay and then choose table zero from the Navigator window and click on load make sure to rename that query countenance that it's something that makes sense to you go ahead and do that now and come on back now that we have our continent data we're going to want to fix it to the existing data in all regions so over here on all regions we have the continent numbers and we want to fix the continent names from this continents query into this data but like before we don't want to actually replace the entire existing all regions query with this new information what we want to do is start from the results of all regions and go down to reference that will reference the results of all regions but it will allow us to include the continent data I'm going to name this all regions and continents so far I have all the region data that's really good and then I have the continent IDs but I want those continent names and I'm going to need to use a feature that's actually going to allow me not to add data to the bottom where do you figured out how to do that that's called append what we're gonna do is try and figure out how to add data line by line to match with the continent IDs this is a feature called merge if you've ever used vlookup in excel it's the exact same concept alright so we're gonna go ahead and merge our queries clicking on merge queries there and we're gonna choose to merge all regions and continents with our continents data now when you use this feature keep in mind that you have to choose the column from the parent table that matches the column in the table that you're fetching data from when you do that you may get a notice about privacy levels this happened specifically if you query information from outside sources and you try and combine it now when we do this we're gonna want to make sure to take our all regions and continents parent table and choose the column from that table that's gonna match the table that we're fetching information from the table that we're fetching information from is continents and then I can see the matching column there is continent ID when you choose the matching column often you'll get this privacy levels window that shows up this happens anytime you are combining privacy levels between two separate sets of data so if you think about it like this your Excel files are rather private but you fetch some information from online which is not private so you want to make sure to mention the privacy levels here so I'm just gonna select public for our HTML table there and click on save now I can continue alright so I chose my parent table all regions and continents this is my main table but I'm fetching information from the continents table and then I made sure to choose the matching columns between those two tables they have the matching values continent ID 1 1 1 1 1 1 and somewhere down there is 2 & 3 and then over here I have continent IDs 1 2 & 3 further down you'll see join kind now depending on the version of Excel that you're in and depending on the version of Windows that you have you may or may not have this drop down window here if you have Windows 10 in 2016 then you get this window here that's asking for your join kind a left outer comes from sequel or at least it's commonly used in sequel and this is the kind of join that you're doing for our exercises we're just gonna choose full outer because if we choose full outer I'm sure that anybody watching this video that's using Excel 2010 or has Windows 7 or something like that will have the exact same feature applied there where they don't actually have the option but it's a full outer that's automatically chosen so we're choosing full outer and you'll see that it says the selection has matched 671 out of the 673 rows now regardless of how many it's matched it's going to populate all of their responses and that's exactly what I want here so I'm going to go ahead and click OK so we've just merged these two tables but I have one additional difficulty and that is that this column that got populated here that's supposed to have the matching column names it's actually giving me these little words that say table table table table the reason that that is happening is because I am fetching information from a table now there were three different columns in that table right I'm fetching information from over there and trying to populate it over here but over there I have three different columns that I could populate in this spot and power query doesn't understand how to get three different columns to populate in the space of one column here so what I have to do is actually choose the column over there on that table that I want to be populated here I can do that easily by clicking on this little button in the upper right hand corner of that thing that says new column and I'll choose the column that I actually want populated there I'm gonna deselect by clicking on select all columns and deselect that and I'll choose the continent name now one more thing I'm gonna do here and I'm just doing this because I have the experience with it there's this kind of annoying thing that I don't like you might like it though um that uses the original column name as a prefix there so what is gonna say is new column dot continent name I don't like that so I'm gonna unclick that box and click OK now it's called continent name and I do have the continent name there that's gonna make it so much easier for my end-users to understand what continent I'm looking at now just to keep things well-organized I'm gonna take this column and actually move its location I'm gonna click and drag on the header there and move it all the way back to the beginning so that it's right next to the continent ID and I'm just doing this to make myself happy or later on so I don't have to see continent number and then be like oh my gosh where's the continent name I thought I and then I have to look at the end of the list and I'm like oh there it is right so I've had some experience with this so I'm just gonna make it easier on myself choose continent their continent name right after it and now I'm happy alright this is exactly what I wanted so I'm gonna go click on close and load and then you'll see that my all regions and continents tables over there I have my continent IDs my continent names that's exactly what I wanted if you haven't already done so you want to make sure that we have this query here so you'll want to pause the video and make sure that you start with all regions right click and reference that query and then you're going to be merging in the data from the continents table you'll click on the columns that match in each one of those tables you will need to mention the privacy settings which I set to public and you'll click OK and you want to make sure to rename this query as well something that's really obvious like all regions and continents you will also want to expand this column because that's not quite enough ok now we will also want to make sure to expand this column here so we're gonna click on this little button up here and we're gonna choose exactly which column we want to populate data from so continent names is what we're looking for here and I'm gonna unclick this box that puts the extra name at the beginning that I don't like and I'm gonna click OK now I have the column of data that I wanted and I'll just drag it all the way back here to the beginning so that later on I can see it populated right next to the continent IDs go ahead and do that and then click loads and load and then come on back all right we're gonna have to start to clean this up this looks pretty good we have all the data that we need but it's not exactly always in the format that we need for instance we have a couple of rows here that are completely useless these are totally empty I don't need that I also have a couple columns here called CD name an actual if you scroll down you'll realize that they have the exact same data I also have a column that says status valid this just happens because the data needs to be considered valid for me to even import it from the data dump and this happens when people actually generate the data all the way back in their individual continents but it ends up coming in like this and just saying valid valid valid I don't need that right I'm never gonna need that so I'm gonna want to remove that I also have a column that says state comma region and then I have some estates and regions here looks like Armenia is just a region Australia is for whatever reason considered just a region and then I have Beijing China here either way state and region are being separated by a comma most of the time and it looks like it's actually a comma space if you look really closely at it but sometimes in a couple areas for some reason the people over there are putting in a semicolon instead of a comma so this is a little bit frustrating because you can imagine in normal Excel if you wanted to separate these values you would have to use something like text to columns right now in the querying feature there's actually a really easy feature called split columns that'll do it for you too way easier than text to columns however both of those features do require you to use the same delimiter every time so we're going to need to deal with this problem first where we have commas and semicolons and then we got a couple of other issues we got to deal with we have some date data in here that's not being formatted correctly and then we have some other numerical data that's not being formatted correctly we got a couple columns over here called location two and location private ventually those are going to need to be combined together all right so basically what we're doing is we're following all of the normalizing concepts that we've already discussed and we're gonna do that in power query right now all right let's start by launching our power query editor window you can do that by either double-click your query or you can right-click on it and click edit or you can hover over it and when you get that peak window you can also choose edit here whichever way you like best use that way from our query editor window we're gonna start by removing some of the data that we don't need so for instance here I'll see city name an actual seems to have some errors in here right but I also just completely don't need it because it just populates the exact same data from city name who knows whatever reason somebody needed that but I don't need it anymore so I'm gonna choose to remove this column you can remove columns any ways that you want you could right-click on it and choose remove column or you can click on that column and find your command on the Home tab that says remove columns I also have a column called status I'm going to remove that one as well if you haven't done so already please remove the columns called actual and status and then resume the video all right one of the other problems that we have is we have some blank rows in here that's going to be a real big problem for me down the line because it's going to in accurately populate some statistical analysis that I'm going to do so I want to get rid of those useless rows here so I'm gonna find this button that says remove rows and then you'll see one of the features in here says remove blank rows this means that an entirely blank row in any row of my data is going to be removed and I'll go ahead and click on that if you haven't done so already please pause the video and remove your blank rows let's just make an observation here as we're continuing on let's just make an observation here as we're continuing on on the right hand side you'll see remove columns and removed blank rows that's the two steps that we just did so if for some reason you accidentally removed a column that you didn't mean to you could simply click on this little X right here and that would undo that particular action now keep in mind we actually removed two different columns there but we're only seeing one line item that says removed columns now it just so happens that if you accomplish multiple of the exact same feature such as removing columns and then I removed another column and then I removed another column it's actually all included in the exact same code block in day three we're gonna get into that code block and actually take a look at it and see how it works but it's just something to make note of here all right let's continue with our normalizing strategies I want to split state in a region so they're actually in two separate columns but before I do that I'm gonna have the problem of the semicolons not agreeing with these commas so I'm gonna start by using the feature called replace values clicking on replace values and I'm gonna say every time you find a comma replace that with a semicolon and I'll click okay and that at least will normalize all of my de limiters there now they're all a semicolon now there's no reason to end up with a semicolon you could have done it the other way around and said hey instead of having semicolons replace them all with commas you know whichever way you want to do it is fine just so long as they're all the same delimiter now that they are I can go to my split columns feature and actually split my states and regions so I'm going to select that column and split by a delimiter and I could just choose comma or semicolon here from the drop-down menu but I actually have more than that if you look really closely I have a semicolon and Dennis space so I'm gonna go down here to custom and say I want a semicolon and a space okay I'm going to choose that each occurrence of the delimiter and click OK if you haven't done so already please select the column called state region replace your commas with semicolons or your semicolons with commas whichever way you want to do it and then go ahead and split your columns by a delimiter and it'll be by a semicolon space all right now I'm gonna take a second and just clean this up a little bit because I have regions and states in here and I want to make sure to separate them so I'm gonna say region these are my regions and these are my states and now I'm gonna be able to separate that data when I'm actually doing analysis later if you haven't done so already just go ahead and rename those columns all right if we scroll to the right a little bit you'll see a couple of columns called LOC 2 and LOC 5 this really means location 2 in location 5 our company certain regions need to use their location 5 data because that gives them a lot more data about all the different regions in their area however from where we are in corporate we tend to pay attention to location 2 because this is a much more high-level overview of the business that's happening in that particular region so we use location 2 they use location 5 however we're gonna need to make this data consumable by everybody in the company so we're actually gonna combine these into location 2 in location 5 and in our case that's totally fine because everybody understands that they're actually supposed to go together so I'm gonna click on location 2 and location 5 and I'm selecting both of those at the same time by holding down my shift key shift then click on the second one and you'll see that I don't have a combine feature in here because I'm on the Home tab of the ribbon and on the Home tab of the ribbon you have most of the popular stuff however if you wanted to transform your data you'd go to the transform tab of the ribbon and on the right hand side or rather what should be right in the middle you'll see a feature called merge columns I'll click on merge columns I'm not gonna use the summer a separator and I'm gonna call this location ID and click OK if you haven't done so already take a moment to pause the video choose location two and location five go to the transform tab of the ribbon and merge your columns together into one called location ID the other feature that we talked about in our normalizing strategies the other strategy is to make sure that all of our data is actually using the correct data type here I'll see that my load dates if this is supposed to be a date that is definitely not looking right when you start to use data between power query and power pivot and then power bi and all the other ridiculous power names that you have going on here they're gonna need to communicate to each other using the data type so it's going to start to be really important to choose the correct data type so for my load date here on the Home tab of the ribbon in the transform command group at the top you'll see an option to change your data type right now my load date is being formatted as a decimal number but that's not what I want I want a date data type now you do have a couple of ones you have date date time and date/time timezone whatever you use just make sure that everybody's gonna understand this is the most simple date formatting for this set of data if I actually care about the time in my load dates and I definitely want to use date and time however if I don't care about the time and nobody in my corporation everies the time in my load dates that I'm just going to choose date and that's going to format it to a simple short date format like we see in Excel so you want to do that for both of the columns here you have load date and then you have update date if you haven't done so already now is the time to pause the video and make sure that you choose load date and update date and get them to be the date format all right we are just about done here I'm pretty happy with the way that this is looking and so I'm going to click on close and load now when we clicked on close and load notice that so far we have just been using the very standard close and load feature but there was another option in there that we haven't really talked about which is close and load to now we've created a lot of queries that are all dependent upon the same original data set here so let's talk about some strategy when it comes to containing all this data in Excel so far each one of these queries have been loaded into a spreadsheet which means all the data in that query is actually posted somewhere in a Cell in Excel so that's a lot of data for Excel to contain you may not know this already or you may already know this but Excel is not actually a mass data manager it really doesn't do well at handling mass data however it has a partner tool called power pivot that we're gonna spend a lot of time in tomorrow or in day 2 rather that actually does the mass data handling the power pivot feature uses creates modifies maintains and manages what's called a data model if you load things into your data model that's actually a separate location just designed for data storage so while Excel doesn't do data storage very well your data model does data storage beautifully and so if you choose among your load two options if you load to the spreadsheet that's the default option load too close and load loads to the spreadsheet that means it loads it as a table that's the default option however if you chose the other option here you clothes and load too you could choose to load it to your data model now you can't do that because you've already chosen to do this so if you wanted to change your mind you actually have to do it from Excel all right so if I right click on any query and go down to load two I can change how I'm actually loading it so I'm loading it to a table or I could choose to only create a connection that means it remembers all the steps of the query but it doesn't actually populate the results of that query anywhere and you may be wondering why would I ever use that well often people want to create queries that are using queries that are using queries that are using queries and all those queries take up a lot of space so if you think about it our original query geography Asia we don't actually need that populated here at a table right that's pretty useless because we have an end result that we're actually going to be using called all regions and continents so that means that geography Asia doesn't actually need to be populated on a table because I'm not going to be using it so if I go to load two and choose to only create a connection it says possible data loss warning that's only telling you that you're gonna lose the data it's actually in the spreadsheet but it's still gonna send the results of geography Asia all the way down to all regions and continents that's really what we want we don't need to in some spreadsheet so I'm going to go ahead and click OK so you notice that that sheet that called geo Asia that actually had the geography Asia data is now not there so the data is not taking up space so really I could actually do this for all of the queries that we have created so far except for the last one called all regions and continents so I'm going to do that if you want to follow along with this example you want to right-click on almost all of your queries and choose load two and then only create a connection if you've done this with me you'll notice that all of your queries now say connection only connection only connection only and then the last one all regions of continents that we actually do want populated says 671 rows loaded and if I go to that query there's all of my data right there and this is the fully normalized beautiful version of all of my data I don't want all these previous junk versions of my data that I had to clean up so I can go ahead and make those connections only this is going to save a lot of space in my spreadsheet all right we are at the end of our normalizing exercise I'm gonna go ahead and save this file I suggest that you save it - I'm gonna save it as geography and in a few moments we're gonna do a lab optional lab meaning if you don't do it it really won't have any effect on the rest of the class however it's a good practice so what we're going to be doing in this lab is looking for information online about the average American household income in each state first thing we're gonna do is find the information about the average American household income in each state you can do that by googling that information or you can go to the URL that I've typed on here after that you will want to normalize your data the most important thing about normalizing your data is making sure that your numbers are being formatted as numbers and not as text when you're done you'll make a pivot table and it should look something like the pivot tomb on the right hand side remember if you can't figure out how to do this or you don't feel like you want to do this it really doesn't have an effect on the rest of the class good luck welcome back what we've done so far is quite impressive and super handy in the real world I would probably now need to figure out how to present my findings in Excel we have a few different options for this the most popular visual analysis in Excel is of course pivottables and pivotcharts you've probably already found that these tools as amazing as they are lack a bit of showmanship and they also have limitations in terms of different visuals created from the same dataset the following lecture is just that it's a little lecture you don't have to do any of the following examples in Excel with me I'm going to discuss some limitations here actually so I'll be asking you to join in again when we start discussing how to circumvent these limitations using power bi now I am in no way ever going to bad talk pivot tables they have been the most important end-user analysis tools in data since the early 90s in the days of Lotus if anybody remembers that they are no less important today however they do have some limitations namely the visual options you have for instance let's say we wanted to take this great set of data that we created and we want to look at our projected revenue versus our actual revenue and maybe we want to be able to separate it out by States or by regions or something like that all I have to do is create a pivot table from my data now in modern Excel you don't actually have to select your entire table you just need to select one cell inside of your data set and then you'll go to the insert tab and find your pivot table button on the left hand side this is going to insert a pivot table into your workbook here I'm gonna make a couple of specifications here I'm going to select this entire table of data it does a pretty good job of selecting that and I'm gonna choose to place it on a new worksheet there's going to be lots of examples in which we actually play with the other options here but I'm just gonna throw together a quick pivot for a little tutorial here all right so within my PivotTable interface on the right hand side I'm gonna have all of the different fields that were in that beautiful set of data that we ended up with after all of our normalizing stuff that we did so let's see I want to be able to look at our projected revenue here's projected revenue and my actual revenue as well if I wanted to look at those I could just choose those fields and place them in the pivot lets see projected revenue and actual revenue these are both calculable types of data so I'll place them in the values so I can drag them or drop them or in modern pivot tables you can also click on the little checkbox here and it makes a pretty good guess about where it wants to place it but it's not always right which is why I am actually in the habit of clicking and dragging but you can do whichever one you like and let's see we also wanted to look at the different states that we have here so States is a categorical data type meaning there are lots of different states that show up maybe one time or many times but they are not calculable states are not calculable they are categorical so I'm gonna grab those states and I'm gonna place them in the rows and let's take a look I'm looking at our meanie here Australia Boutin Canada China did looks like I'm actually looking at countries here or something like that but what I'm looking for is States as in the United States or North American states so maybe you'll need to swap around these two options here maybe I'll grab region instead and take a look at that all right I do see Colorado down here so that looks like we're going in the right direction later on I might actually have to change my data here but we won't worry about that for right now what I am worried about is making sure that I'm only seeing continent what I am worried about is that I only want to see information for the North American continent so I'll grab my continent name field over here and I'll place it into the filter that's gonna make it really easy for somebody to come up here and filter just by North America all right so let's see what we have we have Alabama Alaska Alberta Arizona British Columbia this is looking pretty good so this is along the lines of the analysis that I wanted to do but now I need to be able to see it on a chart I want to visualize the storyline here for maybe myself or for somebody else if I wanted to put a pivot tables data into a chart there's a special chart type for that they're called pivot charts and those are the only kinds of charts that can be generated from pivot table data so I'm gonna go to my pivot table tools analyze tab up here at the top remember if you have an older version of Excel like Excel 2010 your tab there is not gonna say analyze it's gonna say options in that case so you just might have to look around a little bit to to find the appropriate tab of the ribbon there and I'm gonna choose pivot chart and I have all these different options for visual types that I can choose let's make a column chart I'm gonna click on OK all right so this is looking pretty good so far I'm gonna resize this so I can see it a little bit better all right now that we've got this let's say I also want to be able to see the difference between projected and actual on another chart well over here my fields actually have a column called difference so that would be really easy I'm just gonna create another chart and put difference on that chart right unfortunately that's not how pivot tables work if you want to create another chart and show difference on it you actually have to change the data that's represented in the table because all the data that's in the table is gonna be directly reflected on the chart and vice versa anything I put onto this chart is going to be directly reflected on the pivot table here's where the pivot table limitations are realized if I want to look at a different measure I'll need to make a separate pivot so that means if I want five different visuals with different stories on each I'll need five different pivots on this one sheet to show them all right next to each other and in walks power bi this is the report or one of the reports that you will be making by the end of our first a here's our projection versus actual by state is going to be in a nice-looking column chart and then we also on the left hand side have a bar chart that is showing States but it is showing the difference there and we'll get a lot more used to it but imagine that you're the end user and now you're looking at this data and you want to be able to slice it for maybe you just want to look at California clicking on California will slice all the other visuals to show just the data that adheres to California fornia again we'll remove that filter from your entire dataset on the left-hand side I have a visual displaying all of my states by the difference between projection and actual which I've titled sales over projection and down here you'll see that I have a couple states that were actually under projection and the numbers here I have this visual which is the original one that we created or that I created over here in the pivot table but I'm actually being able to see this side-by-side with all of my other measures evaluations and story lines this is the magic of power bi so now the question needs to be asked well why wouldn't you always use power bi the answer for that it really depends on your situation there are a lot of people who have been spending their entire day there are a lot of people who have spent their entire life analyzing data inside of Excel so making a transition to a new technology like power bi can be a little bit odd so this is a challenge that we will all run into when we are trying to promote adoption of power bi as a new technology within our company luckily most people are going to step into this realize how convenient it is to see all these measures side-by-side they will realize but that by clicking on things they can see different sets of data so now all we have to do is get all of our Excel data all this great okay so now our only challenge is getting all of this beautiful normalized data that we created queried normalized cleaned up inside of Excel and get this exact same data in power bi and you might also be asking the question do I have to do all this in Excel and then bring it into power bi the answer is no you can do all this querying right from the get-go directly within power bi so in the real world you're gonna start asking yourself where do I want to end up with this project what do I want the visual result to be by the time I'm done with this project if your end goal is actually to make a great-looking analysis like this all inside a one report then you're probably going to want to start directly within power bi and do all of your querying directly here in power bi however if you have people who are really expecting to see pivot tables then of course you'll want to do this exact same task in Excel we've already done this entire task in Excel so how would we do this in power bi stay tuned and we'll talk about that next welcome back let's open up power bi when power bi first opens up you're probably going to notice that you get a Start screen similar to the Start screens that you tend to see in Excel and Word and PowerPoint if you don't know what I'm talking about I'll show it to you when you open up Excel from scratch these days usually enter a Start screen first unless modern versions of Excel you would land in a blank workbook but now you get into the Start screen first and what it's supposed to do is allow you to efficiently move directly to the file that you want so here would be favorites here would be recents and then here would be the most popular templates that you use power bi has a very similar feature here's your Start screen it's just on a hovering window instead of its own separate screen but here on the left hand side you'll see a list of the recent power bi reports that you've made you can also open other reports if you don't see what you're looking for on the left-hand side right in the middle you'll see a couple of recommended videos to help you get started with power bi and on the right hand side you'll see a list of favorite articles and blogs from Microsoft and elsewhere if you don't like seeing the screen you can always click on show this page on startup we're not actually going to use into any of the features on here because we are doing a much more in-depth tutorial so we're gonna click on this little X button in the upper right hand side because this is your first time in power bi or it might be your first time in power bi you probably are not signed in up there you'll notice that I am signed in if you're not signed in then on the top right hand corner of your screen you'll want to click on the button that says sign in and type in your credentials and sign in go ahead and pause the video and come on back once you've done that let's get a basic description of what we're looking at in power bi first of all you'll notice this big white area right here in the middle this is what we refer to commonly as the canvas or the stage this is only going to be there if you are in report mode there are three different modes and we'll talk about each one of them but the first one called report mode you'll notice that if you go into report mode you have this canvas right here we have our canvas we also have our visualizations and fields pains over here on the right-hand side and then you'll also notice at the bottom of the page you see a tab that actually says page 1 this is much like the tabs in Excel in Excel we have different sheet tabs at the bottom in this case we just have different page tabs all of the pages altogether are referred to as a power bi report so this entire file altogether is usually referred to as a power bi report much like you would refer to an Excel workbook to make more pages of course you can click on that little plus button right there you'll notice that there are only a few tabs of the ribbon you have your file tab although it's not much of a backstage view like it would be in Excel you have an old-fashioned drop-down menu here you have the option to create something new open something save and save as you can import data we are going to be doing that you can also export data into a power bi template for instance and you have some more options in here that we're going to be playing with on the Home tab of course you have a ton of tools that people use most often all the way on the left hand side we have cut copy and paste just to the right of that we have get data so this is power query so all the querying that we were doing in Excel is located directly here to initiate a query from scratch you'd click on get data but you can also click on edit queries to see any existing queries that you already have and be able to edit those all right so we've taken a look at report mode here let's see what the next mode down is if I hover over it I can see it's called data mode and if I click on that I'll be able to see all the data that I have in the file here however we don't have anything so it looks very boring so let's go ahead and go and get some of our data now just so that you know every single step that we did in Excel you could do the exact same steps in the exact same way here in power bi so if you wanted to get that data if you wanted to do all those steps over again you could go to get data choose Excel and then you could choose each one of these individual geography Asia geography Europe geography North America files once you pull them in then you can merge them and append them to things that we did in excels query engine as well and then after you've done that you could do all the normalizing strategies all the things that we've already done but why do all of that work if you've already done it and saved it inside Excel so we're just gonna fetch the the end result from all the querying work that we did earlier today and that should be contained within a file called geography or whatever you named it I named mine geography so I'm going to click on that and click on open from my navigator window I'm gonna take a look at all the different options that I have here so I have a table called all regions and continents that I could choose from I also have a sheet called all regions here's one called all regions and continents continents Geo Asia geo Europe geo North America so these are all the different individual queries that we created earlier and they're populated result inside of that spreadsheet I'm gonna click on the one called all regions in continents because I know that's where my end result was in that file and I can even visually peruse it on the right-hand side just to make sure that everything looks good I have my continent numbers my continent names remember that's one of the other things that we were doing in there we got rid of a couple of messy columns in here looking pretty good so I'm gonna go ahead and click on load if you haven't already done so please open up power bi click on your get Data button on the Home tab of the ribbon choose to get data from an excel file choose your file called geography or whatever you named it that was the end result file from all of our work earlier and you'll want to choose the sheet that has all of the resulting information from your final query there mine is called all regions and continents hopefully yours is called the same thing because that would make it easy but if not just remember whatever it was called and then you'll click on load all right so now that we have all of that data if I'm in data mode it's still gonna look kind of boring until I actually click on a table that I want to look at so I'm gonna click on the table called all regions and continents and then I'll be able to see all the data from that table populated over here if you ever want to select one of the columns here you can just click on it to select that column and on the right hand side if you use this little triangle click on that little triangle to the left of your table name you'll be able to see all of the different columns of data in a stacked fashion here now I really like this view over here because I can just start to see some pretty important information here for instance the column called percent difference I know that that has calculable data because it has this great little icon right next to it indicating that that is numerical data I also have this column called actual revenue again that must be numerical or calculable data because again I see that Sigma icon right there now I also see the Sigma icon right next to continent I know that my continents are just continent IDs they're not actually calculable data and we'll talk about how to handle that in more real-world circumstances so you can explain to power bi this data is not meant to be calculated it's just an ID or something like that our objective today is to build some really nice-looking visuals and reports that's going to be able to tell a story about our data so far the story that we want to tell is projected revenue and actual revenue information and maybe the difference between those two about different states and we're going to be building these reports in report mode all right and the last thing that we need to do before we're ready to move on and really start building some neat looking reports here is grab a little bit of extra data because it turns out we actually forgot some today so we have all of our geography data which by the way says all regions and continents that's gonna be really confusing for me to look at I'm gonna title it something a little bit more obvious like geography I can right-click on that and choose to rename or I can just double click on it and rename it to geography if you haven't already done so please rename your only table in here rename it to geography we're going to be using this table a lot and you're probably going to want to have it name the same thing as mine so we've got this data the last thing that we need to do is grab a little piece of extra data that we're going to be using to do some further analysis which has information about all of our employees so we want to get our employee information for each of our different stores and stuff like that so we do need to fetch a little bit of extra data so let's go ahead and issue this new query directly from power bi we'll click on get data and it just so happens that our employee data isn't actually in a typical excel file it's in the super old-fashioned file called a CSV if you scan down a couple of items you'll see something that says text or CSV we can go ahead and click on that make sure you look in the folder called student files you may need to go into day one work files and then you'll see a file called employees gonna go ahead and open that and I could take a quick look here just to make sure this is all the data that I actually need and want and it looks like it is so I'm gonna go ahead and click on load if you haven't already done so please pause the video and take a moment to get our last piece of data that we'll need for today which is in a CSV file called employees you may need to go through a couple of folders here to make sure that you find it so in your student files folder you have another folder called student files and another one called day one work files and in there you will see the file called employees and go ahead and click on open once you have done that on the right-hand side you should see two different tables employees and geography alright just make sure that you have those two different tables and then come on back and we'll get started now that we have some data in here let's go ahead and make our very first visual over on the left hand side where we have our different modes find report mode so that we can get back to the canvas and create our very first visualization on the right hand side I have two different panels visualizations and fields much like in your pivot tables all you have to do is choose a field from the right hand side and place it in one of the wells on the left hand side in order to create a visualization I'm gonna go ahead and expand my employees table so that I can see a little bit of the data here that we can play with and I'll start by creating a visual from average state pay I have a couple different options for how to create my very first visual I can either click on the box now because this is a calculable type of field meaning there are numbers in this field it automatically puts it into a calculable visualization type I don't have to check on the box though if I would prefer I can click and drag average state pay somewhere here on my canvas and drop it and I'll get a visualization generated there if I want to add multiple different fields into my visualization I can go ahead and click on their boxes as well such as grosspay 2013 and maybe living wages to change the order that your fields are showing up in your visualization find your value well over in your visualizations pane and notice that you can just reorder your items over here by clicking and dragging them to a new location keep in mind all the fields that I have just added to my visualization are all calculable fields so they're all showing up in the value well however choosing a different field type will result in a different visualization look however if instead of layering all calculable fields in here if I started to layer some categorical fields such as state I'll get a very different-looking visual notice that my state field just went into the axis field area axis and legend field areas are typically used for categorical data types whereas the value field area tends to be most useful for calculable data you'll notice that the axis is the preferred drop zone for any categorical data type this is much like the Rose field area in a pivot table if you're familiar with pivots you probably notice that every time you click on something categorical it ends up in the rows field area Rose is like axis and if you've ever made a pivot chart you'll notice that they're actually called the exact same thing however if I wanted to I can click and drag this well down into another area and I'll get a very different looking chart at this point this chart hasn't done more than make it look rather confusing and color-coordinate my different categorical bars there I'm going to put that back in axis I just resized my chart by clicking and dragging on the anchors much as you would in any Microsoft program and finally let's go ahead and play with a couple more wells that we have down here by dragging and dropping something into color saturation I can change the way that my bars look depending on the value there so let's just say I want the color to change by average state pay I already have average state pay in my values but I'm gonna go ahead and click and drag it again in a color saturation and you'll see that my color does vary there depending on the value if instead I dragged and dropped this on tooltips then I start to get this really neat result once I hover over one of these bars here I will get a tool tip that pops up and tells me what that actual value is now in this case I didn't really need to do that because it turns out that any field that you've put in any one of your wells shows up in your tooltips so maybe in this case I want my end user to be able to see what the average state pay is on the chart here but if they hover over it I also want them to be able to see what the gross pay is and what the living wage is I can then grab my gross pay 2013 put it in tooltips and I can do the same for my living wage and now even though the chart looks the same as soon as I hover over one of my items now I can see the metric for average state pay gross pay 2013 and living wage if you haven't done so now is the time to pause the video and create a basic visualization I suggest putting at least one categorical field in your axis or legend field area and then putting a calculable field in your values well also go ahead and try out the color saturation and the tooltips and then come on back and we'll move along this first visualization looks pretty good but I don't really need it so I can go ahead and click on it and now that the entire visualization has been highlighted I can hit my delete key on the keyboard and that will remove it I'm gonna create a new one I'm going to create a chart that analyzes gross pay 2013 compared to average state pay compared to living wage now with this particular analysis I have a bit of a problem because I don't really want to analyze the sum of the gross pay the sum of the average state pay or the sum of the living wage but because these are calculable fields as soon as you put it into a calculable field area such as values they will automatically be presumed as a sum function however if you click on the small dropdown triangle next to the name of the field you'll see that you have the option to change the function that's being applied here so I'm gonna go ahead and change each one of these fields to average if you're following along with me make sure that you've added the three fields gross pay 2013 from the employees table average state pay also from the employees table and living wage again from the employees table and we want them all to be in the value field area and click on each triangle and let's change it to average my visual looks pretty good so far but I'm having a slight problem where I can't actually see the entire name here now that I have added the average function to these the name of the field has actually changed to average of grosspay average of average state average of living wage and I am running out of room in my legend there so I'm going to go ahead and start to make some customizations of my visual so far we have just been dealing with the fields area what we're gonna do is we are now gonna play with the format option that we have there clicking on the paint roller will allow you to play with a lot of customization options first of all I'm gonna start by changing this legend up here to be on the right-hand side clicking on legend will show me all the options that I have there including the position currently it's placed at top but I'm going to switch this to the right-hand side if you haven't done so already now's the time to pause the video and open up your format options and go ahead and change the legend to the right-hand side and come on back the other thing that we want to change here is change this title by default all the titles of all the visualizations in power bi get named after the fields that are being used in this visualization which makes sense however sometimes it can be a little bit wordy and so we want to modify this title so that it is a little bit more clear to our end users I'm gonna open up title and then just underneath title now I have the ability to modify the title text and I can go in here and say this is pay vs. average state pay versus living wage and maybe I also want to change this to a slightly different color to catch people's attention now the bars here look pretty good to me but let's say at my particular organization we all understand certain colors to mean certain things in that case what I want to start to be able to do is modify the colors that are being used to represent my data I can go down to the format option called data colors expand that and you'll see that I have the option to customize my colors for each one of these bars if you haven't done so already go ahead and pause the video and change the color of some of your data colors I can also apply data labels by simply clicking on the on button here by default that's off but now it's on and somebody could actually see the numbers associated with each one of these bars of course with any of these options I can also turn it off to turn any feature off finally down at the bottom you'll see the option that says border a lot of the time it gets hard to look at all these visuals if there's no borders on any of them so many people prefer to see borders on them now with the borders the default color for a border is a rather dark black color some people like this some people don't it's good to know that you can change it so I'll change this color to a medium gray if you haven't done so already now is the time to pause the video and try and see if you can create enough customizations to make your visual look kind of like mine my next objective is to actually be able to analyze average gross pay average state pay and living wage by the actual states that were measuring in order to do this I would like to use a map visual I'm going to go ahead and choose my state or zip code fields these are both geographical fields and because their geographical in nature they will usually create a map visual type now clicking on state actually created that map visual type but if I tried the exact same thing with zip code zip code is actually a bunch of numbers and if you look really closely right next to the zip code field name you'll see that there's a little Sigma icon right there which indicates that power bi is thinking of this as a calculable field now we can have much more interesting ways of turning that off or changing it but in the meantime all I'm going to do is select this visual type and I'm going to change it to my standard map visualization the other thing I'm going to need to do is change it from being in one of the calculable field areas to the location area this is actually going to use zip codes as zip codes my map is taking a couple minutes to process and this is pretty normal especially if you have global data the reason for this is that your map visualization actually accesses Bing Maps and so for that reason you need to have access to the Internet for your map visualizations to work so keep that in mind if your map visualizations just plain old aren't working it's likely that you are either on airplane mode or maybe you've just lost your Wi-Fi connection I like this map it's looking pretty good but I want to be able to change the colors of my dots depending on the value of the living wage I want to know where the highest living wages are and where the lowest living wages are by color code to do this I'm going to find my living wage field and I'm going to drag it and drop it into color saturation once I've done that behind the scenes I now have a color scale applied to the highest numbers and lowest numbers now that I have that color scale I can totally change it if I go to formatting options so I'm gonna do that go into formatting options and you'll see now in data colors that I have a diverging color scale there minimum is currently a very light blue color and the maximum is currently a darker blue color I'm gonna change this so it looks a little bit more obvious how about pink for the maximum and yellow for the minimum looks like I have a lot of minimums there if you haven't done so already go ahead and make a map visualization that analyzes by the zip code so the location is the zip code and then we're analyzing color saturation by living-wage you can choose whichever colors you like just long as they make sense to you go ahead and pause the video and do that and come on back most important visual types that you can create is called a card visual it's nice because it very blatantly just puts a big old number on your canvas that actually reports the total of a particular field for instance I'm looking at average gross pay for 2013 right now but I can't actually see the number for that now we know how to add data labels but instead of having it on the chart which would be tiny I want to make a very nice big one that I'm gonna have show up down here we're gonna make one for average gross pay one for average state pay and another one for average living wage of course when I click on each one of these if I just click on the blank canvas and I choose average gross pay to start with gross pay 2013 it shows up in a column chart because this is a calculable field type so in order to switch it I'm gonna go up to my visualizations pane and find my card visual type you'll see that that converts it to just show me the total for this particular field now of course this is a sum so if the point of my entire analysis here is to do mostly averages I'm going to need to again go up and fix gross pay 2013 so it switches to average I can resize my cards by clicking and dragging in the corner and we'll want to create one of each of the measures that we are analyzing on this particular report we've created a average of gross pay we'll want to create an average of state pay in an average of living wage as well go ahead and pause the video and make sure that you have three cards on here one for gross pay one for average state pay and one for living wage each one should be showing the average of that field mine is looking pretty good so far except I have a couple of things that I want to change first of all I'm having a hard time aligning these and I know that it'll look a lot better if I align them but unfortunately so far all I have is my own eyes to detect the difference between the size of these visuals what I'm going to do is go ahead and turn on my visual gridlines if you go up into your ribbon into the View tab you'll find an option that says show gridlines and you also have the option to snap objects to the grid which is pretty handy as well now when you click and drag to resize these objects you'll see that they snap to the grid and you can actually see that visually because you have the grid enabled you of course turning off this visual aid is as easy as clicking on the exact same checkboxes the next thing I'm gonna want to do is make sure that my card visual types are very obvious about what data they represent for instance this very first one which is average of gross pay 2013 so far in my report this field called average of gross pay 2013 is usually represented in this darker aqua blue color so what I want to do is change my card so that the number thirty one point one four thousand there actually appears as blue I'm gonna go to my format options and find the data label options there and say that I want this data label to actually be that blue color now I'm gonna go change the other couple of card visuals as well so that they reflect the field now that's a little too light of a blue color so I'm gonna choose one of the other options here there we go that's a little bit better just for cosmetic reasons maybe I would like to add a nice big box around my cards here so that I can imply that they're sort of all part of the same same content delivery here on the Home tab of the ribbon on the right ish hand side in the insert command group you'll see a command called shapes you have a few different kind of shapes that you can use so far in power bi but as power bi gets more advanced we're expecting to possibly see more clicking on rectangle we'll put in a basic looking square for me and I am gonna click and drag it and move it to the side and then resize it using the corner anchors so that I can drag it around those three results there now my box is now showing on top which is a little bit weird so what I can do is go up to the format tab of the ribbon and find my options that say bring forward or of course send to the back and I'm gonna choose the option that says send to back now if I click off of this visual I'll see that all three of my measures and our showing up on top now that gray fill color I don't really like it so I'm gonna modify that as well clicking on my rectangle here again I'll go look at the options on the right hand side and turn off the fill color then all I need to do is reorient my objects a little bit here so that it looks a little bit nicer just gonna clean it up a little bit I'll also want to create a text box to allow my end-users to see exactly what we're talking about here again on the Home tab of the ribbon on the right hand side in the insert command group now we have a command called text box I'll click on text box and it inserts of course a text box and I'll say this is our wage analysis and it analyzes paid wages versus average local wages versus local living wages I'm gonna resize this a little bit I can also choose this header item here just highlight the item that says wage analysis and turn this into a much bigger font size like maybe 54 and maybe I'll make it bold too I'll change the item below to be just a little bit bigger and then I'm going to move this maybe right underneath my card visuals here and there's my report title if you haven't done so already create a at least one shape in your report and also include a text box just so that you know how to do it one of the last items that I'm going to add to my report is a slicer I really like slicers because they make it very easy for end-users to choose particular data to analyze for instance if I want to be able to analyze this data by state in the US what I can do is create a slicer visual type and then to that slicer I'm going to add the state field now my end users can choose one of these states and the whole set of data gets sliced for that particular item however maybe with my particular report here I don't think I'm gonna find space over here because I have one more visual to put over here on the lower right hand corner I want this slicer to go all the way across the bottom that's one of your options with slicers but it's one of your formatting options so you want to go over to format if you click on general you have an orientation item right here and currently it's set up to be vertical but I'm going to switch this to horizontal and then this will go left to right now you'll want to resize this so that it's actually a good left-to-right slicer and then I'm gonna place it here at the bottom of my report now they're still not quite enough room that's alright though because somebody can go to the right or left by clicking on these arrows here and now by clicking on any one of these states can look in the data there if you haven't done so already please add a slicer to your report that analyzes states so far we have used a lot of the really typical and simple visualization types usually simple visualizations do a pretty good job and we want to keep things simple generally however some of the other visuals do a really good job at telling a particular story in this circumstance what I would really love to do is directly compare gross pay to living wage that's gonna tell me if we as a company are paying people over the living wage in their particular state in order to do this I want a visual that actually measures some kind of a goal to an actual value one of my favorite charts to do this is called a gauge visual I'm going to select on the gauge visualization and then I'm gonna choose my fields to analyze let's start by looking at gross pay and so far we've been looking at gross pay 2013 so I'll go ahead and add that as my value now the gauge visual by default always shows the number as 50% and we'll analyze that in a little bit and fix it but for now what I want is I want to see an item on here that actually marks the target value of course the target value for me is the living wage of that particular area I'm gonna go find my living wage field and I'm gonna drag it and drop it in the target value now I can actually see where the target value is and I can see where we are comparatively looks like we are paying people well above the living wage and that was our objective as somebody slices through this data the gross pay or value of the gauge visual always shows up as 50% so if you want to change this you can actually manually define your boundaries I'll click on the format option for my gauge visual and I'll click on gauge axis and now I can set defined boundaries now it just so turns out that most of the states have somewhere under around 5 million so maybe I'll start by selecting on my gauge visual and saying I want the minimum to be 0 and the maximum to be 5 million now when I slice through my different states I should see those numbers climb in direct relation to the number 5 million and of course at a later time if I ever decide that this that 5 million isn't the right number I can of course always go back into my gauge visual and change the maximum if you haven't done so already go ahead and create a gauge visual you'll want to make sure that you have grosspay 2013 in the value L and also that you have living wage in the target value and then in order to create hard coded boundaries for your gauge visual you'll want to go into the formatting options into your gauge axis and actually set manual boundaries I set 0 as the minimum and 5,000,000 as the maximum go ahead and do that now and come on back for this lab you're gonna want to be able to use a bunch of data from the geography table so you won't need any data from the employees table for this particular exercise so make sure to collapse your employees table open up your geography tables so that we can see all the fields that are in here now ideally what you're gonna try and do is pause the video and see if you can recreate this report just on your own however following this introduction video is a tutorial on how to create every single one of these items in the lower left of the report here we do have a visual type that isn't actually available within your normal visualization so this is going to be the one element that you want to know about before you get started for this particular visual here this is a preview feature which means that it's not already turned on you'll need to go to file options and settings into options find your preview features and turn on your shape map visual once you do that everything else should be possible to figure out on your own however keep in mind directly after this video is a bunch of videos in order that show you how to create every single one of these visuals so however you choose to follow the lab is totally up to you now I'll see you on the other side the first thing that you're going to want to do to get started is create a whole new page here so click on the plus button down here that says new page and then we can go ahead and get started as we said previously I'm gonna open up the data from my geography table that because that's what I'm going to be using for this particular tutorial the first visual that I'm going to create is going to be a nice column chart that is just going to compare the actual revenue to the projected revenue so I'm going to go ahead and select my actual revenue and my projected revenue and I would like to see this information listed by state so I'm going to go ahead and select state as well and now I can see this plotted out here now it just so happens that I have a lot of states that don't seem to be in the United States and that's because the data on that particular report the data in this particular geography table is actually global data so what we're gonna want to do is make sure that we are able to report this visual and every single other visual on this page by the United States only so what I'm going to do is I'm going to go down to region and I'm gonna drag region and drop it in my page level filters this will allow me to apply a filter to the entire page and I'll go ahead and scan down until I see United States click on that and now I should have my report appear filtered by that now this isn't actually the order that I want I would love a alphabetical order so I am going to sort my visual using the dot-dot-dot in the upper right hand side and I'll say they don't want to sort it by state and that's actually Z to a so I'm gonna do it one more time so that I get my A to Z view that looks pretty good so far except I do want my projected revenue to come first so I'm gonna go ahead and click and drag my projected revenue and put it in front so now I can see projected revenue and then actual revenue which in a lot of states looks like it actually surpassed our projected revenue so that's pretty good the other thing that I want to do is swap my colors here later on I'm gonna want to be a bit explicit about the colors that I choose and I've already decided ahead of time that blue is gonna refer to sales over projection in this case I'm not looking at sales over projection I'm simply looking at actual and projection so I'm gonna want to change my colors here and maybe I've decided that I'm gonna go into the format area go down to data colors and I'm gonna swap these out for maybe I want this one to be dark gray and then I want the second one to be yellow move this chart over to the side because it is not the only visual that I'm going to be creating here so this is looking good except the real point of this particular analysis is to take a look at the sales over projection or the sales under projection if that was the case but what we would like to look at is the difference now you see that there's a column called difference and this happens to populate the difference between the two I want to create a separate visual type that will look at the difference so I'm going to click off of this visual click on to my canvas somewhere and click on difference now maybe I also want to see this by state so I'm gonna make sure to choose state right there and there are all my states I'm kind of running out of room horizontally so I'm gonna swap this over into a bar chart instead I just clicked on the bar chart in my visualizations and now I'm just gonna make it big enough I have a lot of states okay so California had the biggest amount of sales over projection and it looks like Arizona actually was under projection by a little bit this is pretty good but my end users aren't going to know what difference by state means so I'm gonna give this a better title I'm gonna go into my format options gonna go into title and I'm gonna call this sales over projection and maybe I'll just turn it yellow for fun and I'm gonna make that text a little bit bigger so it's easier to read okay this is looking pretty good but now I would like to start to indicate success or in the case of Arizona a little bit of failure there depending by a color spectrum so I am gonna want these to start to be color coded so I'm gonna go and have to make this adjustment in the fields area now you might already know what I'm gonna do here I'm gonna grab the difference and I'm gonna drag it and drop it into color saturation this allows a color spectrum there to be created for all these different colors that looks pretty good I do want blue to be the highest success amount but I want red to be the lowest amount and then in between those two I want some kind of a gray color so I'm gonna have to redefine my color spectrum I'm gonna go into my format options here and go into data colors and now instead of the regular option I'm actually going to turn on diverging and watch what happens it actually gives me three different colors that I can choose from maximum center and minimum I know I want my minimum to be red so I'm gonna change that and I want my Center to be some kind of a great color because white actually disappears on the visual into the background so I'm gonna choose a more of a grey color there alright so this is looking pretty good except the only success story it looks like here is California that's not actually true right I have a lot of success stories the only non success stories are things that are actually negative things that are actually less than zero so I'm gonna need to redefine this in my minimum Center and maximum boundaries here so for my minimum I'm gonna say it's okay if they are or not it's okay but I know that they might have negative 1 million right so 1 million dollars under projection there's my 1 million for minimum and then my Center I could put 0 there um let's let's just start with zero there but the problem is that zero is gonna start to make the negative numbers also a little bit gray so I actually want to push that up I would like for P to be over a hundred thousand I would like for each state to be over a hundred thousand over projection that would be ideal and then the maximum is going to be eight million and this to create a better looking color spectrum for me now how did I know that ahead of time I did not I just played around with some of these spectrums until I found something that looked good and reported the story that I wanted to tell we're gonna want to put a couple of cards on here that's actually gonna tell somebody explicitly what is the sales over projection so I'm gonna grab my I'm gonna click on the canvas first and then I'm gonna grab my difference measure and I'm gonna drop it on my canvas and turn it into a card maybe I'll put it up here and then I probably want the percent difference on my canvas as well so I'm gonna do the same with my percent so this is looking okay although these are not being formatted very well and so I'm going to want to go and format them the other problem is that my percent difference is actually being summed which is not appropriate for a percent measure so I'm gonna need to go into my fields area into the percent difference and actually choose a more appropriate calculation here like average and that should look about right but the number is one point four eight which again just like the number to the left formatting goes a long way so we're gonna need to go back and fix this now the easiest way to go back and fix this is to go to your modeling tab choose the field and then correct it so percent difference for instance on the modeling tab here's my % I'll click on that and that should correct every where I see the field and maybe I want to reduce it down to a couple of decimal place holders or no decimal place holders that's what I meant to say all right and then for my my difference measure here let's go ahead and find that one so for my difference measure I do want this to be a dollar sign because this is a sales amount and I'm gonna choose my English United States currency and now that looks pretty good I can even apply a couple of borders here I apply a border there and a border there now these aren't looking all that lined up and turns out that the human eye can detect one ten thousandth of an inch of misalignment so I want to be real careful here I might be so careful as to turn on my grid lines and to choose to snap objects to the grid so now I can move my items around and resize them and put them into a natural place that I feel like looks pretty good the other thing that I'm gonna want to do here is change the words that are on here so each one of these cards currently says different so I'm gonna need to fix that so I'm gonna click on this card here and I'm gonna go to the formatting options and I'm gonna find where it says category label so the category label currently is on I'm gonna go ahead and turn it off and that's gonna take off the measure name there the only reason I'm taking off the measure name is that my end users might not know what difference means so I'm gonna go ahead and apply my own custom title I'm gonna turn it on the title and I'm gonna call it sales over projection I'm also gonna Center that and maybe turn it up again that that yellow color that I like okay I'm gonna do the same thing for my other card visual now is a good time to pause the video and make sure that you catch up with me I'm gonna go ahead and turn off my grid lines and snap objects to the grid for now I have a new challenge now that I have not encountered before and that is that I would like to be able to represent three different measures on the same chart but I don't want to have three bars so you'll see here that I have two bars that's one way to present two different measures on the same chart but as we can see over here on the left hand side this bar chart can only have one measure on it if I want it to look like it has this sliding color scale right there right so now I have the challenge of trying to put three different measures because I one more really important analysis that I need to be able to allow people to read is being able to see the percent difference and the actual revenue and the projected revenue on the exact same visual so in order to do this I'm gonna start to to get a little bit familiar with my more advanced chart types and the one that is really gonna help me start start getting moving with 3d charts if you will meaning three dimensions or three measures on a single chart is my scatter chart if I just use a regular scatter chart they typically use two measures which just means height and width placement or x-axis and y-axis placement and you'll see over here in the fields for your scatter plot that you have an option for an x axis and y axis placement let's just go ahead and get started with those I'm gonna grab my percent difference and place it in the x-axis and of course that is a percent and I want to make sure that it changes to average that it's using average there and then I'm also going to use my actual revenue and I'm gonna put it in the y axis and I want to be able to analyze this by state just like I've done every single other analysis on this particular page so I'm going to grab my state and I'm going to place it in the legend you know I don't love that let's see what happens if I place it in the details hmm interesting difference if I place it in the legend it seems to be a color coordinated in here but if I put it in the details it's not color coordinated or I should say it's not color coordinated yet all the colors on here are all the same much like all the colors were originally all the same on my sales or projection chart over here on the left hand side all this means is that later on I might be able to start doing a sliding color scale of my own but if I place this in legend what it means is that each one of these dots is going to be given its own color based on the actual label so I've decided I don't want that I want to place it in the details which means that each dot is going to be given the same color unless I later specify a difference okay so I have my my two measures in here that are being plotted on the exact same chart now I have the challenge of trying to create a visual expression of a third measure and what I can use to do this is either size or I can use color saturation I'm gonna use size in this example I'm gonna grab projected revenue which is the other field that I wanted to analyze and I'm gonna drag it and drop it in this size well so now I can clearly see the states whose average percent difference was high and whose actual revenue was high right so this would be actual revenue hi this would be percent difference that's really high and I can also visually see the projected revenue that's highest which happens to be the same one that is the highest for the actual revenue as well all right so it looks pretty good the only thing that would be left here is if I needed to call out some outliers here so clearly I do have three bubble outliers that are here at the top depth that seem to have the largest bubble here so the largest original projected revenue but I also have some outliers here down on the lower right hand side so depending on what kind of a story I'm really trying to tell from this visual I might want to color coordinate a couple of outliers this just helps me be able to communicate to my end user immediately here's what you're supposed to be paying attention to in this case maybe I've decided that California should show up as an outlier of course it doesn't say California there so that would be nice too let's change two things let's actually get some labels on there that say the name of the state without somebody needing to hover over it and then I'm also going to change the colors for California Washington and Texas because maybe these are the outliers that I want to call attention to so I'm going to go to my format options here and the first thing that I'm gonna do is turn on my category labels now somebody will be able to see exactly which state this is and the second thing that I'm gonna do is go to my data colors and right now you'll see the default color is set to blue and show all is off but if I turn this on now I have the ability to actually choose each one of these series starting with California and turn it to a different color or in this case I guess since on the left hand side I used blue as a success measure and red as a failure measure if you will maybe I don't want to use red at the top here so what I can do is change my default color to a different color and then all of the rest of them are showing up as in this case that gray color that I chose and I can use blue as my success measure instead that'll keep me from having to change the remaining 48 states there and finally I'm gonna create my cool shape map here now in order to create this shape map it's gonna hover over down here and all its gonna do is is just be very obvious about what item somebody is selected on if somebody has selected on an item on one of the other visuals then we wanted to show up on the shape map and also it will also show us what what states are actually participating in this and which ones are which ones are successes and which ones are more in the failure category of course I don't actually have a shape map until I turn it on for the very first time so you'll want to make sure that you do this as well the way to turn on any preview feature including your shape map is to go to file go down to options and settings options and settings and then find the options item and here if you scan down you'll see something called preview features these are a bunch of really cool preview features that I really encourage you to play with we're gonna start by turning on the shape map visual and click OK once you've done that it should show up in your visualizations down here as the latest visual type you can go ahead and click on that and it will start to create a visual for you I'm gonna make a little bit of room with my shape map I'm gonna need some labels in there so I'm gonna grab my state and drag and drop it in the location well now I do want these colors to be color coordinated by let's say the difference between projected and actual revenues which we call difference so differences in here and I will grab difference and I will place it in color saturation now that's pretty good but of course it's not the color spectrum that I've been using for the rest of my report here so I'm gonna go in and change that in my data colors and I can turn onto diverging options if I want here as well but you don't have to I'll set the maximum to blue the minimum should be red and then the middle color is that gray color that we chose for the middle and I'll set my minimum bounds to negative a million Center can just be zero for this one that's fine and my maximum is 8 million okay so it looks like I could eyeball this pretty well looks like our success stories here are California and Washington and Texas and Colorado here and also Wisconsin and we have a couple of our little states Massachusetts and Maryland over here that are also doing pretty well our problem states happen to be Arizona and Alabama as the most red colored states there so I'm just using this for a nice visual cue so that when somebody steps into this report they have an eagle's eye perspective of what all the data means in geographical terms so I don't actually need this to say difference by state I probably would if I really wanted somebody to be able to read this and analyze this but it's just a little helper visual and so I don't really like the title because it's it's making it look a little messy in here so I'm gonna start cleaning some things up so I'm gonna take the title off of the shape map I just went over to title and turned it off and then I'm gonna be a little bit more careful about the titles of my other visuals as well so this one looks really bad average of percent difference actual revenue and projected revenue by state my end user is not going to want to read that whole thing right so let's see if we can make it a little bit more explicit I'm gonna go into the title and change the title to say actual revenue let's do actionable revenue and percent over projection and this visual projected revenue and actual revenue by state that's actually pretty good I'm just gonna make it a little bit cleaner projected versus actual revenue okay looks pretty good here that was our entire lab if everything is worked out perfectly you should be able to click on your highest outlier here California and see the number 161 showing up as your percent over projection and twenty-two point five nine million sales over projection well done I'll see you the next video if you've completed the lab it should look like this excellent job on completing the very first day of our power bi training remember what we've done so far is we've fetched information from a bunch of different sources we have actually queried that information which has guaranteed us that in the future when new information comes in it will automatically be pulled in here and we won't have to re query that our information regularly and then we figured out how to actually get this information to display all on the exact same visual area using power bi now we could have done a lot of the same things directly within the power bi features in excel those power bi features our power query that we've already explored and power pivot that we're going to explore a little bit more in the next session congratulations on completing day one I'll see you in the next video thanks for watching don't forget we also offer live classes and office applications professional development and private training visit learning com for more details please remember to Like and subscribe and let us know your thoughts in the comments thank you for choosing learn it [Music] you

Channel: Learnit Training

Views: 532,620

Rating: 4.9385509 out of 5

Keywords: free Power BI tutorial, Power BI beginner tutorial, Power BI, microsoft Power BI, learn Power BI, Power BI tips and tricks, Power BI tutorial, Power BI training, tips and tricks Power BI, Power BI how to, free Power BI training, Power BI free tutorial, Power BI tutorial free, free, how to use Power BI, e learning Power BI video

Id: i3CSD7bMMbg

Length: 128min 34sec (7714 seconds)

Published: Fri Mar 27 2020