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

[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 have 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. Uh, 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. 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 gonna be answering a lot of the questions through the chat as we work our way through today's class. Man, well, 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 7c delay, Dev, so we're good to go.

Perfect. Well, thank you, Manuel. Well, yeah, as I said, this is 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 maybe totally brand new to it, that you'd be able to pick up a few things throughout the day, and maybe even those that uh have been working with Power BI for some time might be got 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. Uh, 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 need is you will absolutely need the Power BI Desktop. Power BI Desktop application is kind of the companion app that goes with Power BI that allows you to design solutions that you'll eventually publish to the cloud uh 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 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 uh 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 few little nuggets there, uh, but you can find those class files at the link that I have in the uh 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 uh a recorded class. 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 all 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 open 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 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 going to 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 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 uh either through links that are being shared in the chat from my colleagues uh or you can also, of course, use a search engine and search for the Power BI Desktop download. Uh, 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.

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. Uh, 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 uh 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 open in the middle of your screen, letting you know which version of the Power BI Desktop you're running. It actually it looks like I'm running a slightly old version myself. There is an 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 uh, 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 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 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. Your going to go connect to 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 sorry, I said right, I mean left, uh 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 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 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 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 those today, even. We 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 spelling 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 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 uh the State of Florida, where I'm from, and then I can see all the cities with inside Florida, 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 uh data. So hierarchies are a popular part of um 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 a and type this out, expression link. 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 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 into 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 going to 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. Uh, we'll explore a few of those things today. Uh, you may also do things like uh 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 being very brief, mainly because the uh 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 to investing time and building a proper data model. And again, we'll share some links to some other uh 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 within 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 the Microsoft 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, uh, 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 row-level 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 within 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. Uh, 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 continuous 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 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, uh, 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 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 within 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, uh, file that, uh, we're going to be pulling from a website. The reason I shared the file—the data source—within 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 within 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. All right, now 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—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 what I—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, 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 probably 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 within Power BI. And my colleagues that are—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 selected 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 a 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 to 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? Let's see, there's about a seven-to-ten-second delay, but let's see if anybody can guess where's the location that I should go. There we go. Now we're getting some answers in there. Thank you, Yvon, uh, 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 at 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, uh, 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 Web, 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, uh, 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—if you're struggling to find where did I get this from, there is a link in the, uh, chat, maybe Minh—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 Okay right here. And clicking Okay, 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. Okay, it looks like Menwell 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 Okay, 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? Uh, 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, Antonio, I'm going to 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 website—in 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. Um, 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 in 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—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 there—good question; thank you for that. All right, let's go ahead and click Okay, 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, uh, 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 on the bottom. All right, so there's three buttons on the bottom here; 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 will—it will immediately load the results of your data into the Power BI data model. Okay, so the data model within 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 data cleansing steps. Okay, so the option here—the idea with the Transform Data button—is it will allow me to then, uh, 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 within 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, uh, Manuel has kind of promoted that up; we are talking about that now. So Load would be—I—so let's—let's—let's word it this way: I would choose Load under pretty rare circumstances; generally, I would choose Load is, uh, 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—it's already kind of perfected; that's already gone through 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 within 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, uh, a little bit of time to be able to pull this in. 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 within 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 us to perform the data cleansing steps that we need. Okay, good question. Uh, that just popped up in the chat as well, again from Antonio. Let me go ahead and pop that up. Uh, his question was, if you load, can you transform later, or do you have to start over? Great question. If you had chosen 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 the Transform Data editor open, also known as Power Query Editor, 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 dialogue 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 CT 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 dialogue box, you'll click Okay. All right, so uncheck these three and then click Okay on the bottom.

All right, so now that you've done that, you'll go ahead and select Okay, 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 Banks, something like that, with a space. Spaces are okay here. Uh, by the way, one thing worth noting here, spaces in 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 Irena's question here. Irena 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 remove 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 Step 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 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, to the question from Marina—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, let me hide—let me unzoom for a moment—and hide that question. There we go. All right, so the Applied Step section is going to become your friend. You'll find a lot of times you'll go revisit the Applied Step section when 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 uh 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 often times 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 in the United States. There's there's uh a dozen or more uh Jacksonville 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 often times there's more than one way to accomplish this—to solve this problem. I could select both of the columns, I can click on the column names up top here and multi-select them by holding control, and then I could 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 end—you want—what you want the end result to look like. Stumbling over 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 and 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. 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, 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 appear 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 in 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 intelligence 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 Almena, comma, with a space, Kansas—the abbreviation for Kansas—and 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 Almena—let me spell it right—spelling does matter here, by the way, so if you misspell it, it won't work—but I typed in Almena, 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 State, something like that. So I can give the new column 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 Okay once we're done with this. Now you can go ahead and click Okay. 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 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 to 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 M by itself very much; you're probably using this user interface to do a lot of the M code for you, uh, 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 that cleared. Uh uh, oh, Manuel, can you figure a hi 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. I'm not going to go too deep into it today, but I will show you where to go. Uh, I want to make sure we kind of keep on pace where we want to be. Uh, so Antonio, I'm gonna 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 Okay button here, and that will finalize the column that I've created. Okay, so I'm going to go ahead and click Okay, and that will create that new CityState 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—be 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—but 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 with me—let's—let's play a game here. I want to know which state—and this is all US data, by the way—US state, which US state is going to have the most failed Banks? Answer in the chat. Let me know which one you think—which US 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—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 got lots of good guesses that's continuing to come through on which state people…

Had or think had the most failed banks? So here's what I want to do. If I want to see which state has the most failed banks, I need to load this data into my data model so I can actually build a visual on it.

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. When 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 closes 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 banks. 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—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 stacked bar chart right here, and if you're following along, you can go ahead and select the stacked 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 stacked 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 stacked bar chart that I'm pointing at right now. All right, so I'm going to go ahead and choose that and select that 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 uh 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. Now, sounds like lots of you have already done this, you're a little ahead of me, no problem. We're going to go 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 Axis 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. Uh, 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 drag in 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 drag 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 what we just did. Now, you'll notice when I dragged in the Bank Name to 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 a count and a count 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 can 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 a count 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 the leader. I don't know if they should be proud about this one, but Georgia. Then 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 round up the top five or six there. So it kind of shows you here what 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 this 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 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 hour 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 going to share a link to a three-hour class very similar to like what we're doing today around data modeling, and it was done what by one of my colleagues and 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 going to have so much time to cover it today, I want to make sure you have that three-hour resource, and Manuel is also shared a secondary link in there to a 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 two videos that were shared in the chat, one is uh by Mitchell, that's the three-hour one; one's 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. Uh, now we're at a good point for a question. I see Matt, uh, uh Manuel is 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, 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 Matt's 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 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 of Banks, I can do that by going over to my Fields pane. Here's your Fields pane right here, right, and then from with inside of your Fields pane, you can go down to where you see Count of Bank Name underneath the X axis, and I saw some people saw they they noticed that their X and Y axis was flipped; just make sure you put them in the right slot, right? Put the State in the Y axis and the Count of Bank Name in the X axis. But if I wanted to rename this instead of showing Count of 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 of 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 of Bank. So much better—a better way, more user-friendly, as Matt's question indicated there, more user-friendly way to be able to uh display and and show uh 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 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 dates 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 you in the United States and and 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 that 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 fiscal 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 often times different than a traditional calendar. For example, Microsoft—small little company known as Microsoft, right? Kidding, of course, they're huge—but Microsoft uses a fiscal calendar that begins in July. Okay. So what that means is if you ask Microsoft, 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 would 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; 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 crucial if you are trying to design DAX time intelligence. Okay, so let's spell that—that's always fun when you misspell "intelligence"—uh, but DAX time intelligence, time intelligence is within the uh, data analysis expression language; that's what DAX is, again as a reminder. But using the time intelligence functions within DAX allows you 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 are some time intelligence functions that you can do without a date table, but there are some others that are really beneficial if you 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-date, or maybe I want to look at 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 need to have a date table—let's answer the question of how do you create a date table? How do I get a date table? How do I get one? Okay, so there are a couple of different ways you can get a date table. One, you can import a date table from your data source. So if you're connecting into a data warehouse, for example, by connecting into a data warehouse, your data warehouse likely already has a date table, and you would simply connect to that date table and use it within your model. So step number one there, or option number one, would be: I have a date table in my database, 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 within 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 the other options are. Manuel, well, if you can get handy, there should be a URL to a date table script; 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 date 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. The—I really want to get you a little bit of a peek into what DAX is because it's so much more commonly needed than the InQuery option. The writing InQueries 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 uh, 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 he 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 date 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 we're going to be exposed to 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 Power Query; that's fine. Uh, you can do it with DAX, 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 in the chat, and unfortunately because I'm in my drawing mode here, I won't be able to uh, 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, Manuel, for promoting that. Uh, the reason why I prefer to create my own date table as opposed to using the auto one is for some of these top reasons we mentioned here. Uh, 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 date 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, uh, 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 a screenshot of these notes, now is a great time to take it because we are going to be moving on into our next section here, uh, in 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 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 sqlbi; 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 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, 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, 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 DAX. 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 within 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 within 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 and 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 of 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 DAX; 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 uh, actually uh, provide the definition of it. Okay, all right.

So here's what we're going to do: 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 gonna 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 CALENDARAUTO. Okay. Now the CALENDARAUTO function, which you can see on my screen right here, what it does is it scans all of the date columns you have within your data, your data model. So it's going to look at any date columns I have within my data model, and what it will do is it will create this continuous list of dates—continuous meaning there are no breaks, there are 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 within 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 within all my date values. So CALENDARAUTO automatically generates this list of date values inside of this new table. Okay, and I see a question: why are we creating a date table? You may want to rewind the video a little bit; we did some whiteboarding where we talked about the why; you can go back a little bit, and we answer that question of why we're doing this. All right.

So I'm going to go ahead and select CALENDARAUTO, 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 DAX. 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 uh, 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, try again. All right. But we're going to go ahead and type in CALENDARAUTO and I can actually click on it right here if I wanted to, and underneath CALENDARAUTO this would allow me to be able to return back that special CALENDARAUTO 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 are special capabilities with CALENDARAUTO if you're dealing with a fiscal calendar. Remember we talked about fiscal calendars earlier; that 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 CALENDARAUTO function. Now we're not going to be taking advantage of that capability today, but I did want you to know that there are 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 CALENDARAUTO and do an open and close parenthesis, 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 CALENDARAUTO function to return back a list of all of the date values that I could use within my data set. Okay. Now again, all CALENDARAUTO 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 uh, 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 within this new column that got created for us right here. So you can see this new column called "Date" got created after we use that CALENDARAUTO function. Okay, all right. Very good. Uh, question in the chat: what if we need two more years to do some forecasting or something like that? So uh, CALENDARAUTO 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 need beyond just the current years that are appearing whenever I use CALENDARAUTO? In that case, you might want to use a different function called CALENDAR, but not CALENDARAUTO, 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 uh, 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 calculator icon on it; that tells you that little calendar uh, calculator icon indicates that it is a calculated table, so it indicates to you that it is a 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, in 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 uh, a year column. I want to have a column that parses out the year from the date. 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, 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 "Year," and then to the right of the equal sign we're going to uh, 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 at an hour and a half, which 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 within Power BI, and you'll notice there are lots of other functions in Power BI that leverage year capabilities like SAMEPERIODLASTYEAR or PREVIOUSYEAR or STARTOFYEAR; 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 into 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 within Power BI. So if I use the square bracket—getting a little over-enthusiastic there with my brackets—if I use the square bracket, you'll notice Intellisense immediately tries to help me out, and it lists all of the column names that I have within this table that I 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 continues 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. 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. In a 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 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 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. 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 used 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 uh, 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 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 and then 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. Uh, 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 uh, a new function using the a new column created using the format function. Okay. All right, so we're going to go ahead and 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, and using those four lowercase M, 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 M, and it'll bring a month abbreviation. I could optionally do three lowercase Ms 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, year column, month 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, 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 going to keep moving forward. We 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. e e e e e e e [Music] n [Music] [Music] [Music] [Applause] [Music] [Music] e e e e

All right everyone, we are ready to come back. I'm going to go ahead and 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 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, 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. But 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 DAX, so if you really are eager to learn more about DAX, I would certainly recommend our DAX boot camp. And then we also have uh, a Paginated 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, Paginated Data Reports is part of Power BI. Uh, 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, Learned With The Nerds 250, and that will get you $250 off 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. Uh, so they are virtually generally taught in Eastern Time Zone, uh, but 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 go beyond the basics of what we can do in a three-hour session or even a one-day session, and check check out what we have there and see if that might be a good fit for you. Uh, 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 as 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 uh, I'm sure our team will be happy to answer any kind of questions around the sale in the chat, so that's available for your uh, for 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 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 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 uh results of my date 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 visuals 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 Stacked column chart for this example. All right, so I'm going to go ahead and select stacked column chart, and that will bring the stack column chart on our design canvas here, and 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 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 to banks by state, this now should return back the count to banks by uh by month. Okay. All right. 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 month, y-axis has count to 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, and Jackie is 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 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 uh, 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 uh, how 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 in the date the calendar table, and you have a closing date column in the fail 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 date on top of closing date right here, click and drag one on top of the other. Now a common question that we get will can is can you drag closing date on top of Date? 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 the 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? Uh, 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 fail banks by year, you're allowed to do filters going in this direction only, based on on how the relationships are designed right now; that's what this little uh 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 it, 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 fail 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 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-to-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 the one side of the relationship, that means that the date column is unique, meaning you're never going to have the same dat—date appear more than once within the calendar table. However, closing date is not unique, because—and it kind of makes sense, right? You can think about it: if I'm allowed to have more than one bank close on the same date, so it's not possible that my Failed Banks 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-to-many means. It means that you have a unique set of values on one side of the relationship and not a unique 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-many, 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, 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—well, I 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 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 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: how do I fix the sort order? Now, the sort order problem is really kind of a—it's both a 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 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 axis 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 axis, 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 axis, and choose Sort ascending. Okay. So those are 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 great—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 or 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. 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. Uh, I'm trying to see—I think I actually answered a bunch of these questions that uh you started, 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 within—within our date table, right? Within 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. And 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 date table, we created a year column, we created a month column or 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 it 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, a standard deviation, count, 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 they're metric values; you're 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 in 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—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—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 within—within my Failed Banks table. So within—within 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 going to 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—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 Banks, 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 COUNTROWS. 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 COUNTROWS, which allows us to count all of the rows within—within 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 in our Failed Banks table, like so. And that's it; that creates this COUNTROWS function for us. If I want to—or if I want to—I'll—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 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—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 pretty—pretty nominal, uh, but 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 within—within 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 of 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 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 and 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 visual 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 font or 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 selection 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 within 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 label section, I can modify and say, hey, instead of seeing those data labels um 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. Uh, 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 within 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 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 have a set standard color scheme, a set standard font type. I wanted 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 is 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 within a theme. And the where you're going to go—and I think this is going to answer Antonio's question—Antonio, you you got like the hot hot mic here on questions—uh, Antonio's question is, can you import color schemes? And yes, uh, you can. You can actually; we'll we'll give you a little peek at that here in a few moments as we discuss themes. Uh, 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. 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 what 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 theme 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. Uh, there are some—for accessibility reasons—that have been added; there's ones like color-blind-safe themes, which are kind of nice, and then there's some other themes that are are kind of interesting. Uh, maybe you go with the 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 on the bottom. Again, this is uh 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 gonna 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 within a JSON file. 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? Uh, 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, as 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 theme, 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 within 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 within my theme. Now you could change the other colors here—color two, color three, four, five, six, seven, eight—you could change those 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 a 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 could 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 sake, I'm going to go ahead and select Arial for our font family. All right, then I'm going to go down to the visual 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 to 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 at 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 modified now with the changes that I have from 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; that's 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. Okay, so the three steps we have here: we already did this one; this was to create the theme; 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 a 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 can add in background images, and we could add in different colors; we could add 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 within 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 within those class files that I provided to you—let me bring those over here—within 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 visual selected. Okay, so I have no visual selected right now. Okay, and if I have no visual selected—and by the way, I see Linda's question; I'm just going 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 Power 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; I 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 uh 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, uh, good question from Ingo; I'll come to you here in 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 it so I don't forget your question, uh, but um 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 0% 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 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 when we change it to fit to screen, rather than it just basically taking 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 space, whereas background is only the area that you see in the middle of my screen right now. So I used a 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 uh, that's the difference there. Uh, 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; um, that's the primary area at least where you'll see it. You might actually, you could 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. I go underneath insert, I could select textbox, and I could put a title up, 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—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, 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 uh 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 give you a little bit of a exposure into custom visuals, but custom visuals allow you again for the purpose, 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. Appsource is where all of the custom visuals can be found, but you can also navigate to find 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 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 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, uh, 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 in the PlayAxis 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 PlayAxis Visual found right here. So I'm going to select the PlayAxis, and Matt, I see your question, happen to glance over and saw Matt's question. Matt asked, can background, can the background image be part of the theme as well? Unfortunately not. Uh, 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 PlayAxis Visual right here, and that will allow me to then uh see this report change over time. So basically, here's what the PlayAxis Visual does: it allows you to animate your report, 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 PlayAxis 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 PlayAxis Visual is now available to me, and I can start to use this visual inside of my report. Okay, all right. Uh, 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 your visualization area, or will it need to be 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 Nick's question that you're seeing on my screen right now. This would allow you to be able to make that visual permanent within side of your Power BI desktop, so it's specific to you. Uh, 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 and 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 PlayAxis, let's actually see it in action. If I want to add the PlayAxis Visual to my report, I can do that by going to select it over within side of the visualizations pane right here. So I'm going to select the PlayAxis visual that we just added a few moments ago. Again, make sure you have no other visual selected when you do this. If I have another visual selected when I add in the PlayAxis, 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 PlayAxis Visual. With the PlayAxis Visual selected, I'm going to bring this over and shift it in into place, and you can really shape it or size it however you want; that's up to you. But now within side of this new PlayAxis 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 PlayAxis, and this will allow me to actually see how things change 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 happened. 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 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 within side 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 fail 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 within side 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 PlayAxis Visual, and the PlayAxis 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 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, 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 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 rewatch 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 uh for the time we have left. So the next thing that I'm going to show you that that's going to be fast is called report page tooltips. Report page tooltips are a feature that replaces the standard tooltip you see right here. Okay, report page tooltips can replace this little feature that you're seeing; this is what's known as a tooltip right here. Okay, a tooltip is basically a hover over whenever you got 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, 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. I can call this my tooltip 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 tooltip. 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 within side of this little popup 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 within side of Power BI where you can turn off a security setting that will allow maps to work. Uh, 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. Uh, 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 anything on my map at the moment. So what I'm 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 checkbox, 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 uncategorized, 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 tool tip, 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 tool tip 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, does kind of act like a a drill through. Uh, see 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 tool tip we created with inside of it, and a map. So we actually do see a map showing us each of the state where those failed Banks were occurring, now with inside of our tool tip we've 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 punchline, even if I had to do it a little fast, uh because it is such a really, 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 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 tool tip; you don't want users to go to that tool tip 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 tool tip 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 button—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-looking 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. The 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; uh, it actually just allows you to kind of take care of um uh it allows you to build in the navigation without without you having to manually create the bookmarks. So they they saw—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 going to 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 with the Nerds; I'm going to go ahead and select Learn with the Nerds as my workspace, and it's going to publish now to the Power BI service, which is going to be found from with inside 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 live 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 inside of the Power BI service, and you're able to interact with it just like you could from with inside 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. Uh, what I would recommend you may want to take a peek at is Menwell, 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 lengthier webinar, three-hour webinar on Administration, I'm going to 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 uh first of all we do have another event coming up; I'm sure my 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; 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 Nerds 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 rewatch 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.