Hands-On Power BI Tutorial 📊Beginner to Pro 2023 Edition [Full Course]⚡

foreign [Music] well hello everyone my name is Devin Knight I will be your instructor for today's class this is power bi beginner to Pro our 2023 Edition we've actually taught this class before but it was almost two years ago and as you might expect a lot of things change over time and so we wanted to do a nice refresher of this course that maybe some of you have seen before and you can see it on our YouTube channel today it is actually one of our most popular YouTube channel or videos excuse me but we're excited today to give you an updated refreshed view of that because as you know technology changes and it's always good to see how power bi works today as opposed to how it did two years ago so I am joined today by one of my colleagues Manuel Quintana uh he'll be popping up on screen here for a moment he's going to be answering questions in the chat so you'll hear from him throughout the morning and really through the afternoon depending on where you're at locally uh but he's going to be answering a lot of the questions through the chat as we work our way through today's class Manuel go ahead and say hello hi everybody super uh super excited to be here um I've Loved everyone who's attended my events you guys are in for a great ride here with Devin if you're interested in power bi be prepared to be wowed and everything's looking spectacular on the stream about a seven second delay death so we're good to go perfect well thank you Manuel well yeah as I said this is a a pretty lengthy three-hour power bi course but it is for beginners so I want to make sure I set the context of who this class is meant for if you've been working with power bi for years this class is probably not for you this is a beginner level class where we're going to be teaching you some of the basics of working with power bi and I like to teach it as if you've never done this before so that's my goal is to teach power bi for someone that may be totally brand new to it that you'd be able to pick up a few things throughout the day and maybe even those that have been working with power bi for some time might be gonna get a little few nuggets or tips here and there of things that maybe they haven't seen before so what I'd like to do to start us off for today is to just share with you a couple of the resources that you may want for today I've got this shared on my screen let me actually show my screen here there we go so a couple of the resources for today's class that you will need is you will absolutely need the power bi desktop power bi desktop application it's kind of the companion app that goes with power bi that allows you to design solutions that you'll eventually publish to the cloud to be able to share your your results or your work with others now the power bi desktop is a Windows application so if you're watching this and you're on a Mac unfortunately I have a little bit of bad news for you you won't be able to install the power bi desktop on a Windows operating system sorry on a Mac operating system Mac OS you will need to have a Windows operating system to install the power bi desktop now you can do things like run parallels or virtual machines with inside of your Mac operating system to do that but just a heads up that is potentially a roadblock for some of you that may be watching or trying to play around Along on a Mac the other link that I have in the chat and by the way my colleagues will be sharing these links in the YouTube chat here as well uh these links I'm sharing with you you can grab at any point but the second link that I'm going to be sharing with you is to the class files now the class files aren't super critical for today's class uh the class files are actually something we're going to be doing live together anyways but you'll be able to find my completed Solutions out out there you'll also find a few uh images that we're going to be using as backgrounds with inside of our reports I shared a few a few little nuggets there but you can find those class files at the link that I have in the on my screen now but you can also I think my colleagues will be sharing them within the chat as well all right so that's a couple of the things that you'll need for today a couple of the resources let's go back to full screen view here for a moment and talk a little bit more about what to expect for today's class now this is a recorded class and one of the most common questions that we get is is this class recorded absolutely whenever we stream to our YouTube channel these classes will be recorded and available here for you to watch later if you see any kind of glitches with YouTube just always you can all don't don't forget you can always rewind you can even rewind live in YouTube and you can kind of flip back a few seconds and catch up back to where we were and catch yourself up so if I go too fast through a section if you want to see something a second time you're on YouTube you can just kind of rewind it in the YouTube video and go back to where I was before I went maybe maybe perhaps it was my fault maybe I went a little bit too fast or maybe you stepped away for a moment you can always rewind me and go watch things a second time now I really don't have a lot of slides other than the one I had opened up on my screen a moment ago I really want to jump straight into the application and get you familiar with power bi because we have three hours together and tell you what I could spend a full week with you uh talking about power bi if you'd let me uh or if my if my company would let me do it for free that's the other thing but uh what we're going to be doing today though is we're going to jump straight into the tool and we're going to use the power bi desktop to connect into and build a set of reports so I'm going to go back to sharing my screen here and I'm gonna go ahead and get rid of the slides and we're going to talk through first of all of course launching the power bi desktop once we launch the power bi desktop what are some of the areas that we need to be aware of uh what are some of the things that we care about whenever we get into the power bi desktop and where do you need to draw your attention to as you start to work within the tool so I'm going to be starting by making the Assumption you have the power bi desktop installed if you don't take a moment and install the power bi desktop you can find that either through links that are being shared and the chat from my colleagues or you can also of course use a search engine and search for the power bi desktop download but I'm going to be starting launching the power bi desktop today I'm going to take myself off camera for a moment so you can see my start menu here but I'm going to go ahead and start my Windows machine here and look for the power bi desktop which I have already installed and I'm going to launch the power bi desktop like so pretty simple stuff so far just launching the application I'll bring myself back on camera now once the power bi desktop launches it's going to bring up a little startup menu on our screen which we should see Pop open in the middle of my screen here in a moment there it is it's that green background that we're seeing and what we can do is we can kind of look at the interface that's provided to us and we can get a little bit of interesting nuggets from this uh what you'll find is on the left hand side over here this is where you can find some of the more recent files that you've worked on so if you've worked with inside of power bi recently you can find those files again on the left you will find there are some tutorial videos in the middle those tutorial videos are a little bit older at this point but you do have some tutorial videos and then on the right hand side there's some useful links that might be helpful to you as well the one in particular that I tend to highlight is the power bi blog Now power bi is updated rather frequently and the power bi team at Microsoft does a pretty good job of documenting those changes through their blog so I definitely keep a pretty close eye on the blog to see what's changed what's new uh whenever there's updates to the tool all right but once you're done with that you can go ahead and close this out you can hit the little close button in the top right and that'll take us to the main application here so I'm going to go ahead and close that all right so here's the power bi desktop the main view that you'll get used to using over time and the first thing I want to show you is I mentioned just a moment ago that power bi is updated very frequently so how do you know which version of power bi that you're running because maybe you installed power bi a year ago or four years ago and you want to know which version of the tool you're running on your Workstation so if I wanted to go see the version of the tool that I'm running I can do that by going up to the help menu and I'll zoom in on this here for you in a moment but I'm going to go up to the help menu found right here select about and then it'll pop this up and in the middle of your screen letting you know which version of the power bi desktop you're running it actually looks like I'm running a slightly old version myself there is a update that did come out in January I think it was January 10th there was an update of the power bi desktop I I thought mine was up to date even but there there are very frequent updates and you can always kind of go to The Help menu and about to see which version of the tool you're running so if you find that you're running a particularly older version of the tool then you might want to consider going ahead and updating yours to update all you have to do is download the new version of the power bi desktop and you can just install it right on top of the old one you don't have to uninstall the previous one you just simply install it and it will take care of it for you all right so that's how you know which version of the desktop tool you're running go to help and then about all right so the next thing that we're going to do then is I want to highlight the different areas of the tool as a beginner that you really need to focus in on or that you need to be aware of so when you're working with inside the power bi desktop there's several areas that I want to draw your attention to starting with the get data button the get data button is your starting place for any new power bi solution so if you're starting a power bi solution from the ground up you're going to go to the get data button to be able to connect to the data that you want to use for the set of reports that you're trying to build so the get data button is generally going to be the first place you go whenever you're working with inside of power bi that's a generalization there's going to be some exceptions to that but generally when you're starting a new solution get data is where you start the other button that kind of goes along with it or often goes with it is the transform data button over here on the right a little bit further to the right and the transform data button is where you'll go anytime you want to do any kind of data manipulation or data cleansing to your solution the transform data button launches a tool known as power query so when you select transform data this is going to launch a tool called the power query editor okay now these two buttons together tend to go along in this phase that I and many of us at pragmatic Works call the data Discovery phase so I usually like to break power bi down into four different phases phase one which we'll talk about now is maybe the data Discovery phase sometimes also referred to as the data shaping phase you can use those as synonyms if you'd like but what you're doing with inside of that data Discovery phase is you're going to be connecting to data okay so you're going to be connecting to data using the get data button and then you're also going to be manipulating data or performing data cleansing steps and that will be done underneath the transform data button okay so these two buttons are going to become very familiar with early on with your development with inside of power bi these are the first couple steps that you'll use you're going to go connect the data and then you're going to make sure that the data that you brought in is actually accurate that's the data cleansing step found underneath the transform data section so that's phase one and we'll talk about that a lot in our first hour then we'll kind of merge into a few other topics as we go later and later on in the session now let's go ahead and talk about the other three phases the three additional phases because it's helpful to kind of have a full context of how each of these things uh works as you're looking at the Fuller picture of power bi so phase number two tends to happen on these two buttons over here on the right hand side I'm sorry I said right I mean left on the left hand side you have a data View and you have the model view and these two buttons on the left hand side these correlate with the second phase within your power bi project which is oftentimes referred to as the data modeling phase what are you doing with inside the data modeling phase well there's actually quite a bit but generally speaking the data modeling phase is going to be where you're doing things like creating relationships okay so what I mean by that is oftentimes you're going to be connecting into more than one data source and so by creating relationships that allows you to connect those different data sources together so say for example you're trying to get an understanding of what profit like is for your company well generally that's a pretty complex question because you have data in a lot of different places you might have your income in one data source maybe your income data is stored in a SQL server and maybe you have your expenses are stored in Oracle and maybe you have some data in spreadsheets and access databases and data is kind of all over the place and so to be able to answer this question that's kind of overarching the entire company often you need to be able to create relationships between those different data sources you're working with so creating relationships is a major part of power bi we in a three-hour session we'll have just a little bit of time to talk about it today uh but I am going to direct you to some lengthier sessions where we spent a lot of time talking about data modeling uh those are sessions that we've done in the past so you can actually watch this today even we'll share with you uh in a little bit where to find that all right so data modeling encompasses creating relationships it also encompasses building hierarchies and forgive my spelling hierarchies is like my Nemesis I I never know if I'm telling hierarchies right but building hierarchies is the idea of being able to group multiple Fields together for the purposes of later building reports faster so by grouping those fields together it creates this natural hierarchy of data where you can drill into say for example uh I want to drill into this geography a geographic hierarchy and I want to drill into the country and I want to see okay I'm expanding into the United States and then I can drill down into the State of Florida where I'm from and then I can see all the cities within side floor and I can drill down to the city level all the way down to a zip code level so that's kind of the idea of a hierarchy is it allows you to drill deeper and deeper into your data so hierarchies are a popular part of your modeling that you might do and by the way there's lots of other things within the modeling steps that you're doing we're trying to keep it a little basic here for today's session and then the third thing I'll mention here around data modeling is also encompassed with inside of your data model is this idea of Dax calculations I see lots of comments in the chat about Dax I think folks knew that was coming next right Dax is the data analysis I'll go ahead and type this out expression language okay so Dax is the language that you'll use for building calculations so what I mean by that is you can kind of think of like what you used you may have done in the past in Excel some of you maybe have some experience with Excel formulas and have worked quite a bit with Excel Dax is kind of that idea of those Excel formulas you would have done in the past or at least it can be it's not exactly the same as Excel formulas but there are a lot of commonalities there but the idea of writing Dax Expressions is often where you would want to be able to create some kind of additional metric let's say that I wanted to compare this year's Revenue to last year's Revenue well to be able to do that comparison you would need to know a little bit of Dax to be able to return back a formula that would show the previous year the prior Year's Revenue so Dax is I wish something we could get super deep in today we're going to explore it a little bit but I will refer you back to some additional classes these are also three hour classes that we've done on Dax that might be helpful to get you further along with Dax if you're ready for that Dax is usually something that you want to learn and get a little bit more in depth into after you've been working with the basics of power bi for a little bit then you'll want to dig deeper into how Dax works okay all right so that's phase number two phase number three that we're gonna talk about is up in the top left right above the previous two buttons this would be the report View and with inside of the report view this is where you will do what most people think of when they think of power bi within the report view this is phase three this is your data visualization phase and with inside of your data visualization phase of course you're going to be building reports okay you're going to be designing things like uh drill Downs you might do things like tool tips we'll explore a few of those things today uh you may also do things like explore custom visuals so custom visuals are an aspect of power bi that you can work with as well so there's lots of components here I'm being very brief mainly because the screen screen size I have here but the data visualization phase is while many people think of that as one of the core things with power bi you might notice that the step number one and step number two tend to happen before you build your visuals you really need to have a proper data model before you can build visuals the way you expect them to be returned back so uh while a lot of people try and jump rather quickly to the data visualization side of things within power bi there's a lot of benefits to investing time and building a proper data model and again we'll share some links to some other classes that we've done in the past that will give you uh what does a proper data model mean that right that's me saying kind of a very generic term there uh so I'll share uh later some classes where we've actually spent some time talking more about data modeling all right so data visualization is a big part of power bi but again phase one and phase two oftentimes are things that are happening first uh that you'll want to focus in on before you start to build your visuals okay all right so the fourth and Final Phase here is going to be your sharing phase and that's going to happen up here with the publish button over where the publish button can be found phase number four this is your data sharing phase and with inside of the data sharing phase there's a couple things that will happen here this is actually most of this time is actually not spent in the power bi desktop you'll spend a little bit of brief time within the power bi desktop but then after you publish your solution which is Step number one with inside of this phase you'll publish your work but after you publish your work where are you publishing it to well you're publishing it to Microsoft's Cloud what's known as the power bi service and from the power bi service you'll be able to very easily share your work with others so you'll share with others and you'll also be able to do things like schedule data refreshes that's all what's going to happen once you publish your work to the power bi service that's where you're able to then share with others make sure your data stays up to date and then you could also do things like row level security which we won't have time to talk about Unfortunately today but the idea of row level security at least is an important one because the idea of reliable security is where you can not only be able to present results to your users but you can secure the data in the reports that you designed so they only see the rows or the results that they're supposed to see so you can make sure that hey Jimmy only sees the southeast data Sandra only sees the Northwest Data and while I have only one report they're only seeing the data that's relevant to them and that's all those are all steps that happen with inside of the power bi service that last one row level security is actually something that's done both in the desktop tool and in the service so there's a lot that goes into that all right now we're going to move on from this if you want to take a screenshot of this I'm done I'm done you can kind of uh move on from this so if you want to take a screenshot for your notes you'll have this available and we're going to move on to our uh first demonstration and it's really going to be a continuing demonstration that we're going to be doing here coming up next and what we're going to be doing in our demonstration is walking through pulling in some data then using that data to do some data manipulation which is that's step number one remember phase one is all around data cleansing and that's what we're going to walk you through is how to connect to data and then after you connect to that data then how do we start to shape the data to make sure that it is cleansed and we're looking at Accurate results then we'll build a data model we'll talk about building relationships and hierarchies and do a little bit of Dax today and then we'll finally publish our solution so that's kind of our steps here as we get going today all right and I'm going to tell you what I'm going to kill my teams just so it's not popping up on us here that would be frustrating to see teams popping up the whole time here I thought I did that but apparently not there we go now I did all right so here's the use case that we have for today and by the way if you attended our session that we did that was named the same thing two years ago this is going to be the same use case the same demo but you'll see a few different things as power bi has changed a little bit throughout uh the last two years since we did the session last but what I'd like you to do is put your imagination cap on here with me for a moment and imagine that you work for a bank and the bank that you work for they are interested in focusing in on opening up another physical brick and mortar location right another physical location where they can serve their customers and there's a there's a lot of internal data with inside of the organization to tell them where that's probably best to do they you have the frequency of your visits from your customers you have the highest density of area where your customers live but what you like to do is to try and bring in some additional supplemental data that maybe can influence your decision it may not be the overarching reason why you choose one location or another but it's going to be an influencing factor into why you decide where you're going to open up your next banking location and so what we're going to do is we're going to walk through pulling in some data and the data file by the way that I'm going to show you is in the class files however you don't have to have the class files to follow along with this because what I'm going to be doing is actually showing you how you can get this file that we're going to be pulling from a website the reason I shared the font the data source with inside the class files is in case any for some reason someone couldn't get to the website that I'm going to be showing you you'll have a backup plan there you can go look in the class files that I've shared as well all right so let me take myself off camera and let's shift back in here to My Demo and what we're going to be doing is I'm going to launch open a web browser on my screen you can pick whichever web browser you prefer and what I'm going to do once I open up my web browser I'm going to go with chrome today and what I'm going to do is I'm going to go to the website data.gov okay so here's the website we're going to right here if you want to follow along you can okay data.gov all right so open up your web browser and go to data.gov and once we go to data.gov what I'd like to do is then search for a particular data set that's going to help answer the question where banks have been unsuccessful in the past because that's going to be an influencing Factor on where we decide we're going to open up our next physical location okay so if I want to be able to determine that I know there's a data set on this website again this is on data.gov right now and I'm going to search with inside of the search bar found right here by the way there's loads of data sets here there's more than 335 000 data sets to choose from but the one that I'm going to look for is one called FDIC failed Banks so for those of you following along you can type that in FDIC failed Banks and then once you do go ahead and hit the little search over here on the right hand side so FDIC failed Banks let me zoom in on that just in case it's a little small to read and I'll go ahead and hit the enter key or search to be able to navigate to that I know this is for me at least it returns back 30 data sets if you notice it returns back a different number of data sets for you don't get too hung up on that but what I do want you to do is to follow along with me and we're going to go navigate to this data set right here called FDIC failed Bank list and I'm going to select the data set by clicking on the name of it on the top right here okay so for those of you following along you can go ahead and click on FDIC failed Bank list the title at the top and that will take us into the data set that we're going to be using today all right so once I've navigated here this will take me to where I can actually find the file that's going to have all the data that we want for the purposes of today's class and there's an interesting way that we can work with this uh Power bi will allow me to connect either to a file that I have stored somewhere locally on my workstation or one of the really interesting things that power bi allows you to do is you can actually point to a website or a URL where data is stored now as you what if I was teaching this class interactively normally what I would ask you is what would be the benefit of me pointing to a web URL rather than downloading the file locally since this is a little bit less interactive I won't be able to see your responses in a timely manner because there's a little bit of a delay with YouTube I'll go ahead and answer myself the benefit here of pointing to a web location or a URL as opposed to downloading the file locally is that the data is going to change right and I want to make sure that I'm pointing to the most up-to-date view of the data that I could possibly find if I were to download the file locally that would mean I need to go download the file anytime there's new changes or updates to the data I'd have to go download it again as opposed to if I point to the URL I know I'm pointing to the the true source of where the data can be found so to be able to do that in today's session what I'm going to do is I found right here and you're hopefully seeing it as well a comma separated values file that we can use and we can connect into now rather than downloading it so here's what I want you to be careful with I don't want you to download the file where you see the download button instead of downloading it what we're going to do is we're going to right click not left click but we're going to right click on the download button and we're going to select that we want to copy the link address now if you're using a different web browser yours may say copy shortcut or copy link whatever it may be you're going to copy the URL for that download button basically where can the data be found behind the scenes because we're going to be using that with inside of power bi and my colleagues that are many that are taking care of the chat they can share with you this link as well in case for some reason you have problems making it to this website but I'm going to go ahead and copy this link address right here again all we did there just to reiterate I right clicked on this not left clicked right clicked and select copy link address now that I have that URL copied I'm going to go back over to the power bi desktop and I'm going to use that URL inside of the power bi desktop now I'm going to try and make this interactive even though it's a little difficult to do with the delay but let me ask you this question where do I go now now that I have that URL where's the first place I would go to be able to tell power bi that I want to connect in that data source we talked about it earlier in my drawing where would I need to go to be able to connect into my data and let's see there's about a 7 to 10 second delay but let's see if anybody can guess where's the location that I should go there we go and now we're getting some answers in there thank you Yvonne Rachel nice Ellen nice job Paula well done yes the get data button is where we want to go so if I go up to the get data button off the top of my screen remember that's to be found right up top here we talked about that one earlier if I go up to the get data button found right here this will list off many of the different data sources that I have available to me now you'll notice here it says common data sources these are not all of the data sources that are available these are the ones that are basically their Microsoft data sources but there's far more data sources in fact there's more than 150 additional data sources that you can connect into if you choose the more option down here on the bottom I'm not actually going to click on more but I want you to know there's far more data sources available than what you see listed on my screen right now if you click more okay but for the purposes of what we're doing right now what we're going to do is I'm going to again select the get data button right here and then I'm going to select the web option I saw somebody got that right in the chat as well we're going to do web after you click on get data okay so that's the path that we're doing right now Peter you're right on get data then web all right so I'm going to go ahead and do that on my screen as well hopefully you're following along again if I do anything too fast don't forget this is a YouTube video you can always rewind me even though this is live right now you can go and Rewind me and watch it again all right so I'm going to go ahead and select get data then choose web and once I choose rev it's going to pop open in the middle of my screen where I can provide the URL that we're going to be using for today's example so you can either take the example that was provided in the chat that would be the file that I'm going to paste in right now or you can rewind and you can see where did I get this URL I got this URL that I just pasted in right here I got that from the data.gov website so if you're struggling to find where did I get this from there is a link in the uh chat maybe mean well if you don't mind sharing it again now that we're pasting it in here right now but you'll also be able to rewind the video a little bit and see how I got that all right very good so now that I pasted in that URL we're going to go ahead and click OK right here and by clicking OK that will allow us to uh take this to the next step basically we can connect into the data and then we can start to use this and pull the data in from the website okay even though it's a CSV file we're pulling it from the web it looks like Manuel shared the link in there again so if you missed it earlier you can grab that link in the chat now all right so I'm going to hit OK and this is going to take us into the next step where it'll prompt us to authenticate authenticate meaning how are we going to be connecting into the data you can see we have several different options I don't want to get too hung up in this area but what we're going to be doing for today's example is we're going to be connecting using the anonymous example and uh Antonio I'm gonna star your question because I think we'll come back to that question here in just a moment all right so what you'll find here in the uh on my screen right now is the different methods of how we can authenticate to the website that we just provided that would be the fdic.gov's website and our scenario we're going to be connecting using the anonymous connector which is the default selection and then you can go ahead and get ahead of me a little bit and click connect okay so if you want to step ahead of me a few clicks hit connect here and then what I'm going to do is I'm going to answer Antonio's question that I saw in the chat because it's very relevant to where we're at right now so Antonio's question was around the advanced options so what I asked you to do is you can go ahead and hit connect I'm going to take a step back and answer the question that you see below my my face right now uh he asked about the advanced options that are found right here what the advanced options allow you to do is you can actually parameterize the URL so if I were to click on the advanced option here it would allow me to parse out parts of the URL and I could pass in different parameters into the URL so for example let's say the URL that I'm using as a data source has in it a parameter for the year and I want to be able to dynamically pass in which year of results I want to return back this is one of the ways you can do that so the advanced options the short answer to that question is the advanced options allow you to parameterize the URL and just return back certain results so very good question thank you for that all right let's go ahead and click OK and I'm going to catch back up to you remember what I told you to do just a moment ago go ahead and click connect right here and by clicking connect that will allow us to take the next steps in this wizard that we're looking at right now so I'm going to go ahead and click connect all right so once I do that hopefully I'm catching up to you now what you're going to find on this next screen is a preview of the results of the data that we're about to get into so this is just giving us a sampling of the data that we are going to be connecting into so what you'll find is there's a couple buttons on the bottom here that I really want to draw your attention into so let me go ahead and focus in on this area down here in the bottom uh the the cancel button I think is pretty self-explanatory I'm not going to get too deep into the cancel button but let's talk about the other ones here okay what we're going to see here is we have load and transform data and I want to describe the difference between these two to you first of all the load button what it allows you to do is it will immediately load the results of your in this case CSV file but it will it'll immediately load the results of your data into the power bi data model okay so the data model with inside of power bi remember we talked about phase number two being the data modeling phase this would if you hit load that would jump you ahead to phase number two okay that's what that's what load would do here for me the other option here called transform data what transform data does instead is it is actually going to launch the power query editor which allows you to perform create a cleansing steps okay so the option here the idea with the transform data button is it will allow me to then not only Connect into the data but then also make sure that I'm actually getting accurate results returned back I want to make sure that I have results that I'm displaying that not only are pretty and beautiful with inside of a report but they're actually accurate and that's a big part of what we're what we're trying to do here okay and good question there in the chat looks like Manuel has kind of promoted that up we are talking about that now so load would be I so let's let's word it this way I would choose load under pretty rare circumstances generally I would choose load is when I'm connecting into a data source that really doesn't have that many data cleansing needs for example if I was connecting into a data warehouse which some of you have a data warehouse probably more of you don't have a data warehouse but a data warehouse is a database designed by your it Department generally that's already kind of perfected that's already gone through a data cleansing steps so if you're connecting into a data source that already has those data cleansing steps done then I would choose load if I'm connecting into a data source that still requires me to do some data cleansing then I would choose transform data and that's more often what you would do here transform data is probably I'd say more than 90 of the time what you're going to choose when you're working with inside of this section okay so I'm going to go ahead and choose transform data you can do that as well if you're following along now there are quite a few of us that are actually clicking this at the same time so don't be surprised if uh it may need a little bit of a a little bit of time to be able to pull this in it looks like mine pulled in pretty quickly here though but what I'm going to be doing here is I'm going to walk you through a few small data cleansing steps that you can do there's lots of things that we can do with inside of this editor in fact what I mentioned just a few moments ago was when you click on the transform data button remember I told you it's going to launch a new window called the power query editor and you'll actually see that right here the power query editor window has been launched inside of the power bi desktop so the power bi desktop is the application that we're using but it launches this new window called the power query editor that allows it to perform the data cleansing steps that we need okay good question uh that just popped up in the chat as well again from Antonia let me go ahead and pop that on uh his question was if you load can you transform later or do you have to start over great question if you had chose the load option that I showed earlier you can always come back and click transform data here later on if you needed to as well okay so if you make a mistake and you need to come back to this window that I'm showing transform data right here will always bring you back great question all right so now that we have uh the transform data editor open also known as power query editor uh we're going to actually walk you through a few things with inside of here to perform a few data cleansing steps now one of the most rudimentary data cleansing steps that you can do is to remove columns just bring back the columns you actually need right so if I wanted to remove certain columns that are not necessary then I can do that in a number of different ways and and by the way it's worth mentioning that with inside of the power query editor that I'm showing right now there's usually about three or four different ways to accomplish the same thing so if you have been working with power bi for some time and you're like oh I do that a different way that's okay uh that's okay that you do it differently as long as you know how to do it that's fine there's just multiple ways to be able to solve problems here inside of the power query and power bi for that matter so the way that I'm going to guide you through how to remove columns that we don't need is I'm going to show you underneath let me zoom in on this for you underneath the Home tab at the top which you should already be looking at but underneath the Home tab there is a button here called choose columns and we're going to select underneath choose columns we're going to select choose columns again found right here okay so underneath home go navigate over to choose columns right here and then select choose columns a second time whenever you press the down button okay all right so once I do that on my screen as well we will see a new dialog box pop up in the middle of my screen asking me which columns do I want to include and which columns do I want to exclude Now the default is it's just going to include every single column you'll see there's a check mark next to every column listed here but the reason why I particularly like this editor for removing removing columns like I mentioned there's more than one way you can do this but the reason why I like this editor is if you're dealing with a particularly wide data set and when I say wide I mean you have a lot of columns like some of you might be dealing with data sets that have hundreds of columns the thing I really like about this particular feature for removing columns is there's a search capability up top and if I wanted to look for a particular column I could type in the column name and it will return back the column that I want to either add or remove that's one of the big benefits of using this particular feature is it gives you the ability to search for columns and it makes it a lot easier to find columns as needed as well now for today's example we are just going to remove three columns and the three columns that we want to remove are going to be the cert column and I'll zoom in on this in a moment the acquiring institution column and the fund column so the three you're going to remove are these two and this one here as well so take a moment uncheck those three columns that I'm showing on my screen right now and after you uncheck those three columns which by the way you can add them back in later so just because you remove them now doesn't mean you can't come back and get them again later but we're going to uncheck those three columns and then towards the bottom of this dialog box you'll click ok all right so uncheck these three and then click OK on the bottom all right so now that you've done that you'll go ahead and select OK and you'll notice the number of columns that are appearing on my screen have now been reduced so I now only see four columns in my list that I'm going to be have available to work with now as we make these changes as we apply transforms to our data set on the far right hand side of my screen and when I mean far right I mean way far over on the right right here on the far right hand side of your screen you'll likely see a DOT a pane on the far right called query settings and if for some reason you don't see query settings let me show you what happens if you for some reason close query settings like mine's now disappeared you can find it by going under the view menu and query settings right here this is only if you don't see it the majority of you should see query settings but for the the handful of you that for some reason don't see query settings you'll go to view and query settings to find it all right so what you'll find over here on the right hand side where query settings is this is where you can do a couple things this is where one you can rename your query right so right now my query is called Bank list with no space not a very good name this is also however below where you can see all of the data transforms that you've applied to your data set so we want to make a couple changes here first let's go ahead and rename our query so right here where the query is called Bank list I'm going to rename my query to something more appropriate I'm going to call this failed thanks something like that with a space spaces are okay here uh by the way one thing worth noting here spaces and your column names are okay in power bi as well many of you that come from maybe more of a database design background are probably used to not putting spaces in your column names not the case in power bi power bi it's not only okay to put spaces in your column names it's actually encouraged because you're trying to prepare your columns here in power bi for what your final end users are going to want to see and they're probably going to want to see spaces in the column names all right so I can come over here on the far right and I can rename my query now the other thing and this is a question from Irena so let me promote irina's question here Irina asked if I remove the wrong columns can I add them back and the answer is yes here's how you can do that if I made a mistake and I removed the wrong columns I can always come back over here to the query settings pane this is not only the area where you can rename your queries this is also Below in the applied steps section right here this is where you can make changes to any of the transforms that you performed so to the question if I made a mistake and I need to go back and make a change how can I do that well you can either edit your transform or you can delete your transform right here so what these will do this is your edit button whenever you see the little gear icon you can edit that step you did and you can change which uh which items or which uh which columns you removed or didn't remove and then on the left hand side if you just realize that you just flat out want to delete it you can come over here and you can delete the step as well so so the question from Irena great question you would click on the edit button that I'm pointing at right now the little gear icon if you realize you made a mistake and you wanted to go add or remove different columns great question all right so let me hide let me unzoom for a moment and hide that question there we go all right so the applied steps section is going to become your friend you'll find a lot of times you'll go revisit the applied steps section whenever you make a mistake or maybe you just need to go back and make a change to what you did previously this applied step section over on the right hand side you will visit quite frequently all right so now that we've removed the columns that we don't need now let's think a little bit more creatively with how we'll work with this data and one of the things I know I want to do is I want to be able to place this data inside of a map I want to be able to map my data so that way I can see where all of these failed banks are occurring geographically so one of the challenges with mapping data when you only have city and state and you don't have like latitude longitude is oftentimes you might find that the same city can appear in more than one state so I happen to live in the city called Jacksonville Florida but there's more than one Jacksonville in the United States there's there's a dozen or more jacksonvilles within the United States and so what I want to do is I want to make sure that whenever we refer to Jacksonville or Columbus or what name name the city right I want to make sure that it's very clear on which Jacksonville or which Columbus Ohio or Columbus Georgia we're talking about so what I'm going to walk you through is how you can combine two columns together now again like I mentioned earlier there's oftentimes there's more than one way to accomplish this problem to solve this problem I could select both of the columns so I can click on the column names up top here and multi-select them by holding Ctrl and then I can right click and merge them that's one way you can do it but I want to show you a slightly different way to solve this problem uh and the reason why why I want to show you this different method is because I think it'll get the wheels turning on some other things you might be able to do when you're working with inside of the power query editor okay now the feature that I want to show you is called column from example and what column from example does is it allows you as a developer of this query to provide an example of what you and you want to what you want the end result to look like stumble out of my words there a little bit but I got it out so if I want to combine the city and state together I would feed into this column from example feature an example of what I want the results to look like if I wanted it to be City comma State I would do something like that and then power bi will figure out the pattern of what I'm trying to do with the data and then it will replicate it for every Row in my data set so it's a really neat feature um it's a really powerful feature that leverages some AI capability behind the scenes to be able to find patterns in what you're doing and then replicate it for every Row in your data set all right so let's go ahead and do this uh the column from example feature can be found by going underneath the add column tab found right here and then after you expand the add column tab you'll select column from examples and then from all columns right here okay I'll keep this on my screen for a few seconds but again you feel free to pause me at any point even though this is a live event you can pause me and Rewind me YouTube allows you to do that all right so we're going to go up to add column column from example and then from all columns okay now by selecting that what's going to happen on my screen is we're going to see a new blank column up here over on the right hand side so there's this new empty column right here nothing is inside of it yet it's simply a blank column that we're going to be able to use and what we're going to do with it is in the blank cells with inside of that column we're going to provide an example of what we want the end result to look like so if I'm looking at this at a row level here so let's say I'm looking at this particular row right here what I would do is I would type into this section the example of the city and state combined together so to give you a little example what this looks like let me zoom in on this for you I'm going to come into the cell and underneath column one this new column we've created and I'll double click to get the cursor to appear with inside of that cell and you can use the intellisense here to help you out a little bit but I tend to find it's a little better just to type it so I'm going to go ahead and type in almina comma with a space Kansas or the abbreviation for Kansas after I provide in that sample result and I hit enter it's going to determine what I'm trying to do and it's going to replicate it for every Row in my data set so it it figured out power bi was smart enough to figure out that oh you're trying to combine the city and state together and so what it did was it merged those two together with inside of this new column based on the example that we provided again all I did was to reproduce that just to show you again I typed in almina I'll spell it right spelling does matter here by the way so if you misspell it it will work but I typed in almina comma Kansas hit enter and then every row below had this new result that came through now while we're here we can also rename the column you can rename columns later as well but if you ever want to rename a column all you have to do is double click on the column header so right here where it says merged if I double click on that I can type a new name on top of the old name and replace it so if I want I can rename this instead of calling it merged I can call it City space something like that so I can give the new column a name just by double clicking on the column header and typing a new name on top of the old name okay all right so let's go ahead and click OK once we're done with this now you can go ahead and click OK I want to show you one thing in the top left here in the top left you'll notice that what power bi has done for me it is it figured out the pattern of what I was trying to do and then it actually wrote what's known as M query M query m stands for mashup by the way but it wrote some M query behind the scenes for us could be able to process the data transformation that we're trying to do so what you're seeing Right Here is known as M query M query is used with inside of the power query editor I think I saw this question pop up earlier let me see if I can actually promote it yeah here we go I'm going to promote this question that it came up earlier I was waiting for the right time to answer it now's a good time to answer it uh there was a question is Dax the same as m and no they're actually different from each other what m is used for is more for data cleansing data Transformations data shaping whereas Dax the opposite is used for more analytical calculations that you're trying to build so think of M as your data cleansing language which by the way you're probably not writing in by itself very much you're probably using this user interface to do a lot of the M code for you but Dax is one that you probably will get a lot more Hands-On with and Dax is going to be used for more of those analytical calculations that you want to build so think of Dax as like I want to be able to know and compare this year's Revenue to last year's Revenue to be able to determine what last year's Revenue was you're going to need to know how to do that in Dax using a function like same period last year kind of thing okay all right so good question there earlier I held on to it I wasn't ignoring your question just wanted the right time to answer it so let me go ahead and get uh that cleared uh oh Manuel can you figure a high current comment there we go got it all right so that's now that we've kind of got an understanding of that so it's writing M here for us and there is a question man Antonio is killing it with these great questions here not not to pick on his questions a lot but he's got some really good ones Antonio asked the question can you modify or import M code you absolutely can do that if I'm not going to go too deep into it today but I will show you where to go I want to make sure we kind of keep on Pace where we want to be uh so Antonio I'm going to keep your question up on my screen for a moment I will answer that uh but before I do let me go ahead and make sure I walk everybody through the next step that I haven't done yet I just need to Simply click the OK button here and that will finalize the column that I have created okay so I'm going to go ahead and click OK and that will create that new city-state column all right so Antonio's question that I still have up on my screen is can you modify the M code that's written and you absolutely can modify it if you want to modify the M code for any reason you can do that underneath the Home tab so you would go underneath home and actually there's two different places you can do it uh you can go underneath home and then you can select Advanced editor right here and I'm not going to open that today because that'll take us down a rabbit hole I don't want to go down for a beginner course but you can modify the the M code by going to the advanced editor you'll also notice I have this little formula bar right here I can modify the M code in the formula bar if for some reason you're not seeing the formula bar you can find the formula bar by going underneath the view menu right here and that will allow you to expose the formula bar if you're not seeing it for some reason okay all right good question all right so now that we've got that new column created for city state I'm ready to start presenting these results in a report I want to be able to visualize this data I want to be able to answer a question here and many of you that have attended these sessions with us may already be familiar with this data set so you may know one of the common things let's see if we can make this a little interactive and if you already know the answer don't don't answer here let me let's let's play a game here I want to know which state and this is all U.S data by the way U.S state which U.S state is going to have the most failed Banks answer in the chat let me know which one you think which U.S state is going to have the most failed Banks just for fun let's have a little fun with it here and see if we can answer uh which one we think is going to have the most failed Banks okay all right so we got a couple answers starting to come through all right I'm purposely asking it right now before you can uh see the answer on your screen so we got some good guesses Texas Illinois California Louisiana we've gotten lots of good uh good guesses come through all right so how can we answer the question if I want to know which state has the most failed Banks how can I figure that out well I can figure that out by going up to first we have to get out of the power query editor so don't forget right now at the moment we are inside this tool called the power query editor but what we want to do is we want to now take the data that we have been working on and load it into our data model so we can actually start to build some visuals on top of it okay we've got lots of good guesses continuing to come through on which state people have or I think have the most failed Banks so here's what I want to do if I want to see which date has them both failed Banks I need to load this data into my data model so I can actually build a visual on it so to do that I'm going to go up to the top left underneath the Home tab if you're not looking at home already you'll want to go to home and we're going to select close and apply and then select close and apply again all right now let me explain what close and apply means here to make sure this is really clear for you when you select close and apply what close means is it's going to close the power query editor all right don't forget right now we have this power query editor open right here whenever we click on close it's going to close the power query editor now what apply means apply means it's going to load the results of your query into the data model now before we can actually use this data we need to get it loaded into the data model and so what the apply portion of the close and apply does is it loads the data into the data model so we can actually start to use it okay so that's just to be clear close and apply close the power query editor and it loads the data into our data model all right so I'm going to go ahead and choose this on my screen if you're following along I have on my screen right now what I'm about to do I'm going to go to home close and apply and close and apply again all right so I'm going to do it on my screen home close and apply close and apply all right our goal here is I want to figure out which state is going to have the most failed bank so let's see if we can figure out which one has the most failed banks for us here now what you'll notice in the middle of my screen right now is it's loading the data into our data model and there's not a whole lot of data here but it should load in a pretty small data set right 563 rows is not a very large data set but it's loaded in all of the data from that CSV file into our data set now if it fails or it has a problem loading for you just give it another try the reason why you could have an issue loading it in is we have you know several hundred people that are watching this at the same time and if we all try and do this at the same time as you can imagine it may be a little slower pulling that data in but once you load the data in what you'll find on the right hand side of your screen is under your Fields list found right here you now have this table that appears for you so you now have a failed Banks table that's available and I'm seeing lots of people in the chat have now figured out the right answer they're good um of which state has the most failed Banks and that's what I want to do with you is I actually want to guide you through how to create our first visual from the table that I'm showing on my screen right now to be able to actually see the answer to that question of which state has the most failed Banks okay all right so let's go ahead and show you how to build a visual so if I want to build a visual answering the question which state has the most failed Banks we can do that by going into the visualizations pane found right here so here's your visualizations pane and the visual that I want you to build with me is going to be a bar chart this is a stack bar chart right here and if you're following along you can go ahead and select the stack bar chart that I'm showing and highlighting right now on my screen and then we're going to start to add in some Fields into that stack bar chart together here in a few moments but the first step that we're going to do together is you're going to go ahead and select that stack bar chart that I'm pointing at right now all right so I'm going to go ahead and choose that and select it on my screen and you'll notice over on the left hand side of my screen that this new visual appears with inside of my design canvas and you can resize it you can reshape it you can move it around by grabbing it you can really kind of place it wherever you want but what I want to do is I want to start to add in some new Fields into it so if I want to add new fields and by the way it's telling me to add new Fields right here right it says select or drag fields to populate the visual what it's telling you to do is you need to go over to the fields list and start to bring in various Fields into this visual so you can actually see some results now if I want to see some results in this visual the first thing that you need to be aware of is you have to select the visual so one thing to be cognizant of is you can select either the background of your report and you'll notice it deselects the visual or if you click on the visual you now have it selected the reason why I'm kind of emphasizing this and why it's important is because if you don't have the visual selected like if I click in the background here power bi thinks I'm going to try and create another new visual with the visual selected power bi thinks I'm trying to edit the visual which is what we're trying to do right now so your big clue to know whether or not you have the visual selected is you'll see these little anchor points appear around the edges that's how you know you have the visual selected and you can start to actually add things or change the visual itself if you don't see those little anchor points around it you don't have it selected all right so with the visual selected we're going to go work our way over to the fields list on the right hand side so here's our Fields list right here and with inside of the fields list we're going to expand the failed Banks table and it sounds like lots of you have already done this you're a little ahead of me no problem we're going to go ahead and go up to and expand the failed Banks table so you'll see this little Chevron here a little arrow that you can click on that will expand or collapse the table with inside of the failed Banks table we're going to be creating and bringing in a few of the fields into the fields list right here okay so we're going to be dragging and dropping Fields over in this area all right so what we're going to do is we're going to take first the state column because we want to know which state has the most failed Banks we're going to grab the state column and we're going to drag and drop it into the y-axis now I should mention this if your uh if your power bi desktop doesn't say y-axis if it says access and values that means you're running a pretty significantly old version of the power bi desktop and I would recommend that you go update because you're going to find there's a lot of things different if you're running that old of a version but so just a heads up if yours says something slightly different right here that means you're running an older version of the tool hopefully everyone says y-axis and x-axis and you're going to drag the state column into the y-axis and we're then going to bring the bank name column into the x-axis and the reason we're bringing the bank name into the x-axis is for the time being I just want to do a simple count to count all of the failed Banks count all of the number of banks that I find here all right so I'm showing on my screen right now what I'm about to do so you're a little ahead of me but go ahead and still Dragon State into y-axis and Bank name into x-axis all right so let me go ahead and do that on my own as well I'm going to grab the state into the y-axis and I'm going to drag the bank name into the x-axis and if you missed a step there you can go ahead and Rewind the video watch it again make sure you're up to speed with what we do that what we just did now you'll notice when I dragged in the bank name into the x-axis it automatically did a count of Bank name that's because whatever you put in the x-axis is going to need to be aggregated in some way uh what I mean by aggregated is uh an aggregate is like a count a sum A Min a Max an average something like that those are that's what an aggregate is and you can change the aggregate that's used by clicking the little down arrow here and I can change between account and account distinct right now because I'm looking at text I can only really do counts on it but if it was a number value I could do an average I could do a Min a Max a sum those are things that you can do on integers whereas on a text column I can either count or do account distinct all right so now that we have that in here I can zoom out and we can see that those of you that answered in the chat Georgia Georgia was the state that had the most failed Banks we can see that on the top right here Georgia the the leader I don't know if they should be proud about this one but did Georgia have been followed by my home state so I'm not going to make fun of Georgia because I'm right behind them with Florida uh Illinois California Minnesota Washington kind of ran up the top five or six there so it kind of shows you here what's interesting about this and I did a lot of talking to get us here right we've been we've been going for an hour but this is this shows you how you can go from having no data at all remember we started this session with nothing and then we kind of built out where we can actually start to now start to see visuals come together that we can use and we're very quickly able to answer questions like which state had the most failed Banks I didn't know the answer to that until we built out the solution and now we can very clearly and easily see that Georgia was the state with the most failed Banks very cool okay all right so uh I think we're looking good here now the next thing that we're going to do is we're going to start to merge into phase number two all right so phase number two is going to be our data modeling phase all right so we're starting our number two it's probably a good time to start phase number two phase number two is all around data modeling and how to ensure that we create relationships between multiple sources it's also where we can do things like create hierarchies and where we can write Dax calculations now um Manuel if you could hopefully you have the link handy and if not I think I have it handy here as well uh we're gonna share a link to a three hour class very similar to like what we're doing today around data modeling and it was done by one of my colleagues and Manuel's colleague Mitchell Pearson it's a great session it's three hours just like we're doing today on nothing but data modeling so data modeling is a very very important part of power bi in fact it's often an element of power bi that people Overlook even though it's probably one of the most important things you can do in data modeling so I want to I want even though I'm only gonna have so much time to cover today I want to make sure you have that three hour resource and Manuel has also shared a secondary Link in there to uh I would say a a another good more compact version of that if you don't have three hours to learn about data modeling follow the second link that he put in the chat it's about a 24 minute video those are both really good but it's all around designing a data model to make sure that you have a proper design and solution for building your reports later on okay so those are videos that were shared in the chat one is uh by Mitchell that's the three hour one ones by Manuel it's about a 25 minute one they're both very good uh but it's a very important part we're only unfortunately going to have just a little bit of time to dig into it here today now we're at a good point for question I've seen uh manuela's uh keying me in on a question here from Matt Matt asked the question how do you change the table heading from count of Bank Name by state to something more user friendly that's a great question Matt and you asked it at a great time so if I wanted to be able to rename either the let's say for example what we see down here on the the uh the X sorry the yeah the x-axis on the bottom or maybe we even want to change the title of the visual that's certainly something you can do so to Max question if we wanted to be able to adjust things like the names with inside of our visuals we can certainly do that and there's a couple different ways you can do that and I'll take this question then we'll kind of move on to our data modeling section if I wanted to rename that to say total Banks instead of count the banks I can do that by going over to my Fields pane here's your field pane right here right and then from within side of your Fields pane you can go down to where you see count the bank name underneath the x-axis and I saw some people saw they noticed that their X and Y axis was flipped just make sure you put them in the right slot right put the state and the y-axis and the count the bank name and the x-axis but if I wanted to rename this instead of showing count the bank name you could either go and rename it inside of your model that would be inside the table or a more temporary solution is I can go ahead and rename it right here so if I were to double click where it says count the bank name I can rename it just for this one Visual and I can rename it something like total Banks right here and if I double click on that it gives me the ability to rename it again that's only renaming it for this one visual if I wanted to have a different name for all visuals then I would go and I would rename it with inside of the field list over here on the right hand side I double click on it here and I can give it a totally new name from the table but if I zoom out notice now whenever whenever I go to look at my visual it now says total Banks down here instead of count the bank so much better a better way more user friendly as Matt's question indicated they're a more user-friendly way to be able to uh display and and show new columns but renaming it in one place only does it for one visual rename it in the table does it for the entire data model that's kind of the difference there all right so let's talk about data modeling here now let's shift gears a little bit and start to talk about building out a proper data model now right now as you can see we only have one table with inside of our data model you can look under your Fields list and you can see we have one table called failed Banks but traditionally you're going to have more than one table in a data model and that's really where I would I would reference you to go look at those links that Manuel shared to the three hour uh data modeling session or the 25 minute data modeling video to learn more about how to kind of properly model your data but one of the things I do want to show you at least for today for our session where we're covering a lot more topics is how you can add in more than one table and then also we're going to talk a little bit about the significance of a particular type of table with inside of your data model called a date table all right so for to have this discussion for a moment I'm going to go ahead and bring my whiteboard up up on screen like so and I'm just going to draw on the screen here for a little bit and we're going to be talking about a date table all right so let's first of all talk about what is a date table all right so a date table is often something that you will have with inside of your BI Solutions and I'm specifically saying bi not just power bi other Solutions outside of power bi that are considered business intelligence Solutions will oftentimes also have a date table and the reason why a date table is so helpful and and can help you in your design is a couple reasons so let's let's talk about first of all the why why do I need to have a date table okay the reason why it's helpful for you to have a date table there's I'm going to give you three reasons but there's probably even more reasons than this but the reason why a date table can be really helpful with inside of your Solutions is one maybe you're trying to analyze very specific types of dates so let's say for example you have special baits you want to analyze or let's say you want to compare so what do I mean by that let's say you want to compare holidays or you want to compare weekends or weekdays or any other type of special dates that you might care about there's lots of other examples you could probably come up with so let's say for example you're a retailer in the US uh the U in the United States and in many other countries I think have adopted it over time but there's a lot of special sales holidays like Black Friday or uh Cyber Monday right those are huge sales holidays with inside of the United States and many other countries as well and if I wanted to be able to compare this year's sales on Black Friday versus last year's sales on Black Friday or Cyber Monday I probably need to have some indicator with inside of my date table letting me know when that date occurred this year compared to when that date occurred last year because the the actual date itself is going to change it's always a Friday or it's always a Monday but the date itself is going to rotate or change a little bit based on the year and so having a date table allows me to be able to monitor special dates or when it was a weekend or when it was a weekday that's one of the reasons why you might consider having a date table another reason why you might consider having a date table is if you're dealing with a fiscal calendar okay so some of you are probably familiar with what a physical calendar is others may not be uh but a fiscal calendar is something that oftentimes your accounting team will use to be able to uh do budgeting across a year but the years are oftentimes different than a traditional calendar for example Microsoft small little company known as Microsoft right kidding of course they're huge uh but Microsoft uses a fiscal calendar that begins in July okay so what that means is if you ask Microsoft for as far as their fiscal calendar goes when does the year begin they're going to tell you the beginning of July is when they started the year 2023 as far as their fiscal calendar goes okay and so their fiscal calendar ends in on June 30th and so the the fiscal year of 2024 will begin on July 1st for them coming up so there's a lot of companies that use fiscal calendars Microsoft is not the only one many of you probably do as well and the reason why it's helpful to have a date table is a date table will help you map a traditional calendar date to a fiscal date and so with inside of your date table you'll have a list of your traditional calendar dates and then right next to it you'll have a fiscal period and a fiscal year and a fiscal quarter so that's why it's really helpful to have that's another reason why it's really helpful to have a date table is when you're trying to analyze things over a fiscal calendar all right the third reason and a very popular reason why you would consider having a date table is if you are trying to design Dax time intelligence okay so let's spell that's always fun when you misspell intelligence uh but that's time intelligence time intelligence is with inside of the uh data analysis expression language that's what Dax says again as a reminder but using the time intelligence functions with inside of Dax allow you to be able to compare period over period or a 12 month rolling average or if you wanted to really do any kind of time analysis you generally need to have a date table first before you do it there's some time intelligence functions that you can do without a date table but there's some others that are really beneficial for you to have that date table that you created that you designed that has your fiscal calendar in it to be able to do that time intelligence analysis so when I say time intelligence I'm talking about things like analyzing year to date or quarter to days or maybe I want to look at uh year over year or maybe I want to see a 12 month rolling average something like that those are examples of time intelligence functions you might build into your data model and it's far easier to do those if you have a date table already okay all right so now that we answered the why that's why you'd need to have a date table let's answer the question of how do you create a date table how do I get it's a how do I get one okay so there's a couple different ways you can get a date table one you can import a dates table from your data source so if you're connecting into a data warehouse for example uh by connecting it to a data warehouse your data warehouse likely already has a date table and you would simply connect to that date table and use it with inside of your model so step number one there our option number one would be I have a date table and my data point base and I'm just going to connect to it because it's already got all the things I need so that would be the easiest example the easiest option but it's not maybe necessarily the most common option not everyone is going to have a date table they can pull from from within side of their data source already okay so that's one thing to consider if your data source has a date table great use it if not then you need to consider other options all right so let's talk about what what the other options are and Manuel if you can get handy there should be a URL to a date table script uh if you can share that in the chat that will go along with the next little item here so the next thing that you could do is you could create a date table using power query and more specifically using the M formula language all right so you can use M query to be able to design your own date table and Manuel is going to share a link in the chat to a Blog that I wrote a number of years ago that will actually create a date table for you automatically all you have to do is copy and paste my code and there it is you put it in there now but you can copy and paste my code inside of that blog and you can bring it into Power query and it will create a date table for you I particularly like that method I think it's a great method for creating a day table however we won't be using that method today and the reason why I'm not going to be using that method today is because I want to show you the third method which is Dax uh the re I really want to get you a little bit of a peek into what Dax is because it's so much more commonly needed uh than the M query option the the writing inquiries is something that's done far more rarely than writing Dax and so I think this is a great opportunity for me to show you how to write Dax by using uh by creating a date table so I'm going to guide you through that but Manuel did share a link in the chat if you're interested in learning about how to create a date table using M query follow the link you put in the chat and that'll kind of take you there all right now the third option which is what we're going to be doing in our example today is we're going to design a dates table using Dax again Dax being the data analysis expression language okay and again the reason why I'm going to show you the Dax method I actually kind of like the power query method a little bit more but the reason why I want to do the Dax method today I think it's a great way to help you learn Dax a little bit in a beginner session like today um and and we're going to be exposed into some of the basics of writing Dax by using that method today so both methods are fine by the way really there's not a right or wrong answer to which of the bottom three you choose you can choose to import it that's fine you can create it with it a power query that's fine uh you can do it with Dax that I'm which is what I'm going to be showing you today that's equally fine there's not a right or wrong answer to what you choose here okay now there is a question that in the chat and unfortunately because I'm in my drawing mode here I won't be able to go go promote it but uh how is this different than the auto date table so power bi does create a date table behind the scenes for you to do some things the read and thank you uh Manuel for promoting that uh the reason why I prefer to create my own day table as opposed to using the auto one is for some of these top reasons we mentioned here power bi does kind of create a little bit of a secret date table behind the scenes for you uh and uh it's it's great that you have that option available to you but my preference is to create my own day table so I have a lot more control over it so I can have my own special columns that I want to be part of it nothing wrong with using that date table that's created for you but it's kind of hidden from you you don't actually see the date table that power bi creates for you whereas this one is much more visible and you can design it you can do whatever you want with it okay all right so if you want to screenshot these notes now's a great time to take it because we are going to be moving on into our next section here uh and in this next section I'm going to show you exactly what we just described here I'm going to show you this method right here of how to create a date table that's what we're going to be covering next okay all right and and uh there's just a comment in the chat there is a great third party I'll go ahead and promote this they're uh there it's uh from SQL bi they have a great tool called Bravo uh and I believe it's a free tool um that will actually create a date table for you as well definitely check that out that's at sqlbi.com uh they're another great company by the way they do some of the same stuff we do but hey we're we're friendly with them uh they have a great uh tool called Bravo out there that will actually create a date table for you all right but I'm going to show you how to do it on your own let's say you don't necessarily want to use another tool I'm going to show you how to create a date table on your own and we're going to be doing it using the Dax method that I talked about earlier all right so if I want to create a date table on my own what the way I'm going to guide you through doing this is to go navigate over on the left hand side we're going to go navigate over to the data view found right here so if you're following along with me go to the data view on the left hand side to be able to follow along okay all right so I'm going to go ahead and select the data view that little icon on the left and this is good by the way what the data view does is it literally shows you a view of your data it's kind of like a read-only Excel you can't you can't come in here and change any of the data you can read it all you can't change it necessarily but you can look at it and the thing I like about this view is when you're creating a date table with Dax is it'll actually allow you to see the columns and the data behind the columns that you're creating as you go so what we're going to do is we're going to start by creating a new table again we went to the data view so if you're following along make sure you select the data view here with us first and then under the data view we're going to go up to the top and select table tools it should already be open by default and then we're going to select new table right here so we're going to create a new table with Dax by selecting the new table option under the table tools and we're going to be doing this using Dex all right so I'm going to go ahead and select new table and when we do that this is going to be our first exposure into the Dax formula language here okay so with inside of the Dax formula language this allows us to be able to create calculated formulas calculated tables calculated measures to be able to enhance our model and create new objects that our users can take advantage of whenever they're building out reports now the way that this is exposed with inside of the tool is you'll notice this new formula bar appears at the top of my screen and to make this a little easier to see I'm going to zoom in on this and for those of you on a Windows device you'll use the control key in your mouse wheel if you want to zoom in or zoom out of that formula bar Control Plus Mouse wheel will allow you to zoom in and zoom out okay all right so over here now in the formula bar a couple things I want to point out to you everything to the left of the equal sign right here everything to the left of the equal sign is going to be the name of our object so the name of our table the name of our column the name of our measure whatever it is we're working on and then everything to the right of the equal sign is going to be the definition of that object okay so what I mean by definition is this means stacks so you're going to name it to the left of the equal sign and then to the right of the equal sign we're going to actually uh provide the definition of it okay all right so here's what we're going to do is in that formula bar we're going to go ahead and rename the table so where it says table we're going to rename it instead of calling our table table we're going to go ahead and rename the table to be called calendar and I'm going to zoom in on this even more just to make sure it's really clear for you I'm going to go ahead and type in calendar okay all right then that's going to be the name of our new table and then to the right of the equal sign we're going to need to write the Dax that will Define what our table is going to return back and we're going to use a particular function called calendar Auto okay now the calendar Auto function which you can see on my screen right here what it does is it scans all of the date columns you have with inside of your data call your data model so it's going to look at any date columns I have with inside of my data model and what it will do is it will create this continuous list of dates continuous meaning there's no breaks there's no there's no missing dates but it's going to create this continuous list of dates into a new table for me it's going to start at the very first day of the first year that I have with inside of my data model and then it's going to end on the last year of the it'll just end on December 31st of the last year I have with inside of all my date values so calendar Auto automatically generates this list of date values inside of this new table okay and I see a question why are we creating a day table you may want to rewind the video a little bit we did some whiteboarding where we talked about the why uh you can go back a little bit and we answered that question of why we're doing this all right so I'm going to go ahead and select calendar Auto and uh by the way this is called intellisense let me briefly mention this as I start to type in the name of the the function I want to use this is Dax and you'll notice this little section right here this is called intellisense and it's designed to help you write back so if you're not really familiar with writing Dax you can leverage this intellisense to help you be able to return back values that you know if you're not an expert in Dax and you don't know every single function that's available to you then you can use the intellisense to kind of guide you through uh writing it here if you need it okay if the intellisense isn't working for you uh unfortunately I can't share screens with you to see what's going on but you may want to just uh backspace a little bit and try again all right but we're going to go ahead and type in calendar Auto and I can actually click on it right here if I wanted to and underneath calendar Auto this would allow me to be able to return back that special calendar Auto function okay and if I go ahead and do a close parenthesis on this that would close out this function now one thing worth noting there is special capabilities with calendar Auto if you're dealing with a fiscal calendar remember we talked about fiscal calendars earlier that's one of the reasons why you might consider using this function because you can tell it when is the end of your fiscal year when you use the calendar Auto function now we're not going to be taking advantage of that capability today but I did want you to know that there's some capabilities when you're trying to build out a date table and include fiscal values with it all right so we're going to go ahead and type in calendar Auto and do an open and close parentheses and uh I'll tell you what I'll do I'm actually going to put my calculation here in the chat for you so for those of you following along you'll be able to kind of look in the chat and I put my function there but I'm going to use this calendar Auto function to return back a list of all of the date values that I could use with inside of my data set okay now again all calendar Auto did was it scanned the date columns that I have and we do have one date column called closing date and it returned back and it returned back a list of all of the possible dates we might need this is a distinct list meaning it's a unique list of date values that we have available to us with inside of this new column that got created for us right here so you can see this new column called Date got created after we used that calendar Auto function okay all right very good uh question the chat what if we need two more years to do some forecasting or something like that so uh calendar Auto might not be the right answer if that's the problem that you're trying to solve so the question that's popped up below my face right now is what if I need Beyond just the current years that are appearing whenever I use calendar Auto in that case you might want to use a different function called calendar but not calendar Auto but just calendar and what the calendar function would allow you to do is you can pass in specifically which start date and end date you want to have whenever you're using the calendar function so there is an alternative there if you need to have future dates as well then that's kind of what you can do to be able to manage that all right very good so now that we have this new column and new table created by the way you can see my new table over here called calendar it appears you'll notice the little count calculator icon on it that tells you that little calendar calculator icon indicates that it is a calculated table so it indicates to you that it is a table that was created using Dax uh if you see that little calculator icon on it right now our calendar table only has one column but we're going to add a few other columns to it as well okay so if we want to add in a second column and a third and a fourth column we'll actually have a total of four that we create here if we want to add in another column into this data set we can do that by going up to the table tools tab again and this time we're not going to click new table because we already did that this time we're going to create a new column so for those of you following along we're going to create a second column here and we're going to do that by going up to table tools and then selecting new column all right so I'm going to go ahead and select that all right so what this is going to do is it's going to pop open another new formula so if you look up in the formula bar here you'll notice it's kind of blank again waiting for us to create a new column and what we're going to do is we're going to create a a year column I want to have a column that parses out the year from the dates so if I wanted to create a slicer or a filter where I just wanted to filter on the year 2022 or 2023 or 2024 or whatever the Year may be I want to have a column where I can select which year that I want to filter on so back inside the formula bar up at the top here we're going to rename the column remember my tip for earlier everything to the left of the equal sign is the name of the column everything to the right of the equal sign is going to be the definition of that column so to the left of the equal sign we're going to rename this column here and then to the right of the equal sign we're going to give this a proper function and don't worry we will take a break here shortly I see uh we're going to take a break right an hour and a half which is about five minutes uh so the year function or the year column that we're going to do is we're going to leverage the function called year with inside of power bi and you'll notice there's lots of other functions in power bi that leverage your capabilities like same period last year or previous year or start of year lots of other functions you can use here but the one that we're going to use is the year function and we're going to pass end of the year function a date column okay so using the year function we can pass in our date column that we see right here and the way that you reference a date column is you reference really any column Name by using square brackets so think of brackets like this that's a bracket and that's how you refer to a column name with inside a power bi so if I use the square bracket get a little over antsy there with my brackets if I use the square bracket you'll notice intelligence immediately tries to help me out and it lists all of the column names that I have with inside of this table that I could potentially select from so what I want to do is I'm going to select the date column and you could type it in there if you want rather than selecting it like I did you'll notice intellisense to continue to try to help you out you can ignore this additional help that it's trying to do and just do a close parenthesis so what we what we did here is we said that we want to parse out the year from the date and I want to only return back the year all right I'll put that in the chat for you as well well so yeah to Sydney's question here square brackets is equal to column names now sometimes you may also want to refer to the table name as well and just in case that same column name exists in multiple tables you can also refer to the table name as well if you use single quotes So it would look something like this this is optional the the table name is optional here but if you want to be very precise with how you refer to column names you can first refer to the table name which is called calendar and then the column name is going to be in Brackets table names and single quotes column names and brackets all right but the the table name is actually optional here we don't have to do it unless you really want to refer to the table name okay all right yeah if you missed something just go ahead and Rewind a little bit you can rewind the video and catch back up all right so we're going to create two more columns before we take our break uh to create another column we're going to do that by going back up to table tools again okay so I'm going to go back up to table tools and underneath table tools we're going to select new column again right here okay we're going to create two more columns so I'm going to go back up to table tools and select new column and in this third column that we now have we're going to name this one month number and there's a specific reason why we're calling it month number that will come into play after our break so hang tight I'll answer the questions of why we're creating a month number column we're going to also create a month name column here in a few moments as well so I'm sure there'll be some questions around why we have two month columns I'm leading you a little bit into our demo after our break so uh just call this one's going to be called month number and then to the right of the equal sign we're going to define the month number column using the month function and remember we use the year function in the previous example this time we're going to use the month function here and just like we did last time we're going to pass the date column into it so if you would like to put the table name in it I saw some people like some people in the chat like to put the table name in it as a best practice no problem you can put the table name in it uh so the table name would be calendar and then the column name that we're going to refer to here is going to be our date column all right so all we're doing in this example again is we're saying here's our month sorry here's our date column I want to parse out the month number from the date and that's what we have on our screen right now is we're using this month function to say return back the date the the sorry return back the number of the month from the date that we're looking at okay so yours should look something like this I'll put this in the chat for you so if you want to copy my code from the chat you should see it in there as well all right so now that I have that new column created we're going to create one final one before break and this one is going to return back the name of the month so I want to have the month number but I also want to have the month name so we're going to create one final column again going back up to table tools we're going to select new column for the final time here okay so I'm going to go ahead and do it on my screen as well table tools in the new column and Manuel there should be a reference I gave you a link to if you don't mind sharing that here coming up uh it's going to be to more information about the format function if you can have that handy but what I'm going to use this time is we're going to name this fourth and final column that we're creating we're just going to call this month you can call it month name if you want but I'm going to just leave it as month by itself and then what I'd like to do with this one is I want to return back the name of the month rather than the number of the month and there's going to be some reasons for us having both of these columns that we'll discuss later but we're going to start by creating a new function using the a new column created using the format function okay all right so we're going to go ahead and we're using the format function we're going to tell it that we want to format that date column that we created earlier so we're going to refer to that date column and we're going to format the date column in a particular way I'm going to use four lowercase M's and using those four lowercase M's that will allow us to format the month spelled out so if I hit enter now you'll notice that it creates this new column for me called month and then all of the values here are returned with the spelled out month name I could also do three lowercase Ms and it'll bring a month abbreviation I could optionally do three lowercase M's with yyy and it will bring back the month and year so you got a couple different options there but what we're going to do for our example is I'm just going to return back the month name but the point is I want to show you how the format function has a lot of different options available to you I would certainly recommend exploring that link that Manuel put in the chat for you there's lots of other ways you can use that format function Beyond even just dates you can use it for other purposes as well okay all right very good well uh we now have our date table created we have our four columns our date column your column number column and month column now we are going to take a break so we're due for a break we're about an hour and a half in a little bit more than an hour and a half we're going to take a 10 minute break and then when we come back from break we're gonna keep moving forward we've got a few other things we're going to talk about but let's go I'm going to put a 10 minute timer on my screen so you know when to return back I'll see everybody back in 10 minutes and uh good luck so far see you then thank you foreign foreign [Music] [Music] [Music] foreign foreign foreign [Music] [Music] [Applause] [Music] foreign foreign foreign all right everyone we are ready to come back I'm going to take this off my screen and I want to share a few things as we come back from our break hopefully that break was timely for you I saw quite a few people were ready for a break as so was I uh but what I want to talk about briefly as we return back is if there is interest for additional training obviously pragmatic Works hopefully many of you are already familiar with us but our sole focus is centered around training and education that's everything that we do at pragmatic works is focused on that now for those of you attending today's session uh and this is that we do have a special offer that's going to be centered around our live top boot camps so if you like the idea of live training if you enjoy uh in-depth training obviously today's three hour class I will not be able to make you an expert in power bi in three hours however we definitely have the tools to be able to get you there and so I want to share with you I'll pull this up on my screen here for a moment uh we have going on through the end of this month you can let me hide this I'm not sure if you're seeing that or not but there we go about going through the end of February you can look at any of our boot camps and get 250 off so if you have an interest in being able to attend some of our live training we do have a lot of live boot camps that we teach we have three different boot camps that we teach on power bi we have a more traditional power bi boot camp that's four days long we have a Dax boot camp that's four days on nothing but back so if you really are eager to learn more about Dax I would certainly recommend our Dax bootcamp and then we also have a paginative reports boot camp as well that's a three-day course and so if you're looking to go more in depth into any of those areas paginator reports is part of power bi we have many different boot camps on those different types of courses and so when you go to our website I'll pull up our website here in just a moment but when you go to our website and you select a boot camp you can put in the code lwtn learn by the Nerds 250 and that will give you 250 off our your boot camp so just to give you a little peek at that uh these classes I see questions are they in Dubai these are all virtually taught so they are virtually generally taught in Eastern Time Zone you can find we have a pretty big list of boot camps that are coming up on a variety of topics not just power bi but we have some on Azure uh power apps T SQL T SQL by the way is a great skill to learn even for power bi developers I would certainly recommend going through our offerings of boot camps I'll put this link in the chat so if you want to take a peek at boot camps we offer you can scroll through see which one's appropriate for you but these are really designed to go in depth go beyond the basics of what we can do in a three hour session or even a one day session and check out what we have there and see if that might be a good fit for you we have many different boot camps that are coming up and again that discount that I put on my screen I'll pop it up one more time just so you can see that again that discount is good through the 28th of this month but you can schedule them for boot camps that go till the end of April so uh keep that in mind that is 2023 make sure in case someone's watching this is a recording a year from now it's uh 2023 is when that's for all right very good well thank you so much if you have questions on those as well feel free to I'm sure our team will be happy to answer any kind of questions around the sale in the chat so that's available uh for your uh for any questions you might have all right great so I'm going to take that off my screen now thank you so much for listening and being patient for for hearing about our boot camp offering what I want to continue with now is to get back to the demonstration that we left off on and uh what we're going to be focusing in on over the next half of our day is we're going to be looking at our data model and data visualizations and then we'll wrap up with the power bi service so we've got quite a bit to cover in an hour and a half here so we will at times go a little faster and don't don't worry don't don't forget this is recorded you can always go back rewind a little bit and go back to see something again as well okay all right so we left off we created our date table this on my screen right now is exactly where we left off and then what we're going to be doing is we're going to uh go a little deeper into what do we now do with this date table so we created this date table now what do we do with it right so I want to be able to leverage this date table with inside of my model I want to be able to actually see the results of my day table with inside of a visual so if we want to actually leverage this date table we created in a visual we're going to work our way back over to the report view right here this is the report view okay all right so if you're following along with me navigate back over to the report view for a moment and inside the report view what we're going to do is we're going to create another new visual okay and if we want to create a new visual we need to make sure we click in the background of the report okay don't have the other visual selected and with no other visuals selected we're going to create a quick little column chart to show us the number of failed banks by month for example so if I go to select the Stacked column chart right here that will allow us to Monitor and show by month the number of failed banks that we want there's lots of other visuals we could choose we're going to go ahead and select the stack column chart for this example all right so I'm going to go ahead and select stack column chart and that will bring the stack column chart on our design canvas here we can reshape it or resize it however you want I'm going to lay it out kind of like this like so and with inside of my stack column chart I can start to bring in new Fields into it right so just like we did in our first visual it's all about dragging and dropping the visuals into the design Canvas OR into the fields pane the visualizations pane so that way you can start to build out uh your Visual and actually get data returned back in your visual so what I'd like to do in this example is I'm going to go over to my Fields list on the far right so this is our Fields list on the far right over here again we're in the report view now and I'd like to be able to see the number of failed banks by month so to do that I'm going to drag in the month column into the x-axis like so and I'm going to bring in the bank name column right here into the y-axis just a little bit lower below it and just as we saw earlier where we saw the count of banks by State this now should return back the count of banks by uh by month okay all right so I've showed you on my screen what I'm about to do now let's actually do it myself so I'm going to go ahead and bring in the month column down into the x-axis like so and I'm going to bring the bank name column down into the y-axis like that okay so your screen should look like what you're finding right here x-axis has months y-axis has count the bank now if I zoom out for a moment you'll notice the visual that it creates probably is not what we expected we're actually seeing the same number of banks appearing for every single month this would appear as though 563 failed Banks happened on every single month clearly that's not right and so this is actually uncovering a problem in our data model the problem is that there's no connection between the two tables that we're using so if you look over on the field list you can see that we're using the calendar table and the month column and we're using the failed Banks table and the bank name column however the thing that we're missing in Jackie's right on in the chat there is we need to create a relationship between those two tables by default it did not create a relationship for us sometimes power bi can do that but in this scenario it didn't actually create this relationship between the two tables for us and the result of that is we see the same number of failed banks for every single month not quite what we expected so if I want to be able to fix this problem we need to pay a visit over to the model view on the left hand side so on the left hand side of my screen right here we're going to go to the model view and from with inside the model view that's where we can create relationships if they don't already exist okay and again by creating this relationship the problem that we're trying to solve is that we see the same number of failed banks for every month that's the problem we're trying to solve here all right so if you want to follow along with me we're going to go over to that model view that you see right here I'll go ahead and select it on my screen as well and the model View kind of looks like a database diagram right it's going to show you a diagram of all of the tables that you have with inside of your data model you can zoom in on this by the way if you use your hold down the control key and use your mouse wheel you can zoom in on this a little bit to make it a little easier to see but what I'd like to do is I want to be able to create the relationship between these two tables now the question for you is probably how do I know what the two how do I know how do I Define the relationship between these two tables and here's the thing you really have to think about from your point of view when you're doing this on your own what you need to ask yourself is this question what is the commonality between these two tables what is the thing that both of these two tables have in common and when you look at it pretty closely you'll probably be able to figure out that the commonality between these two tables is the date right you have a date column and the date the calendar table and you have a closing date column in the failed Banks table that's really the commonality between those two tables or the two tables we're looking at so if I wanted to create a relationship between these two tables we can do that by simply dragging the date column and when I say drag I mean you're going to click on date and you're going to click and drag dates on top of closing date right here click and drag one on top of the other now a common question that we get well can is can you drag closing date on top of dates and absolutely you can do that power bi is actually smart enough to figure out what direction the relationship needs to go regardless of which direction you drag the relationship so what happens after you do that drag and drop and let me show you that one more time what happens when you drag closing date on top of Date you'll find that it automatically designs the relationship for you it automatically creates the relationship for you and you're seeing that on my screen here that line that relationship line between the two tables is the indication that these two tables are able to communicate with each other now when you hover your mouse above the line you'll actually see that it highlights The Columns that are the basis of the relationship so when I put my mouse above the line notice that it highlights the date column and the closing date column those are the columns that are the basis of the relationship here okay so there's a great question and I'm going to go ahead and pop this up from Marty Marty asked a question that they've seen double lines occasionally double arrows so what you might be noticing on my screen right now is I have this single arrow pointing right here and so the question is what is that what does that mean that Arrow indicates the direction that filters can occur so whenever you go to build a report and you say that you want to filter the number of failed banks by year you're allowed to do filters going in this Direction only based on how the relationships are designed right now that's what this little arrow indicates that's the direction that filters are allowed to navigate the direction that filters are allowed to go if you see double arrows double arrows would look like this don't worry about how I'm getting I just want to show you if you see double arrows like this that's what Marty's talking about that means that filters can go in either direction you can filter fail you can filter down calendar from failed Banks and you can filter fail Banks from calendar either direction is allowed there now there are some extra implications of you having that turned on we don't really need that for today I'm going to leave it back how it was I just wanted you to know that's the difference that's what the arrow means there the arrow indicates the direction that filters are allowed to be applied which Which Way can they go okay good question let me go ahead and hide that one all right so the other thing that's worth noting here is the indicators that you see right here and right here this is what's known as a one to many relationship or many to one the the way that it's aligned right now but let's the more common way of phrasing it is this is a one to many relationship what a one-many relationship means is that the column from our calendar table that's our date column and the column from our failed Banks table that would be our closing date column have different instances of how many times they can appear when you see one uh the one side of the relationship that means that the date column is unique meaning you're never going to have the same date appear more than once with inside the calendar table however closing date is not unique because and it kind of makes sense right you can think about it I'm allowed to have more than one Bank close on the same day so it's not possible that my failed bank's closing date would be unique because I very likely have had some banks that happen to have closed on the same date so that's what the one Dominion means it means that you have unique set of values on one side of the relationship and not unique uh set of values on the other side of the relationship now this is where I would highly highly recommend that you review that data modeling and well maybe you can share it again we have a three hour session specifically around data modeling where we talk much more about the different types of relationships uh I wish I could go more in depth into it today we have a lot of other things that I want to cover though but what I will mention is that one to many relationships are the most desirable type of relationship to have in power bi there are other types that you can leverage and use there's many to minis there's one-to-ones uh those are less ideal for a number of reasons that I wish we had more time to talk about today but generally speaking one to many relationships are what you want to aim for whenever you're building out your model are there exceptions to that statement absolutely there's there's certainly exceptions to that statement but generally speaking uh you want to try and stick to one-to-many relationships and that's where that idea of a star schema comes into play a Star schema is where you have traditionally many different one-to-many relationships and Manuel did put the links to the the YouTube class I was referring to and the shorter video in the chat if you want to go back and refer to that again later all right so we have designed what's known as a one-to-many relationship it's Unique in the calendar table it's not unique in the failed Banks table and that's the way we want it for this example all right so now that the relationship is created we can actually go back over to the report view up in the top left right here so we can go navigate back over to the report View and we can see now that the relationship has been created it will fix part of the problem that we have in the column chart we created a few moments ago there's going to be some other problems we need to fix as well but this uh first problem of seeing the same number of values for every month has now been fixed by creating the relationship so let's go navigate back over to the report View and you'll notice when we look at our column chart that now it shows a different set of values for every month we're not seeing 563 failed banks for every month anymore now it's showing a little bit more of the way we would anticipate it to show here now that we've done that okay looking good all right now I did see someone else identified a problem earlier in the order of the columns in the column chart they noticed that the months were not sorting the way you would traditionally think they would sort and so that's the next problem that we're going to walk you through is how do I fix the sort order now the sort order problem is really kind of a it's about the data modeling problem but it's also a specific problem with the visual itself that we can fix as well and so we're really going to walk you through both how do I fix uh the sort order of the visual and then how do I fix the sort order of the data model those are really two different problems we're going to discuss here all right so to fix the sort order of the visual we're going to go hover above the visual like so so have your mouse hovered above the visual and you'll see there's this little three dot menu in the top right it's called more options we're going to select more options and underneath that three dot menu when you click on it you'll see there's a sort access that you can modify and the change that we're going to make here is we're going to tell power bi that we want to sort by the month and that we also want to sort in ascending order so we're going to make those two changes right here and right here now when you do this after you change one of these two settings the menu will clear out and you have to come back into it a second time that's why I'm kind of drawing on my screen right now so that way you have an idea of what you're going to end up doing after we make these two two different changes all right so here's what we're going to do again we're going to click on the three dot menu select sort access and we're going to sort by month then you'll notice that it does change the sorting by month however it's sorting alphabetically in descending order so we need to also go back into that three dot menu click more options select sort access and choose sort ascending okay so this is the two core changes we need to make here sort by month and sort ascending all right so I'll go ahead and select that and you'll see now that it does change the sort order of our visual however the sort is probably not the way you thought it would sort right it looks like it's sorting based on the month name not the month number okay and Randy has a comment in the chat you know it would be great if we could also accommodate year in here some way absolutely we might put in a year slicer we might do a couple things in here because right now this is showing the number of failed banks for every year the way that it's laid in here right now so you could add other things in here uh Randy is absolutely right about that I would put like a slicer or something like that in here all right so here's what I want to do I want to now that we see it sort by month I want to also make sure that it's sorting by the chronological order rather than the alphabetical order right we would never want to sort it this way this doesn't make sense as a way to actually sort your data instead what we want to do is we want to sort it chronologically so to the question right here in the chat that's what we're going to be covering next how do we fix it so it sorts chronologically rather than sorting alphabetically okay so let's talk about how to do that to change the way that it sorts chronologically this is really more of a data modeling problem than it is a problem with the visual the previous problem we fixed by going into the properties of the visual and changing the sort order to change how the column itself behaves then we can actually show our change the data model to ensure that it actually properly sorts chronologically or by the month number remember I told you the reason we were creating the month number column would come back later it's coming back now uh now we're going to use and leverage that month number column we created and here's what we're going to do let me whiteboard this briefly the steps that we're going to do is we want to display the month name but sort by month number and that's what I'm going to walk you through how to set up here in this next little mini demo this is a pretty small one okay so if I wanted to display the month name but sort by the month number what you would need to do is you're going to go select from your calendar table from the calendar table you're going to select the month column click on it not the check mark But actually the name of the column and by selecting the month column we can then change the sort order of that column by using a certain property that I'm going to show you next so your first step for following along is you're going to go over to your Fields list on the far right and you're going to click on the name not the check mark But the name of the column called month from your calendar table with that column selected you can see it's highlighted on my screen right now with it selected we're then going to go up to the column tools tab up top now if you're not seeing column tools that means you didn't do the previous thing I just showed you you're not going to see the column tools tab up top unless you first select the month column so if you're not seeing column tools on your screen make sure you go back over here and select the month column first without that you're not going to see column tools all right so with the month column selected we're going to go up to column tools right here and there's a property called sort by column right here and what this column allows you to do is you can display whichever column you selected earlier but then you can sort by a different column and what I want to do is I want to sort by the month number while still displaying the month name all right so that's what we're going to be doing in this little mini example here so for those of you following along don't forget select the month column first then come up to column tools right here select sort by column and choose month number and that will allow you to display the name of the month but sort by the number of the month all right so let me go ahead and do that on my screen as well I'll select month number and watch what happens to our visual now look at that that looks beautiful right we can see January February March April May all sorting the way you would anticipate they should sort whenever you look at date data right so looking pretty good there great all right so looking like we got that set up I'm trying to see I think I actually answered a bunch of these questions that uh you start so I think we're good there all right so let's do this now that we've got the data sorted here properly and everything's starting to come together the next thing that I want to show you is how you can create calculated measures okay so before lunch or before lunch before our 10 minute break I should say we created a set of calculated columns with inside of our date table right with inside of our date table we created a month column a year column a month number column but we haven't really explored measures yet so that's what I want to do with you in this next section is walk you through creating and sorting not creating and sorting but creating measurement data okay and very briefly I want to whiteboard with you and describe the difference between calculated columns and calculated measures okay now I'm going to make some generalizations here there's definitely some exceptions to what I'm going to describe here so don't hold me to a strict definition of each of these because there's definitely uh exceptions to some of these rules I'm about to tell you now calculated columns are like the dates table we created earlier remember in the day table we created a year column we created a month column our month number column and then we created a month column at the end those were all calculated columns that we created okay so these are examples of calculated columns um oh thank you looks like I misspelled calculated I saw someone put in the chat that should be a d thank you um but calculated columns are kind of fundamentally different with how they're used generally speaking again I'm making some generalizations here but generally speaking calculated columns should be used or are used for more descriptive type data okay what I mean by that is something that describes a metric of some kind okay again there's exceptions to this but I'm trying to make some generalizations so that it's easier to understand the difference between these two so calculated columns more for description type data calculated measures are a little bit different so calculated measures are going to be used for more metric data okay so when you think of metrics generally metrics are going to be numeric in nature right you're going to have some kind of numeric value uh their calculated measures are often generally going to have some kind of an aggregate value that go along with them okay so they're going to be aggregations if you're not familiar with what an aggregation is that's something like a sum A Min a Max an average uh standard deviation account so on and so forth you kind of get the idea those are examples of aggregations so generally calculated measures are going to be different because their metric values are trying to you're trying to measure something right get that's where the word measures comes from they're generally going to be numeric although there are even some very rare exceptions to that and then they're going to have some kind of an aggregate that goes along with them I'm trying to sum count min max average that sort of thing so we don't have a ton of time in today's class for me to show you a bunch of measures but I want to show you at least one I want to give you a peek at how you create a measure and as we go through that you'll see how it's a little bit different than how you create columns it's not a lot different but it is a little bit different uh and how you would create calculated columns like we did earlier in this session so here's what we're going to do I'm going to create a new measure and the way that I'm going to create a new measure is by going over to my failed Banks table so I'm going to go over to the right hand side and look at my Fields list now I will mention this if I had a lot more time there are some special tricks and best practices around creating measures some people actually like to create a whole separate table for measures that stores only the measures and nothing else I actually think that's a great practice we're just a little limited on time to show every little nook and cranny of things you can do here but if you're familiar with creating like a calculation table it's a great way of organizing your measures so everybody can so any of your users that are developing reports can go look in one place and see the measures that are available to them I'm not going to have time to do that today but just know that you can create tables that their sole purpose is just to store measures that is a common practice and a way of organizing measures for today's class for interest of time we are going to create a measure with inside of my failed Banks table so with inside of my failed Banks table that we already have I'm going to right click on my failed Banks table right click right here and select new measure okay so again we right clicked on the name of the table in our Fields list so right click here and then we're going to select new measure after we do that right click okay all right so I'm gonna go ahead and do that on my screen as well I'll right click on failed Banks and select new measure now when we do that what's going to happen up on the top of our screen is just like what you experienced earlier this morning remember this morning or before our uh I say morning I realize people are watching from different time zones but if I go up to the top here you'll notice that the formula bar now has a new calculated measure ready for me to write and then everything to the left of the equal sign is going to be the name of our measure and everything to the right of the equal sign is the definition of our measure so what I'd like to do is we're going to go ahead and rename our measure I'm going to come up to the top here and I'm going to rename our measure to the left of the equal sign and I'm going to call it total thanks and then to the right of the equal sign we're going to create a measure that returns back the count of banks now there's lots of different ways we can do this but for Simplicity for today I'm going to use a function called count rows do note though there are lots of different count functions that are available to you notice here there's a really big list of different count functions you can use the one however we're going to use for today's example is Count rows which allows us to count all of the rows with inside of a particular table and the rows that we want to count are going to come from our failed Banks table so remember from earlier we talked about single quotes or how you identify a table name and we're going to tell it that we want to count all of the rows and our failed Banks table like so and that's it that creates this count rows function for us if I want to or not if I want to I'll actually share that function in the chat with you so if you want to have that handy you can grab that from the chat but now we've created a measure and the benefit of creating this measure is it's now uh reusable you know remember before we were dragging in the bank name into different places well that didn't really make sense to do it's not it's not Bank name we really want to see a total count of Banks and so while we were kind of leveraging the bank name earlier to do a count like you see right here really what we should have been doing was we should have created a measure like we did now and we should have used that total Banks measure instead of just counting the number of banks that's a better practice for a number of reasons uh one it's better because it's now reusable and I can Nest it inside of other calculations I create later two it's also beneficial because I can do specific formatting for it up here and it saves that formatting and that formatting is then reusable and it's uh it doesn't necessarily help that much more with performance or anything like that the benefit here is not performance related it's more from a usability standpoint it makes your life a lot easier to do this as a calculated measure versus a calculated column the difference performance wise is probably pretty nominal uh but the the the the making your life easier benefit is a much much better better way to go okay all right so if I wanted to take advantage and actually show this measure in action I could create a set of visuals or another Visual and I could start to bring in some of the things that we've done here or I could even replace the count the banks with inside of the other measures by simply selecting the visual and when I select the visual I can then change out of which particular fields that we're using okay so you can see right here I have count to bank name I could get rid of that and I could swap in instead I could bring in the new Total Banks measure that we created that you see right here okay now the net result is it should look almost exactly the same as what it looked like before that's kind of the point uh but it makes it so it's much more reusable and I can Nest this calculation calculation or calculated measure inside of other measures I create in the future as well okay all right very good all right so I think that's about I want to make sure we have time to get into the data visualizations as well as time to get into some of the power bi service elements later on as well so we're going to call call it into the data modeling section for today and what we're going to shift gears into next is the data visualization aspects of power bi now the good news is we've already gotten a head start here right as we've been working with inside of power bi we have actually explored many of the different data visualization elements as we've been going but I want to add to that I want to spend some specific time talking about just data visualization best practices in different design elements that you might want to consider for example let's say that I wanted to uh enhance or change or modify the visuals that we've used so far in some way you can modify the appearance of the visuals that you're using so far by first selecting the visual so you'll need to click on the visual and with the visuals selected you can then modify it by going over to the format section over on the right hand side that would be found right here again don't forget you first have to select the visual to modify the visual but with the visual selected if you want to change the appearance of the visual say for example the fonts or the colors used or something else you can do that by going up to the format button found right here and this allows you to format the visual that we have selected right now so I'm going to go ahead and select the format button and again you need to have the visual selected first but with the visual selected if I go over to the format button I then have the ability to modify the visual through a number of settings that are made available to me in this section right here uh there are two different tabs you can kind of toggle back and forth between you can look at the general settings where you can change things like the title so if I want to change the title I can do that here or you can look at the visual settings and underneath the visual settings you can do things like add-in data labels so if I want to add in data labels to my visual I can do that by going over to the data label section underneath the visual modifiers and again how did I get here well I got here by clicking on the little format icon right here you have to have a visual selected first so if I want to add on data labels I can click on the data label button found right here and that will make my visual or allow my visual to Now display data labels with inside of it and when I turn that on notice you can now see data labels that appear above every one of the columns in the column chart looks pretty good looks really good now you can also modify those data labels if I expand the data labels section I can modify and say hey instead of seeing those data labels in a certain position let's actually change the position of those data labels to show inside the end of the column chart and when I do that you'll notice now all the data labels are actually inside the columns in the column chart they were above previously so the point is you have lots of different things you can do to kind of go in and modify the appearance of your visuals that's just one example adding data labels but the way that we found that the way that we got to it is by selecting the visual right here and then going over to the format button right there and you'll find lots of different options as far as changing the colors changing the font adding data labels that's all found underneath the format section with inside of power bi and the change that we made was we turned on data labels okay now I show you that because I really want to lead you into a more advanced topic around that just a little bit more advanced so what's nice is that you can change your visual's appearance but wouldn't it be great if you could change the appearance of all of your visuals at once okay maybe I really want to modify every one of my visuals at once and and how to set standard color scheme a set standard font type I want to basically standardize the way that my reports are designed and you can do that using a feature called themes so that's what I'm going to show you next a little bit about how to leverage and use themes so themes are again the purpose of them is to standardize the look and feel of the reports that you create so they're all using the same colors they're all using the same font same background color same primary color all of that you can modify with inside of a theme and the where you're going to go and I think this is going to answer Antonio's question Antonio you got like the Hot Pot mic here on questions uh San Antonio's question is can you import color schemes and yes uh you can you can actually we'll give you a little peek at that here in a few moments as we discuss themes but absolutely you can kind of modify the colors that are being used you can customize the colors you can use HTML colors there's all kinds of different things you can do around colors and we'll talk about that as we go through this next little demo all right so let's talk about themes how do you create a theme how do you modify a theme how do you export a theme that's all we're going to discuss in this next section so themes are something that you create by going underneath the view menu inside the power bi desktop so up on the top I'll zoom in on this so you can find it up at the top you'll find there's a view menu and Under The View menu there's a section here devoted to themes right here and if you want to change the theme you can change to one of the default themes that are provided to you or you can make your own Theme by going underneath the themes section right here all right so we're going to show this in action as on my screen as well again we're going to go under the view menu and select the themes section right here when we expand the theme section you'll see this whole new area pop up on your screen and you'll see there's some predefined themes that you can select from right here some of them are good some of them are okay there are some one for accessibility reasons that have been added there's ones like colorblind safe themes which are kind of nice uh and then there's some other themes that are kind of interesting uh maybe you go with the very bright purple theme I'm not sure if that's one you would select but maybe in reality you want the ability to create your own theme so if you want to create your own theme you can do that by going down to the section of the bottom again this is Under The View menu and you expand the theme section here you'll see there's a section here called customize current theme on the bottom left or towards the bottom left and if you select customize current theme that allows you to be able to modify a theme modify and create a theme of your own and then to Dan's question I'm going to go ahead and promote Dan's question here for a moment Dan asked can you import a theme used on your company's website so basically what happens and I'm going to walk through portions of this as we go through short answer is yes you can import a theme that someone else has created however it does have to be created with inside of a Json file the Json file is just basically a way of storing the metadata for how the theme is going to appear so short answer is yes you can import a theme can you import it from your website oh it may be it may be a little bit different than how your website themes are stored but I'll show you you kind of get the the picture of how that's done here as we go through this demonstration all right so what we are going to do is I'm going to select that customize current theme option found right here okay so I'm going to go ahead and select customize current theme and that's going to pop open a new dialog box in the middle of our screen in the middle of our screen we're seeing this option here where we can customize the theme that we have selected and I can make any kind of modifications to the colors that are being used you can name the theme you can do all sorts of stuff now you don't have to follow me exactly here if you want you can you can make your theme look different than mine uh normally what I would do if I was teaching this class live is I say all right I want you to make the ugliest theme possible we're going to have a competition of who can make the worst looking thing just for the fun of it but unfortunately you won't be able to share your theme with me so you'll just have to look at my Ugly theme all right so here's what we're gonna do I'm gonna go ahead and start by naming the theme up at the top no one is really going to see this name other than internally with inside the power bi desktop so I'm going to call this theme our learn with the Nerds theme okay you can call it whatever you want it's really only going to appear that name only appears inside the power bi desktop your users will not know that then I'm going to change some of the colors so I can change any kind of color palette that I want here so there was a question earlier can you import colors you really have kind of the world as your oyster when it comes to Colors because when you try and change one of the colors here you'll notice you can put in RGB colors you can put in HEX code colors you have lots of options as far as the kind of colors that you can select from here I'm going to go again I'm trying to create a pretty uh gnarly ugly looking theme here I'm going to go with kind of an orange looking color here okay all right so I got an orange color is going to be my primary color used with inside of my theme now you could change the other colors here color two color three four five six seven eight you could change as if you wanted to we really don't have a need to do that you'll notice we're really only leveraging one color inside of our design here so I'm going to leave it as color one and then we're going to move on to some other things now you can also change the text if you go over here on the left hand side you can modify the text options if I go ahead and select that you can standardize the text font families that are being used so I could change here this to a different font type by the way a common question is can you select the font type that's not listed here there is a little backdoor way of doing it there's uh it's going to create a theme file for you that I mentioned is called a Json file and you can actually pass in other font families that are not listed here through that Json file so there are some little backdoor ways to even expand beyond the font families that are shown here but for the time's sake I'm going to go ahead and select uh Ariel for our font family all right then I'm going to go down to the visuals section on the left hand side I'm not covering every element of this just kind of highlighting some of the brief areas I think are most interesting to you I'm going to go to the visual section here next and I'm going to add a background color to every one of my visuals so I'm going to change the color of my background and maybe kind of like a gray color here so that means every one of my visuals is going to have this nice little gray background color to them okay and then I'm also going to change the Border I like adding a border to my visuals it makes it stand out a little bit so I'm going to add a border right here and by selecting the Border section you can turn on borders for every one of your visuals so it's going to put a border around your visuals that you can choose from or you can see now one property here that I particularly like is this radius property and what that will do is it will actually show you it'll it'll allow you to see like a rounded Edge to your border rather than like a sharp right angle like you traditionally see with borders you can actually make this kind of like a rounded Edge border if you add in some kind of pixel or radius here to your your um border that you're creating okay there's lots of other things that we could play around with and change and modify but I'm going to leave it like this I don't want to get too crazy and I'm simply going to hit apply down in the bottom okay so I'll hit apply when I hit apply you'll notice that the theme takes over and it replaces all of the settings that I've modified now with the changes that I have for my theme file so we're going to see the visuals now following the standards of having a border having a background color using primary color of this kind of orange color that I'm using and it just allows you to again the purpose of this is kind of standardize the look and feel for the report design that you've done that's kind of the main goal of creating the theme okay now once you create the theme you can then save it you can then share it with others and that way you can make sure that everyone in your organization is following the same standards that you've come up with okay so if I wanted to export the theme that I've just created I can do that by going up to the theme area again again this is going to be underneath the view the view menu up top so View and then we're going to expand the theme section right here and inside the theme section there is a button called save current theme right here at your way of exporting the theme so I would select this to export my theme it will then allow me to save it as a Json file and then I could use that Json file and hand it to other colleagues that I work with and then they would be able to use the same theme that I created so this is how you would export the theme and then one of my colleagues who takes the theme from me the file that I create they would select browse for theme to then import it so the three steps we have here we already did this one this was to create the theme so that was step one we created the theme then we can go export it and then when we hand the exported file to someone else they can then import it using the browse for theme section right there okay all right very cool all right so I I wish we had a ton more time to get deeper into the theme section that's unfortunately all we're going to have time for because I have a few other things I want to show you but the themes are great for creating this standardization across all of your reports that you design make sure that everyone's following the same standards that you have come up with or that someone at your organization has come up with to ensure there's there's there's uh uh wise choices made when it comes to the design palettes that are used here okay all right so very good now that we have done that now that we've created a theme one other really fun thing we could do is we could add in background images and we could add in different colors and we could add in titles to our report so one of the things that I actually have provided to you this is really one of the only things that you will get benefit from with inside of the class files that I provided is I have given you a background image so Manuel if you don't mind sharing the uh share the class files one more time in case someone did not get the class files from earlier the class files have inside of them an image that I've created that can be used as a background to our report and so with inside of those class files that I provided to you let me bring those over here with inside of those class files you'll find that there is a completed files folder which includes the example that I'm showing you right now there's also a completed theme and then there's a background image that I'm going to be showing you here for the next very little little miniature example that we're going to do so we're going to add a background image to our report so that way we can make this layout a little smoother a little nicer as we go through this next set of examples all right again that's in the class files Manuel has shared a link to those class files in the chat you can grab them there okay all right good deal so now what we're going to do is we're going to show you how to implement that background image so if I want to add a background I can do that by first make sure you have no visuals selected okay so I have no visuals selected right now okay and if I have no visual selected by the way I see Linda's question I'm sure to pop this open for a moment because it's a quick one to answer Linda asks how do you reset the theme back to the powerbeat to the power bi theme I would just simply come back up to the top here Linda and you can select the default theme right here so I would go back up to view expand themes and this is the default one that we started with that will send it back to how it was if you want to revert this back to where you started uh let me take myself off camera so you can actually see that but you'll see that option is right here that's the default theme you started off with and that'll revert you back by the way there's also an undo button in power bi you can go up to the top left you'll find there's an undo button and that will revert you back to what you had before as well it's a good question Linda just wanted to make sure I that was a quick one I glanced over and saw all right so background image I want to add a background to my report if I want to add a background image to my report I can do that by going over to the format pane right here now you do before you go over here you need to make sure you have no visuals selected make sure you don't have any visuals highlighted selected being used in any way so you'll notice right now I have nothing selected how do you know if you go to click on a visual you'll see those little anchor points around the edges tell you that you have a visual selected so I'm going to make sure I deselect and have nothing selected at the moment and if I go over to the format section with nothing selected right here if I click on the format button now I can actually add in a new background to my canvas so you'll see there's a section here called canvas background and if I expand that I have the ability to add in a background image right here and I'm going to go browse to the image that I was showing you just a few moments ago from the class files if you don't have the class files downloaded right now that's okay just use any image doesn't matter I just want you to see the fact that you can add in a background image to your report so I'm going to go ahead and browse to add a file and I'm going to navigate to the class files where I have an image called background image okay good question from Ingo I'll come to you here just a moment I'm going to go ahead and select background image and hit open now uh Ingo I'm going to come back to your question but so I don't forget it I'm going to pop it up on my screen here in just a moment actually let me start so I don't forget your question but you'll notice after I add that background image nothing appears yet and the reason why nothing is appearing is because this bad boy right here right now the transparency is set to 100 which means even if I have a background image it's going to be transparent at 100 which means you're not going to see it I don't know why that's the default setting I wish it wasn't but you can taper that down by moving that dial down to more like zero percent or maybe you make it something smaller you can kind of figure out what transparency makes sense but 100 transparent means you're not going to see it at all so hang keep that in mind you don't want it to be 100 transparent the other thing that I'm going to do is where it says image fit image fit right here it's set to normal right now I'm going to change it to fit my image to the the canvas and if you change this to fit instead of fill or instead of uh the default there notice now we have we're actually able to see the full image here when we change it to fit to screen rather than it just basically take over the whole interface here so using the fit option which let me show you how I did that one more time we went under the format section we went to Canvas background and we changed the image fit here from normal to fit and that made it so we can see the entire image previously the image was kind of being spilled over into an area we couldn't see okay all right so starting to come together looking pretty good so ingo's question in the chat was around what's the difference between wallpaper and background here what's the difference between these two all right so let me promote that question up here so you can see so the difference between those two is wallpaper is the continuing area beyond the canvas so if you look kind of where my arrow is way down here this white space here wallpaper continues on beyond the canvas base whereas background is only the area that you see in the middle of my screen right now so I use the background so you're seeing the background of my report change to be an image wallpaper even goes beyond the canvas into this white space area you see lower on as well so that's the difference there why would you use wallpaper versus background well you might have some circumstances where you you know whenever you're working with inside the tool you want to continue to see whatever image it is even Beyond it it's really going to only apply when you're in the desktop tool that's the primary area at least where you'll see it and you might actually you can see it in the web experience as well just depending on the resolution you have turned on so it's basically area Beyond where you're actually going to have visuals that's the wallpaper all right so we have a background image now that we've used and what we're eventually going to do is we're going to have some multiple Pages here we can add a nice title up on the top here so if I went to my insert menu for example I could insert a text box if I go underneath insert I could select text box and I could put a title up top of my report so I can go up to the insert menu and insert a text box and I can kind of shift that text box around and add some text to it so maybe we add in something like report oh I got my cap lock on I could add in something like report summary and I can make that bold and I can make it really look like a header to my report here if I wanted to and so that way we are able to add a little bit of nice aesthetic changes to our report so it's a little nicer here and I'm just making some aesthetic changes here but I can grab this now and move it up to the top you could even remove the Border in the background if you wanted to so it doesn't have that border around it like we did for our visuals but here I'm able to now kind of add some of the final touches here to our report okay so easy enough to do if you want to add in something like a text box you would simply go up to insert text box okay all right very good so the next thing I want to show is custom visuals so custom visuals are a fun part of power bi that allow you to extend beyond the standard visuals that are given to you over here so you have these visuals are provided to you by default but you can actually go explore other visuals that are available to you beyond the default ones here in fact there's there's more than 450 custom visuals that you can explore and we're going to show you one just to give you a little bit of a exposure to into custom visuals but custom visuals allow you again for the purpose uh the purpose of them is to extend beyond the basic visuals that are provided to you now custom visuals are provided and are available by going to a website called appsource app source is where all of the custom visuals can be found but you can also navigate to define the custom visuals by clicking on the little three dot menu found right here okay so if I click on that little three dot menu the more get more visuals menu you'll see that when I go to click on that little three dot menu right here there is our there are a couple options for how you can bring in additional visuals and we're going to choose the option right here called get more visuals now I will tell you some of you will be prevented from being able to do this your company could stop and prevent you from using custom visuals so if you do hit a roadblock here I don't want you to get too hung up on this one little demo just watch this one if you if you do do hit a roadblock it I don't want it to ruin the rest of your class but do know that some organizations block custom visuals for whatever reason companies have their reasons for doing that but you will be prompted to sign in to your power bi account to have access to the custom visuals and when you go to sign in power bi will know whether or not your organization has blocked you from Custom visuals so just a heads up on that all right so we're going to go ahead and select get more visuals right here and when I select get more visuals it's going to pop open on my screen in the middle of my screen where I can search through all of the custom visuals that are available to me and again there's more than 450 visuals you can choose from here there's lots of different visuals that you can find okay so what I'd like to do is I want to add access Visual right here and if you don't see it on your screen you could always search in the top right as well but we're going to add in the play access Visual found right here so I'm going to select the play access and Matt I see your question I happen to glance over and saw Matt's question Matt asks can background can the background image be part of the theme as well unfortunately not if you want to have a background image that's kind of consistently part of your solution you might want to look into something called Power bi templates it's a little bit different than themes themes don't support background images as of today though all right so here's what we're going to do I'm going to go ahead and select that I want to add this play access Visual right here and that will allow me to then uh see this report change over time so basically here's what the play access Visual does it allows you to animate your reports so you can build in animations and you can see how things change in your report over time so if you actually want to see year over year how your reports have changed or how your data sets your data set has changed year over year you can use something like the play access that allows you to click a play button and you'll actually see changes occurring over time so I'm going to go ahead and select the play or the add button here and you'll notice over on the right hand side that there is now a new visual that has been added right here the play access Visual is now available to me and I can start to use this visual inside of my report okay all right Nick has a good relevant question let me go ahead and promote Nick's question here for a moment Nick asked the question once you add a visual will it always be in your visualization area or will it need to be wrote reloaded uh do you need to reload the visual for different reports so great question Nick the default behavior of how this works is the custom visual that we added is only available in this power bi file that we're looking at right now by default so the default behavior is if I were to save this file close and go open another file I would not see that custom visual however you can change that and I see some good comments in the chat answering Nick's question as well but if I wanted to change that so that that visual is permanently available to me you can right click on the visual and you can select pin to visualizations Pane and so this is answering next question that you're seeing on my screen right now this would allow you to be able to make that visual permanent with inside of your power bi desktop so it's specific to you if you had other users they went to open up their power bi desktop it's not pinned for them but this does allow you to make it so that that visual is there for you always and then a follow-up to that is Marty's question so Marty asked uh how do you then let me put Marty's question up here how do you then uh remove a visual so if I go to pin a visual how do I later remove it well if I go to pin a visual which I just did if I pin that visual I can later remove it by right clicking on it and selecting unpin this Visual and you'll see it kind of goes back and forth below and above this little dotted line here that's how you add or remove a custom visual right click to pin it right click to unpin it if you don't want it to be permanently there okay good questions all right let me go ahead and unpin that question Perfect all right so good deal so now that we have added in the play access let's actually see it in action if I want to add the play access Visual to my report I can do that by going to select it over with inside of the visualizations pane right here so I'm going to select the play access Visual that we just added a few moments ago again make sure you have no other visuals selected when you do this if I have another visual selected when I add in the play access it's going to replace the visual that I'm trying to use rather than edit the visual uh so I what I want to do is I want a totally new visual I'm going to make sure that I click somewhere in the background somewhere in the white space so no visuals are added and then I'll go ahead and click the play access Visual with the play access Visual selected I'm going to bring this over and shift it into place and you can really shape it or size it however you want that's up to you but now with inside of this new play access Visual I'm going to bring into it my year column from the calendar table so what we're going to do is from the calendar table right here we're going to bring in the year column and drag and drop that into the field list for the play access and this will allow me to actually see how things changed year by year with the number of failed banks that we have okay all right so if I go ahead and select year now notice what happens so now I can see there's this nice little visual in the bottom right hand corner for me I can hit play on that and now no you might sometimes by the way you have to hit it twice so I'm gonna hit it a second time here now when I hit a second time it'll actually show me how things have changed over time with inside of my report so in the early 2000s there wasn't a whole lot of different uh banks that were failing but then you can see kind of 2009 2010 2011 range that's when there was a lot of failed banks that were occurring and you see a big influx in the number of failed banks that appear in that range and so if you pay close attention let me go ahead and play that again you'll see the big influx occur around that 2009-2010 range is where the most banks really started to appear with inside of our visuals there we go right there you can see 2009 you can see it highlight is show the highlighted value is showing us there's 25 failed banks of the total 93 occurred in the year 2009. so a really really neat visual it allows you to build in animations into your report it allows you to build more interactive report visuals by using things like this play access Visual and the play access Visual is just one of hundreds uh like I mentioned there's more than 450 custom visuals that are available to you this is just one there's lots of other ones that are worth exploring as well okay all right so we got about 15 more minutes so I have a couple things I want to show you I'm going to tell you this this next thing I'm going to show you I'm going to do too fast for you to follow along with at least to follow along with me live so I'm telling you that because this is a recording you can go back and you can slow me down but I really want you to see this next feature it's really cool I think you'll it has a wow factor to it that I want you to see uh but I'm noticing we're getting low on time here so I'm telling you that just to let you know that you can re-watch this this is recorded you can go back and Rewind me and watch this next section again because I'm going to have to do it a little fast for the time we have left so the next thing that I'm going to show you that's going to be fast is called report page tooltips report page tool tips are a feature that replaces the standard toolkit you see right here okay report page tool tips can replace this little feature that you're seeing this is what's known as a tool tip right here okay a tooltip is basically a hover over whenever you go to hover over data a data point it will show you the value that's represented by that data point and what a report page tooltip does is it allows you to replace that gray box that we saw pop up right here with a box of your own design you can replace it entirely with something totally different and the way that you do it is you you create your own your own page now we haven't really talked about this yet but down on the bottom here you can actually have multiple pages of reports that you can add right now we're looking at page one but you can actually have more than one page to your report by hitting the little plus sign here it looks very much like adding another Excel spreadsheet right so what we're going to do is we're going to actually add in another page you'll see we have a new blank canvas here now when we do that we can always toggle back to page one so here's page one here's page two by the way you can also rename these Pages if you wanted to you can rename them by double clicking on the page name here and you can give them a new name like so if I can call this my toolkit page and you can have multiple names to your multiple pages and they can have different names for each one of them here okay all right so what we're going to do is now that we have created a second page we're going to make this new page turn into a tool tip okay and the way that we do that is we're going to go over to the format section and underneath Page information again I'm going to be doing this a little fast so you can always rewind me and watch this again later but underneath the format section we're going to turn on allow use as tooltip and so I'm going to go ahead and turn that on notice it resizes the canvas to something far smaller that's more of an appropriate tooltip size there and then what I'm going to do is I'm going to add some visuals to it so I'm going to add something like a card visual this would be the card visual right here I'm going to add a card visual to this report and I'm going to put in a measure for total Banks so show me the total number of banks with inside of this little pop-up we're trying to create I'm also going to add in a map and inside the map okay inside the map that I'm going to do I'm going to bring in the total number of banks by city state okay so the location is going to be city state here's what we did we brought in city state into the location and Bank name into the bubble size here now one thing that's worth noting Maps do have some security features that could be turned off for you so if you see an error on top of your map it could be one of two things there is a setting underneath the file and options menu with inside of power bi where you can turn off a security setting that will allow maps to work Manuel may be able to go kind of give you a little bit of path of how to get to that that would take me a few moments to show but there is an option to turn off the security setting to allow maps to work that's something you can turn off in the power bi desktop if this isn't working for you for some reason the other thing that you may consider doing is it could be something that your organization has turned off at the tenant level so it may be something that your administrator needs to help you with as well so there's two things to check if the map doesn't work for you one is it could be a setting in your power bi desktop the other is it could be something that your tenant admin has turned off for your whole company but what I'm going to do is with my map added mine is working I'm going to add into my map the city state column which you actually I already did add it in but you'll notice that none of my city-states are actually mapping it's not actually showing any of anything on my map at the moment so what I need to do there's a there's a special feature that I need to turn on it's called Data categories and by flipping on a data category for the city state column it's going to let power bi know that we're talking about a particular location right now when we pass in our city-state column it doesn't recognize it as a geographic field and so we're going to need to tell power bi that city state is actually a geographic field that we want to be able to show on our map so the way that we're going to do that is we're going to first start by selecting the city-state column just click on the column name not the check box and with the column selected we're then going to go up to column tools like we did earlier so column tools right here and you'll see there's a property here called Data category that's set to uncategorize but we're going to change this to place the reason we're not selecting city is because remember our city state column actually has the city and state in it and so City wouldn't be recognized properly here so we're going to use the place value and by selecting the place value Watch What Happens in our map look at that now all of the data points are finally now being mapped inside of our report visual because we have told power bi that city-state is actually a geographic field okay all right good deal so now that we've done that there's one final step to make this work and the one final step that we need to do is there is a property on the report page right here called tooltip and we need to bring a field into the tooltip section that lets power bi know how to filter this tooltip down now the column that we're going to be using for our example is going to be the state column because what we want to do is we want to make it so that anytime someone goes to build a report visual if it's using the state column we want this tooltip to pop up so we're going to drag and drop the state column into the tooltip section by simply dragging that into the tooltip area and again what that means is any visual that uses the state column will have access to this tooltip and it will filter to only show the state that you happen to be hovering over so this is a little bit more of an advanced feature for sure but I think it's a wow factor thing I really want you to see so let's go ahead and drag in the state column into the tooltip area and we're done so I know I did that pretty fast but let's see the end result of what we just did yeah it does kind of act like a drill through uh for that question that came in the chat it's kind of like a drill through at least how you set it up but it's done a little differently the the behavior is a little different so let's go back over to our report summary now our original report and watch what happens now when we go to hover above State now when I go to hover above State you'll see it pops up not that gray box we saw earlier now we're actually seeing a number our tooltip we created with inside of it and a map so we actually do see a map showing us each of the states where those failed Banks were occurring now with inside of our tooltip we created so this is called report page tool tips it's a really neat feature and it delivers a little bit of a wow factor where you can see that you can go beyond the basic gray box that we saw here so this was what we saw before right we saw this gray box now we're seeing this much more responsive uh report page tool tip that gives you a much neater view now it's a little bit more of an advanced feature but again don't forget this is recorded go back and watch this video again this portion of the video again to see all the little clicks that I did to make that happen Okay I want you to see that one I'm seeing a lot of great responses in the chat because it is really a neat feature so I wanted to make sure you could at least see the the punch line even if I had to do it a little fast because it is such a really really cool feature all right very good so we're down to our last few minutes here and what we're going to be doing in our last couple minutes is I want to show you what do you do with this next what's the next steps that we would do uh one of the things I wanted to show you with a little bit more time and maybe I'll give you a little peek at it is you can also build in navigation into your reports so if you wanted to build in let's say for example buttons you'll notice I left a lot of space here on the left hand side because I wanted to build in the capability to have buttons that your users could click on to go from report to report very briefly let me show you how that could be done if I wanted to build in navigation into my reports for my users then I can go up to the insert menu and I'll zoom in on this here for you in a moment but under the insert menu right here this is definitely worth exploring later on your own underneath the button section here you'll see there's an option here called Navigator and there you can build in buttons for your users to click so it will allow them to hop from page to page so if I were to actually do this on my screen I don't really have very much for them to hop back and forth between in fact you'll see it really only creates one button for me but I could take that button and I can move it over here on the right hand side now it will automatically create a button for you for every page that you have now the reason you're only seeing one button is because power bi is smart enough to realize you probably don't want to have a button for the tooltip you don't want users to go to that tooltip page but if I were to add another page here check this out I added another page now I have another button so it'll create as many buttons as you have Pages unless they're tool tips it actually hides the tooltip pages for you automatically and if you wanted to change the orientation of this you can so I can actually change this so that it is uh rotated let's see it is style here it is there's a but there's a way that you can actually change this here here we go vertical so now I can kind of have buttons on the left hand side going down the left hand side where I'm building this navigation and it allows you to kind of toggle back and forth between all of the pages you have on your report so by adding in that was again under the insert menu an insert button Navigator you can select the page Navigator and it will automatically create a button for you for every page you have on your report allowing your users to very easily navigate between each of the pages you have so that's a great strategy for you know building in that almost like a website look and feel right So Daniel asked a good question Daniel asked how is this different than bookmarks so this actually doesn't rely on bookmarks at all what I'm showing now with the the button option it's almost doing what you would have in the past manually created with bookmarks so bookmarks can do the same thing this just makes it a lot easier so you don't have to build bookmarks on your own it actually just allows you to kind of take care of um it allows you to build in the navigation without without you having to manually create the bookmarks so they solve the power bi team saw so many people were doing the same thing with building out navigation that they went ahead and built that into the product itself all right very good good question all right the final thing the last few minutes we have here that I want to show the final step here that I want to get across to you is what do you do with this next and we're definitely down to the wire here on time we got about five minutes left uh but the final thing I want to show you is what do I do with the solution once I'm done and what you're going to do is you're going to publish and share your work with others and this will definitely be a micro session here but under your Home tab so you're going to go to home first and under the Home tab with inside the power bi desktop you'll find a publish button right here by selecting publish this will allow you to share your work with others by publishing to the power bi service so if I select and click on the publish button it's going to prompt me first to save my work I have yet to save my work so it's going to ask me to save my work locally so I'll save that and I'll call this my learn with the Nerds live session here and hit save and so I've saved my work locally and now I can publish to the power bi service this is the cloud part of power bi here okay now each of you are likely going to have different workspaces you can publish to so a workspace is basically like a folder that you publish your power bi content to but what everyone should have assuming you have a power bi account is everyone will have a my workspace and your my workspace is where you can kind of do like your own personal testing and you can you know validate that the things are gonna look the way they should in the web browser that they do elsewhere but your my workspace is kind of like for your own personal testing when you want to really publish to somewhere that you will share with others you'll likely have another workspace you create so I think I have one here called learn by the Nerds I'm going to go ahead and select learn where the Nerds is my workspace and it's going to publish now to the power bi service which is going to be found from within side of the web browser experience so with a little bit of time we have left I'm going to click on open learn with the Nerds alive inside of the power the power bi service which is going to open in my web browser experience and you'll see here this is the same experience that we had in the desktop tool now appearing with inside of the web browser experience okay so this allows you to now see the same report that we did in the desktop tool now available for inside of the power bi service and you're able to interact with it just like you could from within side of the desktop tool but now you're inside of the web experience now there's loads more to talk about here as far as how do you share your work with others how do you schedule data refreshes there's lots more here to learn about that for unfortunately we're just out of time uh to go any deeper into but there's lots of sharing options that you can do in fact you'll even see that you can share from the report directly right here uh there's other ways of sharing where you can share to a workspace or you can share to a power bi app what I would recommend you may want to take a peek at is Manuel who's joined us today he actually did a webinar on power bi Administration that was another three hour one where he went pretty in depth into a lot of the topics that we have on my screen right now and I will put if if he doesn't have that handy I'll put it in the chat for you uh as well so that way if you want to learn more about the administration side of things with power bi I'm going to put a link to his length year webinar uh three hour webinar on Administration I'm gonna put that in the chat for you so that way you can go back and learn more about power bi Administration all right very good so the final thing I want to mention for you again this is where you would go next you would share your work here there's lots more to learn of what to do next watch that three hour link I put in the chat just now around power bi Administration the way I want to wrap up with you though is I do want to let you know that first of all we do have another event coming up I'm sure my marketing team folks will add this into the chat we have a another lunch with the Nerds which is our shorter sessions is coming up on let me go pull it up on my screen here we have another lunch with the Nerds that's coming up on March 8th so if you look at our YouTube channel on March 8th with Allison Gonzalez we have a lunch with the Nerds which means it's a shorter session it's an hour and a half and it's going to be coming up on March 8th at 11 A.M okay that's going to be on data storytelling with power bi so she's going to focus even more on some of the storytelling features that I wish we had more time to cover today she's going to really dig into that on that hour and a half session she's also going to talk about some methodologies around data storytelling she is actually has a design background so she really has a lot of expertise around designing your reports so uh my team has it looks like has already shared in the chat a link to where you can sign up for that so make sure that you take a moment and sign up for the lunch with the nerd session coming up on March 8th uh we'll also be sending out some follow-up information if you have questions again about the boot camps I'll put the the information about the boot camp sale that we have going on up on the screen here again but other than that thank you so much everyone for joining hopefully you enjoyed today's session don't forget it was recorded you can go back and re-watch certain portions of it if there's something that you missed something that you want to go back and see again take a moment and go ahead and Rewind me and watch it again thanks everybody have a great rest of your week and thanks for joining today take care

Channel: Pragmatic Works

Views: 143,931

Keywords: power bi tutorial, power bi tutorial for beginners, power bi dashboard, power bi training, power bi, power bi reports, power bi visualization, learn power bi, power bi desktop, power bi for beginners, power bi full course, devin knight, power bi beginner to pro, intro to power bi, power bi beginner, how to use power bi, power bi demo, introduction to power bi, power bi data, dax basics, dax power bi, how to use microsoft power bi, powerbi, power bi desktop tutorial

Id: 77jIzgvCIYY

Length: 182min 18sec (10938 seconds)

Published: Thu Feb 09 2023