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

hey everyone we are excited to have you join us here shortly uh we're going to get started in about one minute just doing a quick audio check here look forward to having everyone here with us we will have a few folks in the chat to answer questions as they come up but we're going to go ahead and after we do this quick audio check i'm going to put a timer up here so you know when we will get started we'll get started in about one minute so [Music] well welcome everyone to our learn with the nerds event titled power bi beginner to pro this is an exciting day we have quite a bit of material that we're going to be going over today uh we are going to be starting at a beginner level for some so for those of you that are brand new to power bi and joining us and learning about power bi for the first time hopefully you will get a lot of great information out of today if you've been working with power bi for some time we'll also have some sections geared towards you to kind of lean you up into that pro part of the title there so we'll have quite a bit of different elements of what we discussed with inside power bi for today's class my name is devin knight i am the president of training at pragmatic work so we do a lot of these free events but we also do and have a set of paid training that we do as well that we'd love to share with you about of course at some point but before we do that let's of course jump into our plan for today so for today we obviously have quite a bit to cover in the three hour period to make you a uh hopefully a pro with power bi in that short amount of time we have a lot that we're going to cover and and really we're going to really jump right into it i'm not going to show you a ton of slides i have maybe two slides to kind of get you going and have a brief understanding of how the tool works but our goal is to really jump rather quickly into developing a solution with power bi and guess what you get to do it with us i have a couple folks from my team that are monitoring the chat that's matt and julia and they'll be able to assist with questions they obviously we're planning to have a very large group today so they're going to be rapidly helping folks out with questions that you may have and one thing that i always like to make sure i know when we do things like youtube streams like this you can rewind me you don't have to ask can i see that again use the youtube player and you can push back almost like you're pushing back a recording device that you have at home you can push back my my video and watch something that i did as many times as you want so no need to ask can you show that again because you can watch it yourself again and you can kind of catch back up as we do breaks we will have a couple breaks by the way uh during this three hour period so again our goal for today though is we want to get you a pretty good understanding of power bi you are going to be going you are going to be able to follow along i'm going to go at a pace that you can follow along but again if i do go too fast feel free to rewind and so what you'll need for today is you will need the power bi desktop the power bi desktop is the main application that you'll use for developing power bi solutions that's what i'm going to be using on my screen you will need that on yours as well to be able to follow along now if you have not installed that even to this point right now you still have time to do it and my folks matt and julia will obviously post that those links in the chat and you'll also i think see it in the description but that will allow you to be able to follow along you don't need to have any special files downloaded for today because what we're going to be doing is we're going to be using a web source as our data source for today's example so we're going to be connecting into a data.gov where we can find a government data source that we can use and for some of you if you've seen some of our demonstrations before you maybe actually have seen this example before it might not be new to you but we're going to kind of get an idea of how we can connect and work together on building this solution and really get a hopefully a good end-to-end picture of how power bi works in today's session and along the way i want to try and drop in nice little nuggets of i'd say more advanced material for those of you that are already been in that beginner realm and you're ready to move on to more of that pro side of things i'm going to make sure i highlight a few more advanced features as we go throughout the day so briefly i do have a few slides here i'm going to bring those up here now to kind of get us started so let me share my screen properly here there we go all right so real quickly what i'd like to do is kind of put in the context of uh you know obviously we're focused on power bi here today but power bi is one part of a platform of tools known as the microsoft power platform so if you're not familiar with the power platform it might be because you've been very focused on one of these tools individually whether that be power apps or power automate or for many of you that'd be power bi but what i have found is it's really helpful to understand that power bi is one of these four packages of tools or one of these four tools that are part of the package known as the power platform so power bi at the very least you should know is more of a reporting tool right it's a reporting and analytics tool that allows you to build visuals and dashboards and analytics on top of your data so it's kind of a reactive take take the data that i have and be able to build out visuals on top of it but that's not the only tool that exists with inside the power platform i'm going to take myself off screen here for a moment so you can see all of the slide here but in addition to power bi is that reporting tool you also have tools known as power apps and power apps is the rapid application development tool that allows you as a business user to develop your own applications you also have power automate which is kind of a workflow automation tool that workflow automation tool allows you to create automations or they call them flows and those flows allow you to create these basically triggered events that kick off a process based on your trigger initiating and then the fourth tool that you'll see here is power virtual agents and power virtual agents is a chatbot tool and that chatbot tool allows you as a business user to be able to build out your own chat bot so that way you can have your end users engage with a ai chat bot that you have designed now the major theme that you'll see across all four of these tools is that they are designed for business users and business users are the main audience of who would actually develop and use and leverage the power platform tools whether we're talking about power bi power automate power virtual agents or power apps they are all tools that are designed for business users to be able to solve their own problems and that's where you kind of hear this term of citizen developer come out to play where you as an end user or as a business user can actually use these tools to solve your own problems that's really the power of the power platform now built on top of the power platform it's built on top of the architecture of azure that's microsoft's cloud which also has things like various data connectors that are available across all of the platform whether it be power bi or power apps there's many many different data connectors you have available so if you're trying to connect into a particular data source you likely have a data connector already available for those you have things like ai builder for building out artificial intelligence capability but again you don't have to be a developer to leverage these ai features ai builder actually allows the data consumer or should say the business user to be able to leverage and use ai capabilities on your own you don't have to have a developer to leverage those ai features and then finally the last mention in the bottom right that's why i took myself off camera is dataverse and dataverse is this database that goes across the entire power platform you're gonna predominantly see it used in power apps and power automate but it's also available and you'll see it on my screen as we get into the power bi desktop it's also available with inside of power bi as a data connector so you can connect into a dataverse database that you've created and build solutions and reports on top of your dataverse database so kind of think of the dataverse database here or the dataverse solution as the data store that crosses the entire power platform whether you're using any four of these tools you can have data that's stored with inside dataverse and databurst is really designed to very easily interconnect into every element within side of the power platform now we're not going to focus a lot on the majority of what we see on the screen here our real focus for today is of course power bi so we're going to be focused and discussing power bi throughout today and so let's because of that let's go ahead and talk a little bit more about the process of creating a power bi project so the phases of a power bi project are really three-fold here i should say fourfold for that matter and those different steps that you have with inside of any power bi project are going to start with this data discovery phase and the data discovery phase is all about connecting into the various data sources that you have which oftentimes may be more than one you might have more than one data source you need to be able to answer any one question with inside of your ecosystem but also as part of that data discovery phase you also have the opportunity to do data cleansing so the idea of data cleansing is not only am i going to connect to my data but i likely have some issues with that data that i need to appropriate appropriately clean up and fix before i go to present that data in any kind of report so that data cleansing capability that exists inside power bi is known as power query and that power query tool that's part of power bi is a really powerful component to allow you to again create a set of business rules to fix data as it comes in and there's a lot that goes into that we could have an entire class just focused on power query and we could spend three hours just talking about power query or even a whole week just talking about power query but that's that first phase of any power bi project is that data discovery phase to go connected to my data and then appropriately clean any kind of poorly formed data as it comes in the second phase of any power power bi project is more of this data modeling or data mash-up phase and this phase is all about creating relationships between the various data sources you have because likely there's many cases when you have a question about your data you might need to connect into more than one data source to be able to truly get the answer to that question think about it if you wanted to know what is profit for my organization if you're trying to determine profit you likely need to connect into four or five different data sources to really get all of your expenses all of your revenue payroll information inventory all the stuff that you need to wrap in together to determine what profit is for your organization you're likely touching multiple data sources to really get that answer in some cases that might lean into more of an enterprise solution like a data warehouse but the data modeling with inside power bi does allow you as a business user to connect into those various data sources that you have and then create relationships between them so if i need to go pull in some payroll information from let's say oracle and i have some sales data in sql server and i have some mainframe data and excel spreadsheets and access databases and all of these different data sources you can interconnect them together using the data modeling and data mashup capabilities with inside power bi the other part that goes into your data model that you create is where you'll also define and create calculations using a formula language known as dax dax is the data analysis expression language we're going to very briefly touch on that today obviously in a three-hour time frame we're not going to be able to get super deep into that but i would encourage you to check out some of our additional training we have two separate full classes just on dax through our on-demand learning portal but there's a lot that you can learn when it comes into the data modeling components dax is a big part of that and we're going to get you a little bit of a peek at what that looks like today now the part that most people think of when they think of power bi is data visualizations right you generally think of whenever you're creating a bi solution you think of data visualizations building out visuals uh creating reports and dashboards but as you can see that's really kind of the second half of any kind of bi project that you create and i say bi generically because that's true whether it be an enterprise bi solution that it is doing or whether it be more of a self-service bi solution that you do through power bi you really have to focus in on those first two phases that are mentioned here that being data discovery and data modeling to be able to properly create reports and that's the next phase here though is the data visualization side of things and through the data visualization aspect you're building out visuals reports you're filtering your data you can create things like storytelling through power bi you can build out things like personalized bookmarks there's all sorts of things that go into the data visualization side of things but the main point here is that while data visualizations is the big aspect of power bi that everyone as far as end users sees as an end result it really is kind of the downward part i shouldn't say it almost sounds negative but it's not negative uh it's the the last part of the bi project that you do you do you have to first make sure your data model and your data discovery and data cleansing pieces are done properly before you can have a well-done set of reports so that's a key part here to realize that uh it's it's a very powerful part of power bi but it's not the only part that goes into any kind of bi solution now once you've done these first three steps these are primarily going to be done through the power bi desktop now some of these can be done from your web browser through the power bi service but these are primarily going to be done through the power bi desktop application that we are going to start to talk about here in a few moments these first three steps can be done for free with inside the power bi desktop so the power bi desktop is free there's no licensing cost to the power bi desktop you may be prompted to sign into a power bi account but you can kind of get past that and you can do whatever you want with inside the power bi desktop at no charge where licensing comes into play for power bi and we're not going to get super deep into licensing today but where licensing does come into play with power bi is when you go to share your solutions with others so when you go to share your solutions with others that's when you need to have a power bi license and also the people that are receiving your solution need to have a power bi license as well now there's multiple levels of licensing that you can do there is a power bi pro license which is a list price on on power bi's website is ten dollars per month per user but there's also another licensing model known as power bi premium which you can license either per user now that's actually a new licensing model that used it used to only be kind of for bulk licensing but they have a per user license for power bi premium as well as the ability to license hundreds of users through premium capacity now we're not going to get super deep into that i would definitely recommend researching more and learning more about licensing with power bi but the key thing i want to let you know for now is that when you want to share with others you need a license they need a license so that's some one misconception that oftentimes happens with power bis people think you can share with others for free but they're going to need a license as well even when you do things like embedded in sharepoint or if you embed it in teams uh which you can do you can embed your solutions in sharepoint and teams and you do need a license uh in those scenarios as well so this is really all i have for slides i just wanted to kind of get you an idea at a high level of what are some of the things that you need to be thinking about as you go into a power bi project and what we're going to do next is we're going to jump right into it i'm not going to delay uh kind of jumping into because we have of course only three hours together so i really want to jump you into immediately seeing what this tool is like in case you are new to it and again as we go along and as we work together you're going to have the opportunity to pick up on some new hopefully a small little advanced features for those of you that have been working with power bi for some time so i'm going to shift over to my screen once again and what we're going to do is i'm going to go ahead and close out of our powerpoint here for now and i have opened a web browser and what we're going to do in this is first thing i want to do is uh matt i know i'm sure has posted in the chat many times where you can go download the power bi desktop hopefully by this point you have that power bi desktop ready to go and installed on your machine it's a pretty small install so if you're even if you're installing it right now it'll only take a few moments but what we're going to do is i want to set the scenario here for you for a few moments let me put myself back on camera here for a moment so here's the scenario you know anytime you start a power bi project and let's say for example your company is interested in adopting power bi the best way to do that is to think through a good use case within your organization and so i'm going to give you a use case and we're going to follow this use case throughout today and this is where for those of you that have attended any of our classes before you've likely seen this use case before but the use case that we have that we're going to follow for today and the reason why i like this one is because we can pull in data from the web and everybody can follow along but we're going to be following along through a use case that we work for a bank okay so let's say imagine you work for a banking institute and that we are interested in launching and creating and building on our next set of brick and mortar banking centers you know physical locations right you don't you don't have a whole lot of new banks that open up uh around the world anymore a lot of things are done through the internet now online banking obviously is a has higher prevalence now than ever uh and and so we have some budget in our company to be able to create a new uh and build a set of new physical brick-and-mortar banks in the united states and so what i'd like to do is i want to pull in all sorts of data we have all kind of data internally to help us make this decision we have data that tells us where our most of our customers are we have data that tells us our most frequent customers but what we'd like to do is see if there's any supplemental data that's outside of what we have within our organization that can also be at least an influencer into our decision on which one of the banks or which one of the locations uh around the country that we want to open up our next physical location and so what we're going to do and i would like you to follow along through portions of this is we're going to have you open up your web browser on your screen so you likely already have youtube open open up another tab and we're going to open and go to a url and i'm going to pull this up on my screen here as well we're going to open and go to a website known as data.gov okay so you're going to go to data.gov and i'll leave that up here for just a few moments and if we go to data.gov in your web browser we'll be able to connect into a public data set that has a a list of all of the failed banks within the united states over the last 20 years so this is going to be an influencing or an influencer to our decision on maybe perhaps where we choose not to open a bank because if we see an area of the country that has a lot of failed banks then maybe there's something going on or maybe there's some kind of trend in that area that we might want to avoid again not the overarching reason why we would choose to open a bank in one place or another but it's going to be an influencer all right i'm stalling a little bit to give you time to go into your web browser and go to data.gov i'm going to go ahead and hit enter now and what's great about this website is they have tons of different data sets you can choose from you can see there's 280 different data set 280 000 different data sets you can choose from here and what we want to do is in our scenario here is we're going to search for a data set called fdic failed banks okay so go ahead and search for that data set in the data.gov website and again i'm going to try and pause every few moments here to make sure you're able to keep up but again as a reminder with inside youtube you can always rewind me and go back and see something if you missed it i'm going to probably remind you that several times today all right so i'll hit enter on this and that's going to return back 20 data sets that have something to do with fdic failed banks and the one that we want for our scenario is this very top one this one called fdic failed bank list all right so i'm going to go ahead and select that top listing right here and that will take us into the website where we can see the data connection or the the file that we want to use okay i should note here by the way and i'm sure matt's put this in the chat there's about a 40 to 30 to 40 second delay within my stream so if i if matt kind of runs in and says hey there's a question about this or something like that there may be a little bit of a delay in me answering some things like that there also should note that if we're about to have several hundred people that are going to this website i you know you could probably expect a little bit of a delay as we're all going to the exact same website at the exact same time so plan for that expect things like that to occur now what i want to do is on this website is i would like to take this csv file this banklist.csv and i would like to use it as a data source now why one of the really neat things about how power bi works is you can actually connect into this data source from the web and so rather than me having to download this data so don't actually click the download link here but if i were to click the download link i could download this save it somewhere locally and then connect to the local csv file i have but what would be even better is if i could just point to the url for where that file is stored and then use the url as my web as my data source so that way as the government updates that file all i have to do is refresh my power bi solution and then the latest version of that data will come in so it gives me the ability to kind of have a almost like a a closer to real time view of my data so it's skipping that step of having to download this and then storing it i still will have to kind of work on some kind of data refresh process which we'll talk a little bit later about in the last hour but what i want to do is rather than downloading this file instead i'm going to right click on the download button here so i'm going to right click on download and let me move my head here for a moment i'm going to right click on download and we're going to select that i want to copy the link address now i'm using google chrome and because i'm using chrome my says copy link address if you're using something like edge it might say copy shortcut it might be some variation of this but essentially we want to copy the link for where the download button is going to take us now matt already has who's in the chat already has this url and he knows exactly the url that i'm about to take so i'm sure he will paste this in the the the chat if you if he hasn't already i'm sure he will he's he's on top of it uh he'll get that posted in there for you but i'm going to copy the link address here and then i'm going to use this and take this over to power bi okay now power bi is the way we're going to start this is we're going to launch the power bi desktop so assuming you have already downloaded the power bi desktop and installed it which again it's a pretty light install so you could even you know install it right now and catch up pretty quickly if you rewind my video a little bit but i'll be able to launch and open the power bi desktop right here now i'm going to launch it from scratch because i do want you to see what this looks like when you open it for the first time so i'm going to go ahead and launch the power bi desktop that'll take just a moment here looks like it's opening on my other screen i'll make sure it comes over to the right place there it goes so when you open the power bi desktop for the first time and again this is the main application that you're going to use to be able to develop power bi solutions when you open power bi for the first time you're generally presented this startup screen however if you you may also be prompted to actually sign in now you don't need to sign in for what we're doing for the first couple hours of the day so if you're prompted to sign in or if you're prompted to sign up for a power bi account you can hit cancel past that we will talk more about signing up for power bi and what power bi subscriptions mean a bit later and so once you skip past the sign in or sign up process you should see a start up screen that looks like this and with inside the startup screen this gives you a few things as far as where to get started with power bi on the uh left hand side of the startup screen here you'll see things like the fur the recent files that you've opened so there's some files that i've opened in the past on this machine on this workstation you'll also see things like the get data experience here that will allow me to connect into whatever data source that we're using for our example so i can click get data and kind of get started there there are a few video tutorials here but i don't know if you'll notice this but they're pretty out of date you can tell they're out of date because the view of the little screenshot here is kind of a darker view of the power bi desktop and it's quite different than what you actually see on my right hand side so they do need to go in there and update them of course that's why we certainly recommend our training we do keep it up to date uh then on the right hand side of the startup screen you'll see some useful links like the forums so where do you go if you need help if you need to ask a question you can go to the forums the power bi blog is also a great place to go the power bi blog by the way i do recommend you keep a pretty close eye on because this is how i keep up to date with all the changes that happen with power bi uh you may or may not already realize this but the power bi desktop is updated on a very frequent basis generally it's about once a month there's some exceptions to that but generally about once a month you'll you're going to have a new version of the power bi desktop to install so if you had the power bi desktop already installed on your machine for today you may notice that yours could be a little different than mine if you haven't installed the latest version in quite some time they do update it very regularly regularly so the power bi blog is a great place to go to keep up with all the changes they do have a regular monthly entry blog entry to tell you what's new with the tool my colleague matt peterson and myself also do a video based blog to let you know some of the new things that have come out with the tool as well so there's a lot of really cool things that you can learn keep up with the blog that's a great way to keep up with what's going on with the tool but i'm going to go ahead and hit the x in the top right hand corner of this just wanted to kind of give you a little bit of hey here's what's going on here all right now once you get rid of that this is the power bi desktop that you will get very familiar with over time with inside the power bi desktop there are a few areas i want to get you a little comfortable with get you a little familiar with what's going on on your screen as we continue forward when i tell you to go to this area of the screen or that area of the screen i want you to know exactly what i'm talking about so let's do this i talked about these different phases of a power bi project before i want to highlight in the tool where you would find each of those phases so for example let me zoom in here for a moment try that again all right so for example when you get started with any solution you're going to begin by going into the get data experience right here so the get data button is going to be the first part of any power bi project that you start with so that's always always always going to be your starting point you'll go to the get data section to go along with the get data button you also have the transform data button and i would say that both of these two buttons together would make up that first phase of our power bi project that i mentioned earlier which was that data discovery phase that i talked about earlier you may also think of this as more kind of like a data cleansing phase as well so this is that first step where we're going to go connect to our data and apply any kind of data cleansing steps that we might have that's all done within that first step here the second phase which we talked about which is the data modeling phase is primarily going to be done with these two buttons over here mainly the model the model button here on the left hand side but let's talk about these two for a moment these two are the data view as well as the model view okay and through these two buttons this is where you'll go through that second phase of any power bi project let me not zoom in any more than that there we go that second phase of any power bi project is that data modeling some people might refer to it as data mashup not made a data mashup and through this phase is where you're going to create relationships between the various tables that you pull together this is also where you'll write dax so there's a lot of things that go into this phase here all right let's also talk about the third phase so the third phase which would be done primarily through this button in the top left this would be the visualization view i should say the report view and the report view is going to be part of that third phase of any one of your power bi projects this would be that data visualization data report development whatever you want to refer to it as but this is that data visualization phase of your project so you're starting to build out reports and dashboards and visuals that's all done primarily through this little report view that you see on the left hand side and the the fourth phase which we mentioned was kind of that data sharing or or sharing step and that's going to be done not so much on the screen right here actually tell you what let me actually identify it a little clearer here it's going to be done through this button that's grayed out right now so through this publish button this is going to be that fourth phase of your project and that fourth phase of your project is all about sharing so how to take the results of what you've designed so far and share it with others well you're going to do that by publishing it to what's known as the power bi service this is where things like the power bi subscriptions come into play and the licensing for power bi comes into play all at once you start to publish and share with others okay now you can actually publish to the power bi service using a free account that free account doesn't give you a ton of uh abilities but you can kind of publish to your own personal my workspace and you can kind of test things out there as you're getting some experience with power bi but that's kind of the four phases of your power bi project connect to your data model your data visualize it and share it with others okay all right so let me take this off screen all right so uh within side of the power bi desktop we've talked about several of the different areas in here but again worth kind of highlighting here that this left side of my screen these are going to be your views so we talked about this is your port view this is your data view this is your model view all the views are found on the right hand side and the reason i'm sharing some of these things with you is because i'm going to tell you hey go to the report view go to the model view and when i tell you that i want you to know what i'm talking about so on the left hand side is views on the right hand side these are called panes so these are your filters pain your visualization pain your fields pain all of these on the right hand side are going to be pains so if you hear me say hey go to the data go to your uh filters pane or go to my fields pane this is the area i'm referring to and then of course up on the top here these are your tabs or i think the more formal name for them is the office ribbon but you might refer to these as ribbons or probably more common nomenclature here is tabs so if i tell you to go to the model tab where i tell you to go to the insert tab these this area up top here is really what i'm referring to as far as the different tabs that are available okay all right so now that we've got some of the layout of the tool out out of the way and hopefully that gives you a little bit of background on where we're going to be clicking and what we're going to be doing let's actually start to connect to the data source that we pulled in and that we copied a few moments ago so remember we went to that fdic failed bank website that was found right here and we copied the link so remember what we did we right clicked and we copied this link address and now we're going to bring it over to the power bi desktop and use it as a data source now the way that you can do that is you can do that by going up to the get data button up in the very top and i can select get data and then tell power bi that i want to pull in data from a web source so here's what i would like you to do if you're following along is you're going to go up to get data with inside the power bi desktop and select web and once you do that it's going to pop open on your screen i'm delaying a little bit here to give everybody some time to click it but you're going to select the web button and when you choose that it's going to give you a dialog box where you can paste in the url that we copied earlier for the failed banks data sets so you can go ahead and do that i want to show you one more thing before i click on that myself the one more thing that i want to show you is that you are not limited to just these data sources that you see here you actually have if you click on more don't actually do this just watch this part but if i click on more this will show me a list of all of the data sources that i have available so look there are a ton of different data sources that i can connect into you're not limited to just the ones that you see in that common list you can actually see there are a ton of different data sources that are available to you here definitely worth exploring find out which ones are going to be useful to you but of course you probably already know hey i need to connect into salesforce or i can need to connect in a dynamics or i need to connect into sql server if you know the types of data connections you need you can see if they are available here in this much larger list that they have available so all i'm trying to do here is just show you hey it's a pretty overwhelming overwhelmingly large list of data connectors that are built in you can find the larger list again how did i get there i went up to get data and i selected more but what i had you do just a moment ago is i had you select web and i'm going to go through that same experience that you did as well just wanted to show you where you can find that larger list but for the purposes of what we're going to be doing together i'm going to go underneath get data and select web just like i told you to do a few moments ago okay so i'm going to select web here then once i do that it's going to pop up on my screen where i can paste in the url that we copied earlier so again i think matt will probably paste into the chat this url but that's the url that we're going to be using as a data source and again i can imagine as we go to connect into this and we have several hundred people that are trying to connect into it there may be a little bit of a delay but it's uh it's the government so hopefully the us government's website can handle this maybe i'm making some bad assumptions here but we'll see if we can all connect into this at the same time so we're going to paste in the url for our web source now our web source in this case is a csv file but you can also point to just a screen of data so you don't have to point to a file at a url location you can actually just point to a regular website that has data embedded on the screen and power bi has the ability to do basically like screen scraping and pull the data off of some tabular data that's stored on a website so there's some really really neat things that you can do and you're not limited to just a file type that you can connect into you can connect into just data stored on a web page so there's some really cool things you can do there but for our example we're going to stick with this csv file and go ahead and click ok now when we do that i am likely not you are likely going to have an extra pop-up that comes up that did not happen for me and the reason for that is because in hang tight i'm gonna i'm gonna actually produce that on my screen as well so hang tight where you're at because you are likely seeing an extra screen here that i did not see and so i wanna i wanna reproduce that so bear with me for one second because i'm gonna clear this out because i want you to see the exact same thing i'm seeing and so the reason i did not see a screen that you saw is because i've connected into this data source before so let's try it one more time now that i've cleared that out i'm going to paste that in again and catch back up to you you're already at that point here's the screen that i was expecting you you would see that i was not seeing and so what this screen allows you to do is determine how are you going to authenticate to that source so am i going to need to sign into that source do i need to use windows credentials do i need to use web credentials of some kind in this case we're connecting into a public data source a public web source and so we are going to connect anonymously so really you don't need to do anything here other than click the connect button i just wanted to make sure you that i saw the same button you were seeing all right so i'm going to go ahead and hit connect and when i hit connect that's going to connect me in anonymously to that web source this is now showing us the fdic failed bank data pretty small data set and i wanted to keep it small for today but this is showing me a preview of the data that we're about to bring in now as you look in the bottom right hand corner of this new pop-up you'll see you have three options i think cancel is pretty self-explanatory i'm not going to explain what cancel does you guys are pretty smart you can figure out what cancel does but let's talk about these other two options because these may be unique to you now if you are seeing a different name for transformed data so if you see on my screen it says transform data but on your screen it says edit if that's the case for you then that means you're running at this point a very old version of the power bi desktop i do recommend updating you'll be able to follow along just fine for today they just changed the name of the button which used to be called transform data is now sorry it used to be called edit is now called transform data so if your says edit you're running an older version but let's talk about what these two options do if i were to click load load would allow me to take the data that we see above here oops let's do a square instead it would allow me to take the data that we see above here or that way excuse me and be able to load it into my power bi data model so it'll allow me to take that data in load it into my data model inside power bi and immediately start to build visuals on top of it so you would choose load if you feel like your data is in pretty good shape already if you don't need to apply any kind of data cleansing to it you would choose load because that would take you immediately to start to build out data visualizations if you feel like you need to fix some of your data which is probably almost every one of your data sets you connect into then you would ideally choose the transform data option right here and so what transform data will allow you to do is it's going to launch open the tool that i named earlier called power query and so by launching power query it gives you the ability to do some data manipulations to fix your data as you go to bring it in so if you have some data cleansing or some steps that need to be done to fix your data before you present it transform data is where you're going to go and generally speaking you're going to hit transform data in almost almost all of your power bi solutions so that way you can at least just bring back the columns you need or maybe apply some basic uh changes to your solution before you go to visualize it so again the difference here is load you don't have any data cleansing that needs to be done transform data is where you're going to go if you have some basic transforms or data cleansing that need to be done so we're going to learn about the in this first hour about that data cleansing step so let's talk about transform data i'm going to go ahead and click on transform data here and when i click on transform data it's going to launch open what's known as the power query editor and you can see then that it actually launches a separate window if i minimize this here for a moment you'll see that i have two windows open in the background i have the power bi desktop in the foreground i have the power query editor and so the power query editor is where you're going to go whenever you need any kind of data cleansing you can always by the way get back to the power query editor if you ever close this by clicking on the transform data button here this will relaunch the power query editor later if you close it by accident or even if you close it on purpose clicking transform data will re-launch this power query editor that we're looking at all right so let me bring this to full screen now i just wanted to point out the fact that the power query editor while it's part of the power bi desktop launches as a separate window anytime you save something for power query it's saved as part of one single power bi file there's not separate files for power query and power bi it's all saved as one single file it's a pbix file as the file extension all right so within side of the power query editor there's a couple things that i want to do to fix my data before i go to present it now the first thing that i recommend you do i'm usually really bad about remembering my own best practices but i remembered it this time my best practice for you to start off with is that you should rename your query something more appropriate and in this case you can see that my query name is just called bank list i do recommend that you name it something that is more user friendly in fact you can have things like spaces are okay to have in the names of your queries by the way that the query that we have here is going to turn into a table with inside of our data model so whatever you name the query here is what the name of the data model table will be so you have two different places you can go to rename the query you can go on the left hand side where it says queries and i can double click on the query name here to rename it or the other alternative is you can go on the right hand side on the far right where it says query settings and you can rename your query here as well and so i can just come here and i can type a new name on top of the old one and i'm going to rename this one something like just failed banks for this example okay by the way for some reason you ever lose the query settings pane if you if you hit x on it by accident that happens sometimes if you ever get rid or lose this little pain on the right hand side you can re-add it by going under the view menu so if i go underneath view up top here under view you'll see query settings right here and that will allow you to add it back uh over there that'll allow you to add it back if you ever mistakenly close the query settings pane so if i click on this that'll bring the query settings back visible on my screen here again okay all right so there's a couple things that i want to do to clean up this data before i go to visualize it three things that i'm going to guide you through how to do here is one i'm going to remove some columns that we just don't need and this is a general best practice with power bi if there's columns that you are never going to need for any reason with inside of your power bi solution you should remove them okay and so that's the first step that i'm going to guide you through is how do you remove columns from a particular data set okay the second thing i'm going to show you is how we can start to learn how to leverage and use various transforms that are available to us as part of that process we're also going to learn about data types with inside the power query editor and how's the transforms that are provided to you inside a power query are contextual based on the data types of the columns you have so that's the second thing we're going to learn is we're going to learn how to transform this closing date column that we see right here i want to create another column next to it that returns back and parses out the year from the date okay so i want to have a second column here that not only has the date but the second column will now have the year okay so that's the second transform we're going to do the third one that we're going to do is going to have to do with the city and state column that we see right here so the city and state column that we're bringing back right now they're stored here separately but one of the things that i would normally ask if this was more of a interactive interactive classes i would ask you what would be the potential problem with the data being stored like this let's say for example that i want to combine the city and state together as a single field normally i would ask if i could see your chat back to me is i would ask why would it be beneficial to combine these two columns together i want to combine city and state together but i'll go ahead and answer answer the question myself here today is that it's beneficial to combine these two columns together because you oftentimes will have multiple cities or i should say one city name that can appear in multiple states so i happen to be from the city of jacksonville florida but and i saw there's quite a few people in the chat a few moments ago that said they're from jacksonville as well hello fellow floridians but what benefit i get by combining these two together is when there are cities that appear in multiple states it makes makes the value less ambiguous so it makes it so it's really clear that hey we're talking about jacksonville florida not jacksonville north carolina boo north carolina i'm kidding i'm not going to boo north carolina or if it's columbus there's a columbus georgia there's also a columbus ohio so how do i make sure that we know which one is which and there's a couple different ways you can manage this with inside of power bi one way is you can combine the fields together and then there's some other ways on the data visualization side of things that you can manage that as well but for interest of exploring some of the things you can do with inside the power query editor i'm going to show you a couple different ways that you can combine those two fields together all right so first things first that's the three different transforms we're going to do remove some columns transform our date to a year and then combine city and state so the first thing we need we want to do is we want to remove some columns that we don't need now there's a number of ways that you can accomplish this and you'll find out in the power query editor that there's generally about three or four different ways you can do the exact same thing with inside a power query and in this scenario what i'd like to do is i am going to show you a couple different ways but i'm going to show you my recommended way of how you remove columns so don't follow along with these first couple examples i will tell you when i want you to click along with me so if you want to remove a column again don't do this but one way you can do it is you can simply right click on a column and you can tell it that you want to remove the column that's one way you can do it you can also multi-select and you can remove multiple by multi-selecting and choosing remove column you could also choose the columns you want to keep so if i were to choose the columns here that i want to keep and then right click i can select remove other columns and it would remove all the ones i don't have selected so there's a couple different ways you can do this however my recommended way of dealing with and removing columns is to actually use this option that's found underneath the home tab or home ribbon so make sure you have the home tab selected and then you'll find there's this option here called choose columns and the reason why i recommend that you use the choose columns option is because when you're working with really large or i should say really wide data sets and what i mean by wide is i mean you have a lot of columns in some cases maybe you're dealing with a data set that has hundreds of columns right i'm sure many of you probably have some pretty gnarly data sets that have hundreds of columns and in reality when you go to analyze that data set you need maybe 10 to 20 of those columns and so if you were to use that interaction where i right click on a column and remove it that would be a pretty arduous process when you have many hundreds of columns the choose columns option is a is a more efficient way to be able to remove columns so i'm going to select the choose columns option right here that i've been highlighting so i'm going to go ahead and choose that right here and when i select choose columns you'll see it'll give me a list of all of the columns i have available here and i can pick and choose which ones i actually want to keep now in this case it's a pretty small data set so i could have really chosen to remove columns in any way that i showed you a few moments ago but i wanted to get you in the habit of using the choose columns feature because when you are working with much wider data sets meaning again many columns this is the ideal way of doing it here you can search for columns so if i have a big list of hundreds of columns i can search for them here you can also just simply come in and uncheck the columns you don't want so if i don't want closing date i can uncheck that here's actually the columns i want you to uncheck go ahead and uncheck cert and acquiring institution and that's what we're going to do so go ahead and do that with me but i can uncheck the columns i don't need and then when i have those columns unchecked i can go ahead and click on ok on the bottom i'm going to keep this on the screen here for a few extra seconds to make sure you're able to follow along but again make sure you rewind if you miss something i do you can always rewind the video but once i've unchecked those two columns i'll go ahead and click ok and that will remove the columns that i don't need as i apply these various data transforms including removing columns that's a data transform here as i remove the columns you will see on the far right in the query settings pane above me here that there is an applied step section that lists all of the data transforms that we do so above me you'll see that each of the data transforms that i apply whether it be removing a column or changing the data type of a column all of those steps are going to be found here and if you ever realize that you make a mistake and you need to undo something you'll click the little red the little x that appears to the left of the transform or the step to be able to undo that prior step you can also in some cases you can edit these steps as well if you see that little gear icon above me using that gear icon that will allow you to edit a step if you want to change the settings or in this case change the columns that i'm returning i can use that little edit step if there's a step here that does not have the little gear icon then that means that you say you can't edit it you just have to remove it and redo it if you made a mistake so like this one above it doesn't have a gear icon that means you cannot edit that step all right cool so we removed the columns that we don't need which was just those two columns the acquiring institution column and the cert column the next thing that i want to do is i want to parse out the year from the date here so closing date here i have that i want to take this column and i want to add another column next to it that returns back a year so if i want to do something like that then i recommend first i'm going to go ahead and make a duplicate of the column because i want to keep the original column i want that original column to stay there but i want to have a new column that returns back the year from it so the first thing i want you to do to be able to do our step number two here is we're going to right click on closing dates so right click on closing date here with me and we're going to select that we want to duplicate the column okay so we're going to make a copy or a duplicate of the column by the way if you happen to notice the word copy up at the top that's different what that copy does at the top will it'll actually copy out the data that's stored in this column and allow you to go copy and paste it into like excel or something like that so that's not what we want to do we don't want to copy the data we actually want to duplicate the column so go ahead and select that with me here and then you'll have a duplicate column that's created right next to it all right so let's spend a few moments here talking about data types you can see the data types of your columns if you look in the top right hand i guess the top left-hand corner of each of your column headers you'll see this little indicator here showing you the data types of your columns so you can see next to my state column i can see abc which indicates that it's a text column you can see a little calendar icon to the top left hand corner of my closing date column that indicates that it's a date and then if you were dealing with whole numbers you'd see one two three and you can actually see if you click on the little icon or the indicator here in the top left this will show you all of the data types that you have available with inside of the power query editor so you can change data types here if you wanted to but the main reason why i'm bringing up data types right now is i want you to be aware that depending on the data types of your columns you do get different transforms that are available okay so don't follow along for this for a moment i just want to show you this when i go to right click on the state column and i go down to transform you'll see that the types of transforms that are listed here are ones that are specific to text so i can do things like uppercase my columns are my values i can capitalize each word these are all things that are specific to text columns however when i go to right click on my closing date column here let's zoom in on this one more time when i go to right click on my duplicate or my closing date column copy go ahead and follow me along at this time if i right click on this column that's the closing date copy column and i go down to transform this time you'll notice that i get transforms that are contextual based on the fact that this is a date data type and so the types of transforms that you can do again are contextual based on the data types you're dealing with so what i want to do with this one is i want to take the the closing date copy column and i want to let me take myself off screen for a moment i want to convert this into a year and then convert it into a year again so you're going to select year and then year a second time for making this return back just the year by itself these other ones will actually return back a date like the first day of the year or the last day of the year we want to return back just the year by itself so i'll go ahead and select that and look what it does so it parsed out the year from the date here it's able to return back very easily a parsed out version or a parsed out view of the data here okay so that's how you can convert a date to a year here now you can also very easily rename the columns you'll also notice by the way here that it did change the data type of the column you can see the little indicator in the top left shows one two three meaning it's brought it back as a whole number now that's fine we'll leave that alone here for now it's actually going to uncover some problems later that i want to show you but let's go ahead and rename this column to rename a column you can either right click on it and go down to rename that's one way you can do it or my preferred little shortcut here is you can just double click on the column header so if i double click on the column header i can give it a new name just by typing on top of the old one so if i rename this year i can type on top of it right here okay all right so that's our first two steps remember we had three steps we wanted to remove some columns we've already done that we wanted to parse out the year from the date we just did that my last step which i should highlight here if you look above me notice the applied step section is starting to build out right we're starting to have multiple steps that are appearing here every one of the transforms that we do adds to this list of applied steps and we're about to add to it a little bit more because what we're going to do in this next step is we want to combine my my city and state together so i want to combine and be able to see fort walton beach comma florida that sort of thing now there's a couple different ways you can do this like i mentioned a couple times there's usually three or four different ways you can accomplish the same thing with inside the power query editor one way you can do this don't follow me on this first example this first example is you could multi-select again don't follow me on this you could multi-select the city and then hold ctrl and select year and if you right click you could do tell it that you want to merge those columns together that's one way you could do this there's nothing wrong with doing it that way but i want to show you a little special way that's a little different and the reason why i want to show it to you in this different way is because it's going to expose you and maybe give you some ideas of some other things that you can do with it as you learn more about working with inside of power bi and more specifically the power query editor all right so this different way that i want to show you can be found by going underneath the add column ribbon up on the top so go find the add column ribbon or tab up on the very top part of your screen and you're going to go ahead and expand or select add column and then we're going to choose this option i'm going to zoom in on this for a moment we're going to choose this option here called column from examples that's the very first option in the top left we're going to go ahead and select column from examples and then for this scenario select from all columns okay uh what very briefly what from selected columns or what cell from selection would do is if you had multiple columns that are very similar to each other say for example i had a bill to and a ship to address and so i had multiple cities and states in there i could choose which columns i wanted to base this new feature that i'm doing on so i'm jumping a little ahead here let me pause for a moment and tell you exactly what we're doing with this feature what the column from examples feature will do and what i'm going to have you select here in a moment is from all columns but what this feature will allow you to do is provide power bi an example of what you want the end result to look like and then power bi will figure out what transforms are required to be able to accomplish the thing you're trying to do so for example if i'm trying to combine the city and state together i can give an example where i can type in jacksonville comma florida and it will figure out what transforms need to be done to be able to accomplish that and the reason why i want to show you this method is because when you are new to power query when you're new to power bi and you're trying to learn about this data cleansing tool having this feature where you can create new columns from examples is huge because it will teach you a lot about how to work with the tool because it will show you what's happening behind the scenes whenever it finds the pattern of what you're trying to do and it interprets it into a transform so again if you're following along we're going to select column from examples and then select from all columns okay so we'll select from all columns here when i do that on the right hand side of my screen you'll see and it's actually above me right now you'll see that there's this new blank column that's now visible here and in this blank column this is where you will fill in an example of what you want to return back and you're basing it off of what you see in each of the rows so for example i can pick any random row here if i wanted to i can go to newark new jersey here this row and if i click within side of the double click with inside of this cell i can type in an example let me take myself off screen for a moment i can type in an example of what i want this to look like and it will produce the results for me so to give you an example i can type in newark and by the way you can use the intellisense here if you use your up and down arrows you can do that here and hit enter or you can just type it in either way works but i can type in newark comma new jersey with a space and if i hit enter on that check out what happens boom it produces a value for every one of the rows that we have in here based on the sample i gave it i fed in an example of what i wanted the data to look like and then power bi or power query here was able to interpret the pattern of the data manipulation that we're doing and it actually created if you look up on the top here it created a transform called text.combind where it combined those two fields together now this is a very simple example combining two columns together is not very difficult but as you have more complex transforms using the add column from example is a great way to learn how to manipulate data with inside of power query because what happened behind the scenes when i did this if we go look at our applied step section uh i need to hit okay before we can see this you can see it's kind of grayed out right now we'll hit okay go ahead and hit ok here with me if as long as you got the same pattern i got go ahead and hit ok and if you have a typo you can just type on top of it but you can see in this applied step section here now it it interpreted the example that we provided into a merge column transform so it added a step here it inserted a merge column here as a new transform for us so leveraging and using the add column from example is a great learning experience when you're new inside the power query editor all right we also want to rename this column and we are getting kind of getting inching towards our first break of the day here but we're going to go ahead and rename this column instead of calling it merged i'm going to double click on the column header and we're going to instead name it city let's try that again double-click and we're going to rename this city state and i'll hit enter all right so we've got everything kind of put together here we did the four steps i'm going to zoom out for a moment and full screen this again we've done the four tr the three transforms we wanted to do okay we removed columns we didn't need we then uh converted or created a new column with the year and then this final step we use the add column from examples feature to be able to create this new combined city and state and again as a reminder how do i do how i did that you can always rewind so make sure you know you can rewind the little youtube viewer here to go back and see how i did that again but what we did as a quick reminder is we went up to column from example we selected from all columns and then you provide an example of what you want the data to look like and then it produces a transform for you so really really cool and powerful stuff all right so what do you do next let's say you've done all of the data cleansing that you need to do and you're ready to move on right you feel like this looks pretty good now at this point your next step then is you would select underneath the home tab so you need to go back up to the top left of your screen underneath the power query editor you select home so make sure you have home selected and then in the top left you'll see the option called close and apply and that's actually what we're going to select here in just a moment but i want to explain what that means what close and apply means is it's physically going to close the power query editor so the window that we have open right now this power query editor is going to be closed and then what apply means is it's going to take the results of the power query editor and apply it or load it into our power bi data model so the data is going to be brought in from this csv file stored on the web and it's going to apply it into our data model so go ahead and select that go ahead and select close and apply and if you see this little drop down come hit close and apply a second time and that's going to close the power query editor and this part by the way because we do have potentially hundreds of people that are doing this at the same time it worked fast for me because i have a little delay i'm about 30 seconds ahead of you but you may see a little delay with it loading all that data in because we have hundreds of people that are trying to click that close and apply button at the same time but what happened when we did that is it brought all of the data that we had from that csv file stored on the web and it brought it into our data model and you can see on the right hand side of my screen right above me that we have now uh in our fields list a list of all of the fields that we were working with with inside of our power query editor so this is kind of our first step with inside of any power bi project is really focused in on getting your data in applying data cleansing and then bringing it into your data model so that's we're through that first phase now we're about a third of the way through our stream here so let's go ahead and take a short very short five minute break and then when we come back we're going to continue forward and start to dig into our data modeling components all right so we'll see you back here in five minutes get a quick drink the three hours is too long to keep keep going straight for either for you here or for me so let's take a quick five minute break and then when we come back we will continue forward uh and learn more about the data modeling components of power bi see you in five minutes all right everyone thanks for joining us again this morning welcome back uh in our next section we're going to begin to dive into the data modeling components of power bi now obviously we spent uh you know maybe 30 45 minutes on the data cleansing part you could spend a lot more time on that there's a lot of other interesting things to cover in there one thing i did happen to notice in the chat as a lot of great questions matt you're doing a great job kind of keeping up with that one thing i did notice that was a great question was around if i have a much larger data set so in the data set we're working with it's considerably small it's it's less than a thousand rows i kind of intentionally did that to hopefully have uh where everyone could follow along fairly easily i know we sounds like we brought down the government's website because matt was telling me that the site was down temporarily should be back up now but um the idea here is that when you're working with inside the power query editor you are working with a sampling of your data so let me show you this real quickly if we go back over to the power bi desktop we were working with inside the power query editor again as you remember might remember i told you you can always relaunch the power query editor if you want to look at it again and you don't need to follow along for this i'm just kind of showing you something but if you ever want to go back to the power query editor to make additional changes or additional manipulations you can do that by clicking the transform data button up in the top and that will relaunch the power query editor again and one of the questions that we oftentimes get is what if i'm working with a really large data set now inside the power query editor this only brings back the first thousand rows of any data set you're working with and the reason why it does that is the power bi team has designed the power query editor to be as responsive as possible is that they've designed this so that if you go to click on something and you go to right click on or interact with or apply a transform the responsiveness is going to be really peppy right that's kind of the idea of what the power query editor is designed to do is not only to transform data but be very responsive to the users and you'll be able to tell if you look in the very bottom left hand corner down here this will actually show you the number of rows that you're working with with inside of the power query editor so if i had millions of rows in my data set power bi can handle millions of rows but when you're working with inside of the power query editor it only brings in the first thousand rows to do your data manipulation on and again the purpose for that is to make the tools still be responsive to be able to return back results very quickly and then what happens is after you create your business rules within side of the power query editor when you go to hit that close and apply button remember we that was the last thing we did before we uh before we wrapped up whenever you go to hit this close and apply button it's going to apply your business rules or your transforms to the entire data set so even though it only applies them to the first thousand rows when you start when you go to hit close and apply it's going to apply your business rules and your transforms to the millions of rows that you have within your data set so i saw that question pop up it's a common one i wanted to make sure i address all right so the next thing that i want to talk about is our data model so our data model is the part of your power bi solution where you begin to bring in multiple tables you create relationships between those tables and it really kind of gets into more of the the ultimate design of your solution and this is a really important part of your power bi solution and often times a very underestimated part uh one of the things that we do at pragmatic works and i want to share this with you here in a few moments as well but one of the things that we do at pragmatic works is mentoring where we actually mentor and work with individuals that are having problems with power bi we can actually sit with you and walk you through a dax problem or walk you through a data modeling problem and the number one thing that we find in those mentoring sessions is where individuals have a poorly designed data model and it's not their fault they just didn't know they didn't know the kind of things that go into a data model in a good design of a data model and so there are some things to consider we're obviously in a short amount of time not going to be able to go through every different element of a great data model design but this section is an underestimated part of power bi that's kind of my point there is that this is something that you obviously will want to learn a lot more about now speaking of learning more obviously today's class is just a three-hour class really kind of getting you into power bi getting you familiar with the tool but what do you do after today now this is recorded obviously uh this is on youtube and whenever we do streams on youtube you'll be able to re-watch this session over and over but if you are interested in learning more beyond what we have to cover today pragmatic works is here for you right we are a training company that is our sole focus we uh we really follow the mantra of we want to teach you how to fish we want to teach you how to do it yourself because when it comes to tools like power bi and power apps and power automate and power virtual agents these tools are designed for you to be able to solve these problems on your own and so if you are looking for additional assistance after today i highly recommend that you take a look at our on-demand learning now our on-demand learning we actually have a special sale for those of you that are attending today to be able to get it for 75 percent off that's unusually low for our on-demand learning we have a great sale in fact i'll bring this up on my screen here so you can see i'm not trying to sales pitch it too much but we do have this offering that i want to make sure you're aware of so after today's class you actually can do it today even if you go to our website pragmaticworks.com and pert and add to our your cart the on-demand unlimited package if you type in the event code or the coupon code event 75 that will give you 75 percent off of our on-demand learning and this well this deal will run through next tuesday so you have plenty of time we'll obviously follow up email give you some more information on this if you're interested that's a great way to go beyond some of the basics because like i said i'm not going to be able to make you an expert in this one three-hour course but we have as part of our on-demand learning 18 different power bi classes so we go well beyond the basics of what we're covering today in our on-demand learning i highly encourage you take a look at that as part of our on-demand learning we have courses that go into showing you the full spectrum of power bi like our introduction to power bi course we have an advanced power bi course we have specific data modeling and dax courses we have two different dax courses which i highly highly recommend mitchell pearson taught those many of you have attended classes with mitchell pearson don't tell him he's pre i don't tell him and i think so but he's pretty good uh he he is a great part of our on-demand learning i definitely would recommend taking a peek at those uh but in addition to our on-demand learning we do have other ways that we deliver training as well again we are all about we want to teach you how to fish teach you how to do it yourself and again we do that through our on-demand learning which you can see on the far left we also do live instructor-led boot camps which you can find on our website as well so we have a week-long power bi boot camp or a four-day long power bi boot camp and a three-day long dax boot camp and we have power apps and power platform boot camps that might interest you we deliver those boot camps both privately as well as publicly so if you have maybe your company needs uh maybe you have 10 people of your own that you want to attend to bootcamp we do those privately we also do hackathons and hackathons are kind of the idea of hey i attend a class and everything works perfectly in a class devin right everything always works perfectly when you attend a class and you go through the labs but when you go back to do this on your own at your own desk things don't always work exactly the way that they're supposed to and so hackathons are our idea of being able to allow you to do a training class but on your own data and so it's still a training exercise it's still a learning exercise but we're at the end goal of a hackathon is to be able to build out a proof of concept uh that's based on your data rather than just going through sample data we have so that's a great solution we also do enterprise customized training so if you're an organization that has hundreds if not maybe even thousands of users that need power bi training we have some customizations for that and what we do is we you know we really realize that everyone learns in different ways some people can learn through web-based training that's recorded some people need a live instructor and so we really have a customized package to be able to train your group in whatever their preferred manner is because everybody learns differently i think you guys realize that some people learn in different ways and then finally the last thing i'll mention here before we move on is our architect mentoring and our mentoring is with the design to be able to have some one-on-one time with one of our instructors where you can show them the problem that you're having and kind of get some guidance on how to get past it so the architect mentoring is one-on-one time you can purchase by the hour you don't have to purchase weeks at a time like you would with normal consulting you can purchase by the hour with a minimum of three hour purchase and then you can spend time with one of our trainers on getting some guidance on how to push further along and get past those roadblocks you're having so the big thing for today though and again i apologize i know you everyone love to hear about what we want to try and show you later but the big thing that i want to focus you in on of course is that on-demand sale that on-demand learning is those courses we keep up to date we re-record them when necessary i highly recommend you take a look at it and you can of course purchase online uh using the code the coupon code event 75 that'll get you 75 off of our on-demand learning so make sure you take a peek at that that sale is going to go on through next tuesday all right let's get back into what we've been discussing so thank you for bearing with me on that now what we're going to take a look at next is how do we add some additional tables to our data model what kind of tables would we add what are some of the things that we might find necessary as part of our data model creation and what we're going to start off with is we're going to create a second table as part of our data model right now we just have the one you can see the one above me here the failed banks table and you can just zoom in on that so you can see it here we could actually start to build out report visuals right now if we wanted based on this table it's ready to go but i want to add in some additional tables to it uh but just to show you i could actually start to build out visuals right now if i wanted to i could bring in a visual and i'm going to highlight and show you more detail on how to do the visuals here in a few moments so don't get too hung up on that i'm not going to show you the details of this yet but i'm going to bring in a basic visual here and say hey i want to see the number of failed banks by state and maybe kind of a surprise to some georgia is the state that has the most failed banks now i created that visual really quickly and i know i didn't show you how i did it i'm going to show you that later but i wanted to get you an idea of showing you the fact that this is not that difficult to do it's all drag and drop i want you to feel comfortable with it and i know i did this one fast but i'm going to show you how to create these visuals here in a few moments after we go through the data modeling components but you can build visuals immediately after you get done with the power query editor you can start to build out your visuals right away and i'm already getting answers to which state has the most failed banks here rather quickly all right so here's what i'd like to do is i want to add in a second table to be able to enhance our model i deleted that visual we're going to come back to visuals later but i want to add in a second table into my data model because i i actually have and have some needs to be able to analyze by various dates within side of my organization and what you'll find with most of bi solutions that you might work with it's oftentimes common that you would have a separated table for your dates now you might notice i have a closing date column you likely saw that before i have a closing date column here that's within side of the table that we've been working in but there are some benefits to having a separated out separate date table i'm sure many of you worked with fiscal calendars so you might want to have a separate date table to be able to analyze things over a fiscal calendar versus a regular calendar a regular uh calendar that you were the date starts in january the year starts in january and ends in december maybe you have a fiscal calendar where your year starts in july and ends in june so there's some benefits to having a date table in fact another benefit might be that you want to analyze and understand how well your organization does let's say if your retail how well you do on weekends versus weekdays or how well you do on holidays if you're open for holidays so there's a lot of benefits to having a date table and those are some of the benefits i just listed here so i want to show you how you can add a date table with inside of your solutions that you're working on as well now there are a number of different ways you can add in date tables uh you can add in a date table from your data source so if the data source you're working with has a date table then you can add it directly in from your data source another way is and i'm kind of searching this on the side actually matt will be able to post it in the chat i'm searching it like i'm going to show it to you but i can't copy and paste in the chat uh but one another way that you can create a date table is with a a power query script okay so what i'm showing you right here this is a blog that i have that matt if you can share that with julia so she can share it to everyone this is a blog that i have that actually walks you through how to create a date table with inside a power query this is actually my preferred way to do it but i want to show you and start to expose you into more data modeling features in this section so what we're going to do together is we're going to create a date table by writing dax and there's a lot of different ways you can create date tables i'm just mentioning three different ways here again those three ways are pull one and from your data source it could be create one inside of power query which is what's on my screen here on the right and then the third way is that you could actually create a new day table by writing dax and that's probably the easiest way for us to do one all together so we're going to stick to that method for what we're doing today all right so for this section what i'd like you to do is we're going to work our way over to the data view now if you remember i talked about we have a report view data view model view you can find the data view on the left hand side right here so go ahead and select the data view with me as we go through this next part of our example okay so i'm going to go ahead and select that as well i'm going to click on the data view and then from the data view we're going to create a new table okay this is not the only place you can create this new table but it's probably the most ideal place because you can see the results of the table that's what the data view does the data view shows you the data behind your tables and so what we're going to do is we're going to create a new date table and we can do that by going up to the table tools tab up top which is already shown to me and then we're going to select the new table button with underneath that table tools ribbon okay so i'm going to go ahead and select new table and then with that selected you'll notice that it basically gives me a blank design surface where i can start to write a dax expression dax again as a reminder is the data analysis expression language and this is where i want to give you a little peek into what you can do with dax just a brief peek into it we're not going to be able to get too deep into it we did do a learn with the nerds previously on dax that's one that mitchell pearson did i highly recommend you go back and watch that one again if you're interested in getting deep deeper into dax that one is recorded on our youtube channel and you can go back and watch it at any point don't leave me now but later on you'll want to watch that dax three hour session that mitchell pearson did a really good session i'm going to get you just a a brief understanding of dax he went uh across three hours covered nothing but dax all right so by creating and clicking on this new table button this allows us to create and write a dax expression that will define and create a new table for us so i'm going to zoom in on this so you can see this a little better okay and i'm going to create a new table by naming the table to the left of the equal sign so everything to the left of the equal sign is going to be the name of your table and everything to the right of your equal sign is going to be the definition of what makes up that table so if i want to go ahead and rename this table i can change it from being called table let's call it instead calendar okay and then to the right of the equal sign we're going to define the name are not defined but we're gonna yeah we're gonna define the calculation that's gonna return back our list of dates for us so we're gonna use a special function with inside of dax known as calendar auto it's a really cool function that if you go down here you'll find it and you can see the description that tells right above me it shows you description of what this function does it says it returns a table with one column that's automatically created a list of dates from my data model so what the calendar auto function is going to do is it's going to look for any date columns that we already have in our data set so we have a closing date column already and then what calendar auto is going to do is it's going to produce a range of dates in this new table based on that closing date column that we already have so it's trying to automatically create this list of dates for me based on what's in my model that's kind of the benefit of calendar auto if i were to choose calendar the one above it that is another function that does something similar but instead of it automatically producing the list of dates i would have to provide a start date and an end date for it all right so in this case we're going to use the calendar auto function let me go ahead and slide over here so you can see and using the intellisense here you can actually start to type in things like type in the word calendar and as i type calendar or type in calendar auto the intellisense will help me out and i can hit the tab key on my my keyboard and it will populate out that function for me so if i want to use calendar auto i can start to type it and leverage the intellisense to be able to fully write out the whole name of the expression now some dax expressions have optional parameters this is actually a scenario here of a calculation or a formula that has an optional parameter and you can tell it's optional because it has these little brackets around it here in the template when you see these little brackets around the template that indicates that those are optional parameters for this data set and the optional parameter in this case is i could pass in when the end of my year is so what's the last month of my year and that would allow me to build out a fiscal calendar here if i wanted to i'm not so much worried about building a fiscal calendar in this scenario but just know you can you can build out a fiscal calendar using dax here what i'm going to do instead is i'm just going to do a close parenthesis since that's an optional parameter i can skip it and then i'm going to hit enter and notice what happens when i do that it creates this list of dates for me based on the dates that are already inside of my data model okay so what calendar auto did is it created that list of dates all right the next thing i want to do is i want to build on some additional columns on top of this data set on top of this new table this calendar table by the way you'll notice that even though i named the table calendar it automatically creates my first column and names it date so table is called calendar column is called date and you'll also see that by the way if i go over to the far right you'll see that above me that i have a table called calendar and i have a column called date okay so that's what's created whenever we create that calendar auto function and i named the table calendar all right so what i'd like to do next is we're going to add a couple other columns in here i want to add in a column that returns back maybe the month and one that returns back the year and one that returns back maybe the month number and things like that in here so we're going to add in another new column you'll see there's another up in the table tools tab on the top we're going to select that we want to add in another new column here so i'm going to select the new column button from the table tools ribbon right here so if i select and click on new column it's going to create another new column for me and again if i go too fast for you in any section make sure you just hit the little back button in the youtube player and you'll be able to go back and watch again but i'm going to go ahead and name this new column year so to as a reminder whatever's the left of the equal sign is going to be the name of the object to the right of the equal sign is the definition of that object so i'm going to name this one year and then to the right of it i'm going to define it by using a dax expression known as year now this part is going to feel very familiar to those of you that are pretty comfortable with excel formulas so if you work with excel formulas and you've ever used the year function with inside of excel formulas it works basically the same so it's going to let me parse out the year from the date now i mentioned that not to make you think that all dax formulas are exactly the same as excel formulas but there are a lot of similarities with some of the formulas they're not exactly the same but there are some there are some similarities and that's done by design because the power bi team wants those of you that have that experience already in excel to feel somewhat comfortable as you start to work your way into dax it's not exactly the same there are going to be areas that get more complicated but there's a lot of similarities as well alright so i'm going to go ahead and type in and use the year function by hitting the tab key on the intellisense that will populate in the year function you can also just type it if you want and then you'll notice that the year function if i look at the template it's expecting for me to pass in my date column into the year function so if i want to pass in the the date column here's how you can do it there's a couple different ways there's some shortcuts so those of you that are already familiar with this show me a little grace here because you might already know some of this stuff i'm about to show you but when you go to type in a reference to another column with inside of your data model there's a couple different ways you can do it one way you can do it is you can refer to the table name first so i can type in and say hey bring back from my calendar table and tell it that i want to bring back my date column from the calendar table so if i hit tab on the intellisense that's the pop down here that shows me all my values it will allow me to return back my date column from my calendar table the other thing to be aware of though is when you're working with inside the calendar table already so i'm already in the calendar table if i'm already in the calendar table this reference to the table name is optional so i could remove this out of here and it will still work so just letting you know that you might have some best practices you like to follow where you like to keep the table name in there if that's you that's all good you can do that but do know that the table reference is optional when you're working with inside the table that you're creating the column which i am doing that in this case so i'm going to just tell it that i want to return back the year from my date column and i'll do a close parenthesis here and when i do that i'll hit enter and that will create this year column for me where i can see the year parsed out from the date kind of similar to what we did with inside the power query editor earlier remember and as i scroll down here you'll be able to see that the year does change as i scroll down to the next year you'll see changes to 2001 and it'll change in 2002 and 2003 where appropriate but that's how you can create an additional column on top of your in this case calculated table so this is all done using dax and again we're just going to be scratching the surface of dax here today there's a lot more in-depth ways that you can understand dax our training is a great way to get more in depth i definitely recommend looking at our on-demand introduction to dax and advanced tax courses all right we're going to add in another column we're going to add in two more columns the next one that we're going to add in we're going to go back up up to table tools again so work your way back up to table tools and then we're going to select new column again okay so table tools then new column okay when i select new column that's going to give me another blank column that we can start to define by writing our dax formula to tell it what we want to return back and in this case what i'd like to return back is the month number so let me go ahead and type in that as the name of my column i'm going to call this column month number and then to the right of the equal sign we're going to leverage a similar function to what we did earlier but not use the year function instead we're going to use a function called month now one thing that's kind of neat about dax is dax has all sorts of functions that tie into doing things like accounting and finance you'll see to next to me here that there's all kinds of uh like ending balance and opening balance there's all sorts of different types of functions i should say closing balance there's all sorts of different functions that you'll likely find be well that will be very useful to you as you get deeper and more in in depth into dax for this example though all i want is the simple month function i just want to highlight there's a lot of different ways that you can analyze your data by dates as you get further along in dax but i'm going to use the month function here i'll hit tab on my intellisense to bring back month and then this time i'm going to go ahead and bring back the date column again and then i'll do a close parenthesis all right so simple little function here just to parse out the month from the date now you might notice that i named it month number and the reason why i named this month number is because that's exactly what's going to return back it's going to return back the number of the month it's not returning back the name of the month so if you kind of look if i scroll down here you can see that it's returning back one two three four instead of january february march april but i really want both i want to have not only the month number but also the month name to return back so we're going to create one more calculated column hopefully you're hanging in there with me but we're going to create one more calculated column and i can do that by going up to the new column option right here again so we're going to select new column and in this new column this one we're going to call just month by itself okay and this one we're going to return back the name of the month so we're going to return back january february march april may so on and so forth okay now this function is a little bit different and it's actually a really cool function i think you'll like this one because what we're going to leverage in this function and matt i know you have a great resource for this one so if you want to get that handy and ready to share here shortly but what we're going to use in this function is what we're going to use in this formula is the format function and what the format function will allow us to do is to return back different formats of our dates and it's not just limited to dates you can actually do this with some other things as well but the format function will allow us to return back different formats of our dates so if we want to return back let's say the spelled out month name we can use the format function to do that so i'm going to select and use the format function it's going to prompt us to pass in a value into it and our value in this case is our date column so i'm going to pass in the date column into my format function and then we're going to hit a comma to tell it what kind of format that we want to use now matt has a great resource so it's a microsoft doc that has all of the different types of formats you can pass in but let me give you a few examples of what we can do here so matt if you can share that or have julia share that i'm going to pass in and show that i want to see the date spelled out so if i want to see the date spelled out i can do four lowercase m's like that in double quotes and close parentheses here and that will return back the spelled out month name so you can see here it's spelled out the month instead of just displaying the month number you can also change this around so that's using four lowercase m's but let's say i use three lowercase amps and by the way we're going to stick with the four lowercase ends one so just watch what i do after this what we're going to end up doing is what you see on the screen right now but i want to show you some of the other things you can do as well but if i do three lowercase m's this will bring back something like a month abbreviation and then you can start to get really creative check this out i can do three lowercase m's with something like four lowercase y's and that will bring me back a month abbreviation with a year pretty cool i like that you could also do something like this check this out if i wanted to instead let's say i wanted to bring back something like the day of the week i could do four lowercase ds and it'll bring back the day of the week so if i'm trying to figure out how well we did on the saturday and sunday versus weekdays there's how you can kind of build out to that you can also do other things here so if i wanted to do like three or so let's say two lowercase d's that'll give me something like the day of the month okay so that's pretty good as well but again in this scenario here what i'd like to see is i want to have the four lowercase m's to return back the month name all right so that's what we're trying to do for this scenario and that's it for our date table we created these four columns we use the format function in this one the format function here in this case return back a parsed out month name that we're leveraging and using for this and this is going to come back into play later the reason why i returned back the month number and the month name i'm kind of setting us up for something later that will hopefully make sense once we get to it you might be asking why do i need the month number and the month name there is a reason we're going to get to it here shortly all right so we're done in the data view nothing else i really need to do in the data view what we're going to flip over to now is we're going to go back over to the report view that's the one right here so follow me if you're following along go back and click on the report view to switch back over to where we designed the report and as a reminder if i went too fast during any of those sections feel free to rewind our youtube stream here and you can go back and see any parts that you might have missed all right but i'm going to go ahead and select the report view and from the report view we are going to start to design a few reports now uh i am purposely making a mistake but it's a common mistake that oftentimes folks that are new to power bi run into so i'm purposely making this mistake because what you'll find a lot of times when you go to build out your report visuals is they expose data modeling mistakes and so i want to show you the experience that you might actually have as you begin to build out your solutions so you know what to do when you run into these same problems that i'm about to run into okay so that's kind of the purpose why some of you might be like oh didn't you miss something i did i did it on purpose and i want to show you how to how you would realize that mistake is happening and then how to fix the mistake so like i said a lot of times when you're building out your report visuals those report visuals will expose issues with your data model and so what i want to show you here first is a report visual that will expose an issue with our data model so to do that i do need to highlight here a little bit on how you create report visuals to create a report visual you would select from the visualizations pane right above me you would select the visual that you want to use so say for example i want to leverage a column chart i can select the column chart this is one of the column charts this is the stack column chart i can select the stack column chart just by clicking the little icon here and that will bring the stack column chart on my design service you can see in the top left hand corner of my screen and you can grab that column chart move it around you can also resize it by grabbing and clicking the little anchor points so you'll see these little anchor points that are listed around the corners of in the middle as well of your design surface those little anchor points if you grab them that will allow you to resize your report visuals so if i grab this i can kind of grab it and resize it however i want now what i'd like to do is i'm going to grab this column chart and move to the right so when i'm zoomed in we can see what i'm doing at the same time you can also see the results so i'm going to go ahead and minimize my filters pane here and make it so you can really see this well as we start to work with it so if i wanted to bring in some fields into this new visual then i can go over to my field list on the right hand side let me take myself off the screen for a moment i can go to my fields list on the right hand side and i can start to drag and drop in the fields into this new column chart that we just added and again you can see the column chart is selected because you see those anchor points around it if you ever click in the background of your report power bi thinks that you want to create a new visual so anytime you're trying to edit the visual make sure you have it selected first to be able to actually edit it all right so let's go ahead and add a few new data points to this visual and a few new fields to this visual to add in some fields of this visual i'm going to go over to my fields list and i'm going to find my month column and i'm going to go ahead and add my month column to the axis and you'll notice that all i'm doing is i'm dragging and dropping i selected the column just by selecting the name of it and by the way you can also click the little checkbox next to it but i selected the column the month column and i dragged and dropped it into the axis section and that allows you to add the month column into a certain poor part of the visual that you've selected okay so grab the month column and drop it into the access section by the way if you had just clicked on the little check box that would do the same thing at least in this scenario all right the other thing i'd like to do is i want to bring in the essentially i want to see a count of the banks and i want to know how many banks have failed by month and so i'm going to grab the bank name column because i don't really have a metric right now but what i'd like to do is i want to do a count of all of the bank names that i find within my data set so account meaning count the banks that we have and so to do that i'm going to grab the bank name column and i'm going to drag and drop this one into the values section now the value section of your visuals and you'll see most of your visuals will have a value section this is where you're going to drag and drop in your metrics your metrics are almost always going to go into the value section so whatever it is you're trying to analyze this is why my report visual is blank right now it's blank because i haven't brought in any measure for it to actually be able to analyze so if we drag and drop the bank name and the value section right here that will produce a report visual that shows me my number of failed banks by month now we're running into a little bit of an issue here though and i'm sure many of you can probably figure out what is going you can feel free to put it in the chat if you want but this is the this is the issue that i mentioned that i purposely am running into if you notice right now every one of my months so notice april has 563 failed banks in august has the same number and december has the same number and february has the same number every one of my months has the exact same number of failed banks so there's something going on here and i'm sure many of you that have worked with power bi for some time realize that the issue is that i don't have any relationship to right now between these two tables so i have a calendar table and i have a failed banks table but i've yet to tell power bi how these two columns how these two tables are able to communicate with each other and that's why you're seeing this duplicated value across every single month because when you bring two to uh tables and and fields from two tables that don't have relationship this is how power bi is going to react and that's why i wanted to show this to you is so that you realize when you see something like this on your screen when you're working on your own solutions that your issue has likely to do with the relationships the other issue that i'm sure many of you are noticing is that this sort order of this column chart is not right either hold that thought we're going to come back to that problem that'll be problem number two that we need to solve that's also a data modeling problem but let's fix problem number one first problem number one again is that we have no relationship between these two tables we have a calendar table we have a failed banks table but we've yet to tell power bi how these two tables communicate with each other where we need to go to solve this problem it's a data modeling problem okay and the place we need to go to solve this data modeling problem is that model view sorry my zoom acted up there a little bit is the model view on the left hand side so you're going to select the little icon for the model view on the left hand side and that's going to take us to where we can define our relationship between these two tables so go ahead and work your way over that model view and when you work your way over the model you should see something that looks like this i'm going to zoom in on this a little bit so you can see this a lot better you have two tables that are disconnected from each other there's no relationship between either of the tables so you have no way for these two tables to communicate with each other okay so we need to define how does table one communicate with table two okay how does table a communicate with table b how does my calendar table communicate with our failed banks table i need to define the relationship between these two tables and the way that you can define that relationship is by simply dragging and dropping a relationship between the two tables so from your perspective you have to think you have to step back for a moment and look at the kind of data that you have in each table obviously my calendar table has date information in it so if i'm trying to figure out how does that relate to my failed banks table then i need to try and find which column inside my date table hint it's going to be closing date here i need to find which column from my my my failed banks table is going to be the basis of a relationship between the two tables we have here all right so if i want to define a relationship here you can do that by dragging and dropping the date column from your calendar table and drag and drop it on top of the closing date inside of your failed banks table and one question we oftentimes get is does it matter what order you drag it and can you drag closing date on top of date or can you drag date on top of closing date it actually does not matter what order you do it in if you uh power bi is smart enough to figure out which direction the relationship should go based on the data that you have with inside of your calendar here so i'm going to drag and drop failed bank's closing date on top of the date column inside of our calendar table matt from our team who's monitoring the chat says that we had a question about the auto date time making the hierarchy for us and why we would want to use our own date table if you need a uh filler for time you can okay i'm reading he's he's uh uh telling me if i need to fill time um so there was a question about the question here that came in is why not just use the date information that's provided to you so here's here's the question whenever we look at our failed banks table and let's look at this ju you guys hang tight here for a moment i'm going to come right back here but i need to show this from the report view so when you look at our failed banks table and actually it's gone now as soon as i went to create that relationship it left but many of you before we created that relationship likely saw that the closing date column already had a hierarchy that's part of it that you could leverage and use and so the question is why do i need to create this separate date table what benefit am i getting by creating this separate date table that we're looking at here there's a lot of benefits actually some of the benefits that you'll get from creating the separate date table i've already mentioned so just as a reminder of some of those i mentioned earlier if you need to do things like analyzing fiscal dates you gotta have a fiscal calendar stored somewhere and you'll traditionally do that with inside of a date table and a lot of you especially if you're looking at financial data need to analyze data over a fiscal calendar so that's one of the big reasons why people create separate calendar or date tables now you also have the ability now to analyze and look at things like how busy were we on a weekend versus a weekday how busy were we on holidays things like that by having just the regular closing date column by itself you do have the ability to kind of drill in from a year and go down to a quarter and go down to a day level you have a date hierarchy that's provided to you but it is lacking some other features that you get with inside power bi by creating the date table so it is helpful to have this date table okay because of the reasons i just listed there's a lot of others when it comes to time intelligence that you can do with inside dax there's some benefits as well but just keep that in mind there is a big benefit to having this date table as opposed to just only using the dates that are provided to you with inside of your your in this case failed banks table all right now we've created this relationship between our two tables so the relationship has now been created we can leverage and use this relationship in fact you might have noticed when i flip back over to the report view for a moment and you can go ahead and go back over there with me when i flipped back over to the report view and go ahead and do that with me now click on the report view when i go back over to the report view you may have noticed that our report visual was automatically fixed or at least partially fixed the part that was fixed was it now isn't bringing back the duplicate values over and over you're now seeing a uni a correct value returned back for each month so as i look at july i can see 74 values above me and october has 63 values it's not showing that 563 failed banks for every single month anymore so there's a lot of added benefit here now that we've created the relationship we're not just benefit it's more accurate data we're actually seeing accurate results return back so now that relationship has as soon as we define the relationship and we come back to our report view you'll immediately see the results shown here and you'll immediately see the correct numbers being returned back now let's address the second problem the first problem was we were lacking a relationship the second problem that needs to be addressed here is the sort order of our months right now our months are coming in here and it's it's actually changed the sort order since last we saw it if you remember last time we saw this it was sorting it alphabetically now it's sorting it by the number of failed banks so whichever number of failed banks is highest is on the left to the lowest number of fail banks on the right you can change the sort order on individual visuals by going up to the top right hand corner of the visual so if you look in the top right hand corner you'll see this little more options i uh three dots right here if you click on the more dot the more options you'll then see above me there's an option where you can change how the data is being sorted so right now it's sorting by the count of bank name meaning the more banks you have that's how it's going to sort so that's why you're seeing july 1st is because that's where the most number of failed banks are instead however we're going to sort by the month so we're going to change the sort order to sort by the month which will change things a little bit but now you'll notice that it's still not sorting properly the reason why it's not sorting properly still is one it's still sorting descending so it's starting sorting in a descending order we're going to switch that to a sending here so go ahead and switch again click on more options one more time here and say that you want to sort ascending instead of descending i like how it makes it the screen i'm looking at makes it look like i'm actually looking at the thing up there but you can see now it's sorted ascending but it's sorting alphabetically right that's not right we should not be sorting alphabetical months there's never a case where i want to see april before january as far as the the sort order of our months so that's the next thing that i need to fix i need to fix that yes we are sorting the months but rather than sorting it alphabetically we want to sort it chronologically or the typical way that we're used to sorting months by okay so this next step is another data modeling fix this is a an issue with my data model that needs to be fixed and once i fix it once it will carry over to every visual that uses the months in the future okay so to fix these the sort order here i'm going to choose and select my month column from my calendar table so we're going to go find my calendar table from our fields list above me and we're going to choose the month column so i'm going to go choose the month column right here when you choose it however make sure you choose the text of the column don't click on the check mark leave the check mark there click on the text for month to be able to select it and you'll notice whenever i have it selected it's got this gray rectangle around it that tells you that you have it selected and that means that i can now modify this column's settings okay so you're going to go over to the field list expand calendar and then select the name of the column that'd be month here with the month column selected again don't worry about the check mark that's not what we're trying to do you're trying to highlight the month column and with it highlighted you're going to then go up to the column tools i'm sorry yeah column tools ribbon we're in the right place and underneath column tools and by the way for some reason you don't see column tools that means you don't have a column selected so if for some reason column tools is not visible to you go back over to your fields list and just double check that you have month selected all right we do have it selected so let's go back over here to column tools and with inside column tools we're going to change this property right here that says sort by column and we're going to tell power bi that we want to display the month name but we want to sort by the month number so if we expand or hit the down arrow on sort by column we can tell that power bi that we want to sort our month column by the month number instead so by sorting by month number check out i'm going to go ahead and select this i'll tell you what i'll leave this on the screen here for a few moments but select month sort by column and choose month number this is why we brought in that month number column to begin with but if we select this it's going to update our data model and then look what happens immediately or depending on how fast your machine is it may take a few seconds but immediately we can see that now our months are sorting properly we can see january to december instead of april august december it's now sorting by the month number instead of the month name which gets you the results that you would expect to see in a visual like this as opposed to what it was doing by default so again one more time how did i do that i went up to and highlighted my month column okay so click on the text and then with it selected with that gray rectangle around it we went up to column tools and we selected sort by column and we told it we want to sort the month name by the month number so that way it would properly return back the order of the months and the way you would expect to see okay all right so that's it for that setting all right cool so now that we've gotten that taken care of i want to expose you a little bit more into some other small data modeling features that are helpful uh there are some that we're just not going to have time for today i recommend taking a look at our more in-depth classes to learn a lot more about different data modeling features that you can add the feature that i am going to highlight here as we come back is uh how to create dax measures okay what we did so far was we created a dax calculated column but now we're going to show a calculated measure and how you can create those there are a bunch of other data modeling features that we could implement here next we can create hierarchies we can create and hide columns if we wanted to uh you can create groups with inside your data model there's a bunch of other things that we could do obviously time is limited in the three hour span here so i would definitely recommend kind of exploring some of the other data modeling courses that we have at pragmatic works to get a little bit more in depth but what i'd like to do before we take our second break of the afternoon or of the session you might not be in the afternoon where you're at uh but what we want what i want to do before we take our next break is to show you a little bit of dax and we showed some dax earlier when it came to that calculated table that we created but i want to show you now a a way to create calculated measures with inside of your data model okay so to create new measures and again this is worth spending a few seconds discussing there are different types of dax functions that you can create and you can find them if you look underneath the modeling tab just watch this for a moment you'll find when you look underneath modeling that there are several different types of calculations that you can create alright so the different types of calculations you can create are new tables we already did that we created a calendar table when we created the calendar table we did a new table we also have already done new column that was when we added new columns to that table so every time we clicked on new column it allowed us to it allowed us to add another categorical column so think about what inside of our date our calendar table we created a month column we created the year column we created a month number column that was all done by creating new columns using this option right here these are all done using dax by the way so the data analysis expression language which again check check out our three hour recorded one we've already done on learn with the nerds focused on dax but you can learn a lot more about the uh dax language there so we've already created a new table we've already created a new column now we're going to spend some time talking about measures that's really both of these now the new measure option that you see here this option allows you to create and write a dax measure from scratch so let's spend 30 seconds here talking about measures versus columns okay measures you should think of as more your metric values okay so measures are going to be those aggregates that you have so measures are going to be things that you do like sums and mins and maxes and counts and count distincts these are going to be all of those typical aggregates that you work with now it doesn't mean that calculated columns can't be aggregates as well i'm kind of generalizing a little bit here and saying that hey measures are always going to be your more numeric metrics that you're trying to analyze there are some use cases where they could fall into the columns camp but generally speaking calculated measures this guy right here are going to be your metrics now calculated columns let's draw a little line here calculated columns on the other hand are going to be more of your category or categorical data this could also be more of your descriptive data or categorical data where it's more data that's describing your metrics so in our scenario we created calculated columns to return back the month name the year those are not metric columns those are obviously more descriptive columns they describe our metrics that we're going to have so that's kind of the understanding or what you should understand at least at a high level there is i'm generalizing here some you could break the rules of any generalization of course at any time but generally speaking calculated columns which are these are going to be more descriptive columns or categorical columns whereas measures are going to be more metrics so that's generally speaking the difference between the two now you might be wondering okay well that's great what's the what's quick measures so i have another thing here called quick measures what's quick measures what quick measures is is a a catalog of pre-built dax formulas where you can plug in your columns and power bi will automatically produce measures for you it's kind of cool it's worth taking a peek at i actually have a contribution in there as well you'll actually find my name in that section i contribute not to brag or anything but you'll be able to find a lot of pre-built calculations in there where you can as you're learning dax you can leverage those as starting points for building out your your measures or your metrics okay but generally it's kind of good to have this overview of what's the difference between the two this is the difference categorical data descriptive data versus more metric and aggregate data all right so we're going to create a new measure which means we're talking about a metric in some way and there are multiple places you can go to to create the metric i can go up to the modeling tab i'm in right now and i can create the measure here or if i go back over to my fields list on the right hand side i actually like to create the measures from here this is just my personal preference the reason i like to create them here is it ensures that the measure gets added to the correct table because there are some cases where your measure could get added to a different table that you didn't intend it to and so that's why i like to come to the fields list to add a new measure here so to add a new measure i'm going to right click on my failed banks table so i'm going to right click on failed banks and i'm going to take myself off camera for a moment and underneath the failed banks table when you right click on it so again i right clicked and then i'm going to select new measure okay so you're going to choose the new measure option here and when you choose new measure it's going to add to a new empty formula bar so i'm going to go over to the formula bar over here you'll see that we now have a new empty measure here that we can start to write our dax formula in so i'm going to zoom in on this a little bit here we go so you can see that a lot clearer and what i want to do in this really one measure we're going to do we're not going to do a ton of measures but in this one measure that we're going to create we're going to create a count of all of our failed banks now we could continue to leverage if you remember earlier we dragged and dropped in the bank name into our our visual and it automatically did a count on it that's what's known as an implicit measure however it's a better practice i'm not going to say best it's a better practice to do an explicit measure where you actively create a calculation here that defines how that data is going to be aggregated this is kind of the better practice here and so what we're going to do instead of just dragging bank name into every one of our visuals is we're going to create this new measure and we're going to name it to the left of the equal sign we're going to call this total banks let's try and rename the whole thing let's call this total banks okay and then to the right of the equal sign we're just going to do a count and there's a bunch of different counts that are available to you so as i start to type count you'll see there are a bunch of different counts that are an option here for me i can do count count a count x count blank count rows all account distinct or distinct count is actually listed as here uh and in this case i'm going to do a count rows count rows is going to count all of the rows i have in my table which will produce the result i need because every row with inside of this failed banks table is an individual failed bank so i'm going to do count rows here and then i'm going to tell it which one of the tables i only have two which one of the tables do i want to count the rows on and i'm going to tell it that i want to count the rows on my failed banks table and so again leverage the intellisense as you start to type you can then use the intellisense to bring back the failed banks table here all right i'll close a parenthesis here and then hit enter and that will create our first calculated measure that's going to return back a failed bank's count and i can find that count over in my list above my head here you'll see that there's a new little calculation here you can tell it's a new one because it's got the little calculator icon next to it above me and that new total bank's measure is now what i'll use as my metric that i bring into each one of my visuals and the right the great thing about creating these measures as opposed to just kind of dragging in bank name each time is now i can build them on top of each other i can take this total banks measure that we created and i can use it in all sorts of other measures that we create so if i want to nest this measure in other measures you can do that you can refer to one measure and another measure and so i can build them on top of each other and kind of have this building blocks of all these different measures that we want to create okay all right so i think we are due for another break so let's take one more five minute break for our session i know those are short but go ahead and get yourself a drink when we come back from this next five minute break we are going to go on to the last portion of the class which is going to be focused on data visualizations and we'll also spend a few moments talking about what do i do with this once i've our once i've created my reports how do i share it with others we're going to spend a few moments on that as well all right so i'll see everyone in five minutes and then when we come back we will continue forward and wrap up our session see you in five minutes all right welcome back for the last section of our class today hopefully you've been enjoying it so far let us know in the comments if you're enjoying the class i will see them later i am matt is doing a good job monitoring the chat but don't worry i will take a look at hopefully some good love uh later but i appreciate you guys joining us obviously like i mentioned a couple times now three hour class it's only so much we can go into certainly do check out as a reminder hopefully you don't feel like i'm pitching you too hard here but as a reminder make sure you check out our sale that we have going on on our on-demand learning that's 75 off you can find on our website go to our website enter in the code event 75 and oh i'm not sharing it let me share that on my screen here that would be helpful but using that code event 75 will allow you to get 75 off on our on demand learning that's an annual subscription so it does allow you to be able to watch our courses over the course of a year and a lot of more in-depth power bi courses 18 different power bi courses a lot of good stuff out there and again we just highlighted dax and data modeling we have two dax courses uh two different data modeling courses so we can definitely get you more in depth when it comes to some of the topics that we covered during the last hour so thank you for hanging in there with me let's start our last section of the day which is going to be focused in on data visualizations and so i'm going to go ahead and bring up on my screen and we'll flip over to the proper screen here so this next section is going to focus in on visualization creation we're also then going to start to talk about uh how do i publish and share this with others that section will be pretty brief so i'll let you know that in advance we do have some other classes that go much more in depth into that definitely take a look at that but we're going to focus in this section on data visualizations building out visuals and then we'll talk about how to publish and share this with others okay now we're doing that published section you may consider just watching during that publish section if you don't already have a power bi subscription consider that when we get to that later on now you should still be able to follow along through the majority of this next section but because what we're going to be doing in this next section is to start to build some visuals on top of this data model now we've already done one visual you can see that on our screen now but i'd like to continue to build out some additional visuals including maybe re-show you how i did that one earlier this morning or earlier in our session where i was able to return back the total number of failed banks in a bar chart so as a reminder as you are starting to work with and build out your visuals you should note that whenever you want to create a new visual you want to make sure you have selected somewhere in the background of your report at the moment i currently have this column chart selected again you can tell i have it selected because the little anchor points that appear around my visual and because those anchor points are around there power bi thinks that i'm trying to edit this visual so if you want to make sure you create a new visual click somewhere in the background of your report like so and then with nothing selected you can select the new visual you want and start to add the new fields to it you want so if i wanted to be able to display a bar chart that shows me my total number of failed banks by state then i can do that now i can come over to my visualizations pane right here and underneath the visualizations pane i can select something like the stacked bar chart and then choose which fields do i want to add to that bar chart so i'm going to go ahead and select stack bar chart here i'm going to remove my face from the screen for a few moments and i'll add the stack bar chart to the screen and with the stack bar chart selected by the way i know you don't see it on the left hand side of my screen but i do have it selected right now it's right here but with it selected we're going to add in some fields into it so i'm going to add into that stack bar chart i want to see my total banks so we're going to add in that new measure that we just created earlier called total banks and you can just check it off you can see it adds it into the value section remember we talked about the value section earlier and how the value section is where your metrics will land whenever you build out your visuals so whenever i just checked off total banks it knew that i was referring to a measure and that it should go in the values section but then on the axis in the legends areas we can define how else do we want to slice the data so if i want to for example see the number of failed banks by state then i could do something like select this state column here and you'll see that it will add it into the access where i can now view my total number of failed banks by state and so if we look on the left hand side here now i can shift this around resize it however i want and i can see my total number of failed banks by state now one additional thing that we could also do is i could slice this a couple different ways so if i wanted to i could add in let's say for example the year column this time don't check it what i want you to do instead this time is we're going to drag and drop the year column into the legend section right here so if i drag and drop year into the legend like so take a look at what we got now so now it's actually allowing us to slice not only by the state but also by the years where each of the failed banks have occurred now one other thing that's really powerful about power bi is that you do have the ability to have things like cross highlighting and cross filtering that automatically work with inside power bi so as i go to select and interact with these visuals so say for example i click on georgia notice that whenever i click on a particular data point with inside of my visuals that you have things like like cross highlighting and cross filtering that are automatically enabled that allow you to see the slice of the data that in this case is relative to georgia so i can go look at exactly how many fail banks i had in georgia in each one of these months because i highlighted or selected the state here the way i did that by the way is i actually clicked on the abbreviation and the axis on the side here and i can click on florida or illinois or i can multi-select and hold down control and select multiple states if i wanted to you can also hold down your you can also if you wanted to select individual slices with inside of the year so if i want to look at not just georgia but the but the year georgia 20 2009 i can select that specific year to see the sales for or not sales but the failed banks for georgia in the year 2009 now one of the things that's happening right now is the interaction between these two visuals is it's automatically doing this highlight you'll notice that it's highlighting a portion of the chart the rest of the chart here is in this darker blue that's where we have this cross section of values but then the rest of it is kind of this grayed out transparent almost view of the data now you have control over how that appears on your reports you actually can determine whether or not it highlights those results or just outright filters those results and so if you want to have some ability to customize that there is a feature that's found underneath the format tab right here and if you want to follow along with this part you can this part's kind of optional but if i go underneath the format tab i can select and by the way the format tab may not appear to you unless you select a visual so if you don't have a visual selected you won't see it click on a visual then you'll see the format tab so for some reason you're not seeing it that's that's why but with my bar chart selected i'm going to choose the format tab and you'll find there's this option on the top left called edit interactions right here and that edit interactions feature allows you to determine how each of your visuals are going to interact with each other so if i select edit interactions here what that does is it turns on a few settings that you can now switch to manipulate how these visuals will interact with each other so let me show you what i mean by that right now you can tell i have the column sorry the bar chart selected you can see the bar chart has the little anchor points around it that indicates that it is selected and the way that the edit interaction feature works is whichever visual you have selected you're actually configuring the other visuals so let me show you what i mean by that we have the bar chart selected but these little icons right here appear on my other visuals i only have one other one in this scenario and what this does is it determines what will happen when i click on things in my bar chart that's way over here so what no it's a little confusing but it's whatever visual you have selected you're actually going to choose and click on things on the other visuals on your screen to determine how they're going to interact with each other okay so with the bar chart selected i'm then going to choose and let's actually do this i'm going to click on georgia again so you can really tell the difference right now it's highlighting my values but you have these other options right here where you can filter your values so if i were to select filter notice what happens so check out what happens now now when i go to click on my states it's not highlighting anymore now it's doing a filter instead if i were to flip that back to highlight it goes back to that view that we had before so the point is you have flexibility to choose how that interaction occurs you get to determine do you want it to filter the data like that or do you want it to highlight the data like this you also have the option to choose if you want two visuals not to do any interaction between each other there's another option here called none and if i were to select the none option that would mean that whenever i go to select something in my bar chart it's going to do nothing to my column chart so you have the ability to choose what kind of interaction you want i frankly kind of like the highlight i think the highlight which was the default is kind of a neat way to view the data but it really depends on your business scenario which option you choose which is most appropriate for you so that's edit interactions that's a feature i wanted to highlight to make sure you know you have control over how each of your visuals interact with each other now the other thing to point out here is as you go to edit your visuals you can adjust the for the way the aesthetics of these visuals look you have the ability to change the font size the colors all of the aesthetics that go along with your visuals can be manipulated and modified by again making sure you have a visual selected so i have my my bar chart selected and then you can go over to this format section that's above me let me take myself off screen here again you can select this little format section right here see where the paint brush is the little paint roller that paint roller icon where it says format allows you to modify any one of the visuals that you have selected so if i were to select the column chart i would see settings for the column chart but with inside of here you can see there are all sorts of settings that you can turn on and tweak if you want to do something like turn on data labels you can turn on data labels here if i wanted to do something like add a background color to my visual i can do that if i wanted to add border to my visual i could do that if i want to change the title i can come in here and change the title of my visual so the point being is there's all sorts of different settings in here that you can manipulate there are because there are so many settings they have made it easier for you to find those settings by using this search capability right here so if i wanted to search for a particular setting say for example the font size i could search for something like size and this will show me all the settings that have the word size that are part of them so if i want to bump up the text size on my visual my legend i can bump that up some if i want to bump up the text size on my y-axis i can do that or i can go down to my x-axis and i can do that here as well so the idea here though is that you have the ability to adjust the settings on your visuals by going underneath the format paintbrush icon right here again you only are going to be able to do that once you have the visual selected and then you can use this nice little search capability to find the settings that you really care about all right so let me unzoom so you can see what happened when i made those changes you can see that by making those changes i've increased the font size but i've also added in data labels now so i can see exactly what the values are without me having to hover above each one to see them so that is called that's really the format section where you can add in data labels and things like that so some nice setting capabilities in there i could have adjusted the colors if i wanted to as well speaking of colors and speaking of aesthetics of your reports you also can create and design themes so think of a theme like if you if you're working within powerpoint you've likely leveraged a powerpoint theme before you are or in excel you have themes that you can set up on your tables where they follow a certain color pattern you can do the same thing with inside of power bi power bi has themes that you can either use or you can create your own themes if you wanted to to design a theme you would do that from the view menu that would be far up top here under the view menu so go ahead and feel free to follow along on this under the view menu there is a section devoted to themes and again the idea of a theme is you're basically trying to standardize the look and feel of your report so all sorts of aesthetic changes that you can make on your reports can be found underneath the view section and then more specifically underneath the themes section right here so you're going to go to view and then themes and you can expand the theme section here to see the different themes that are available to you so power bi has several pre-built themes that you'll find here that you can choose from there are several that you might find useful including ones like a colorblind safe theme that i'm highlighting right now or i'm hovering above right now but you can choose any of the ones that you want in here and as you select them you'll notice that it completely changes your report whenever you select them this one's pretty 1995 looking but that's okay this little swooshy thing i'm pretty sure this used to be a screen saver on my computer back in the 90s but you can choose from some of these themes that are already available or say you want to create your own theme so let's walk you through how you can create your own theme here if i expand the theme section here again i'm going to switch this back to the the default view that we had a moment ago so i'm going to flip this back to the default theme okay you can see it's back to normal here now so i chose this one just to go back to normal but now that i've gotten back to the normal theme i'm going to create my own theme and show some how you can make some modifications to the theme as you start to use it now to create your own theme you'll do that by going down to the customize current theme button that you'll see towards the bottom of my screen here on the right or on the left i should say and then i'll select cur customize current theme file or customize current theme so when i select this it's going to pop open on my screen the customize theme window let me take myself off screen for a moment underneath the customize current theme window here i can name the theme i can change the colors that are being used in the theme i can add borders to every one of my visuals backgrounds to every one of my visuals you can essentially standardize the way that your reports look and then you can save this theme file and then share it with others so that everyone within your organization continues to follow the same standards that you set when it comes to the report look and feel so i'm going to go ahead and name this theme we'll call this our learn with the nerds learn with the nerds come on there we go and then i can start to adjust some of the colors and i'm going to probably choose some pretty terrible colors i always tend to do that when i'm doing this theory ad hoc this is kind of like a mint mint green color here we'll select that we'll choose some other ones in here that are probably not going to be very good either there we go i'm just doing them kind of quickly here just to show you that you can customize these there we go alright so i've adjusted and changed some of the colors that are part of my theme you can also change things like the text if you want to change the font size that's being used for every one of your visuals you can come in and bump up the font size sub you can come change the font type that's being used if you want to be really saucy here you can make uh everything comic sans i won't do that to you but you could do that i'll come in and do ariel black for this one uh you can change the font colors that are being used as well i i actually a section i really like to adjust is the visualization section because i like to add a little background color to my visuals so i'm going to add into the background color maybe this little little salmon color here i'm going really really saucy here with the colors i'm going to go with a salmon color here to my background color for every one of my visuals and then i'm going to also add in a border so i can go under the border section here and if i'm doing by the way if i'm doing this a little fast don't forget you can rewind me and go take a look at what i did again but you don't have to get the exact same colors as me i'm just kind of picking them at random but i'm going to add a border to every one of my visuals as well so i'm going to go under the border section turn on the border and i can tell what color border i want i can also tell it that i want it to be a rounded edge border that's what radius does so if i want to have a rounded edge to my border i can actually change the radius property here to something like 20 and then that way whenever i go to look at this report and i click ok or apply on this new theme that we've created check out what happens and again i just picked a bunch of things at random so don't don't get too hung up on the specific settings that i changed my theme but i just made some changes and then now when i hit apply look at what it does it modifies my report to pick up all of the theme colors that i use the font colors i use and the font type that i used as well as things like the rounded edge borders so you can see all of my borders have rounded edges to them and i added a nice little background color i don't know if it's nice i shouldn't say nice but i added a background color to every one of my visuals here so the idea here with themes is that you can standardize the look and feel that you want for your report you can then save that theme so let me show you how you can save the theme file i would come back up over to my view and themes section here and then i would tell it down on the bottom that i want to save the current theme over here so if i select that i want to save the current theme it's going to save it as what's known as a json file which is basically storing all of the metadata for the report changes that we've made so i can i can save all those settings that i've made by hitting save current theme and you can see here that it saves it as a json file and then i can name this whatever i want and then share this file with any of my colleagues so if i go to share this with matt or julia or anyone else that i worked with that pragmatic works and then i give them the json file or the theme file doesn't really matter what the type of file is for the purposes of what we're doing but it's going to save the settings for this file for this uh the visuals into a file and then i can my colleagues can then take this json file that we just saved and i'll go ahead and hit save here oh i already have one oh let's switch this to a folder i have permissions to looks like it's trying to save it into an odd folder here we'll just save it here but then i can take that theme file and i'll share it with my colleagues and they can then import it into their solutions and then their solutions will pick up all the settings that i've set here so that's one of the big benefits of using theme files is you can kind of recycle this this these settings that you've defined so that your your colleagues don't have to go through and do the same set of steps again they can just kind of follow the standards that you've already set so if they're going to consume the theme file that we just created they can do that by going back up to view and back underneath themes and then we're going to go down to where it says browse for themes so when you see browse for themes that's where you'll actually select the theme file that someone else has created and then you can leverage and use that as part of your solution so i would have to go navigate to the file folder where i have that right here to be able to leverage and use that theme file and again i would need to mail this around or email this around to everyone or teams it to everyone that i want to have use the same theme file as me but then they can consume that and use that as part of their solution okay so that's theme files it's a great way to kind of standardize the look and feel of your reports as you start to begin to create more and more interesting reports now the next thing i want to show you is something that's a little bit more advanced this leans on that more of a pro feature than a beginner feature but i want to get you some exposure into some of these more advanced features even for those of you that are new to power bi and the reason why i think it's helpful to get into some of these more advanced features is because there's a lot of stuff with power bi that i am just not going to have time to show you in this short time frame so i want to expose you into some more of these a little bit more advanced features so you know that it's possible to do these things so that way whenever you are expanding on your knowledge base with inside power bi you can remember well didn't devin tell me that i could do something like this and so that's why i want to show you in this next section a really cool feature this is one of my favorite features i'm going to try i'm going to try and sneak in two quick report features here uh we'll see how we are on time here as we're getting to the last half hour of our session but i want to try and sneak in two features but at the very least we're definitely going to get in one of them and the first feature i want to show you is called report page tooltips now if you're not really familiar with what tool tips are let me let me first highlight what a tool tip is for you a tool tip is every time you go to hover above one of your data points anytime i go to hover up of a data point that's a tool tip so that little gray box that you see popping up that whenever i go to move around it changes its values based on whatever i'm hovering above that is a tooltip you'll see the tool tip over here as well whenever i go to hover above my column chart you'll see a tool tip there as well and so what i'd like to do is i want to customize this tool tip the tooltip that's here by default is fine but i want to customize it and make it my own this is one of my favorite power bi features that's why i always like to sneak it in there just to make sure you know hey that you can do something a lot cooler than the the default setting that we see here today so what we're going to do is we're going to customize that pop-up it's known as a tool tip and we're going to replace that default tool tip with one of our own making okay with one of our own our own design now the way that you do this is you need to create a second report page and everything that we've done so far has been inclusive with inside of this one report page but you should know if you look at the bottom of your screen that you can have more than one page to this report so right now we've been developing one report but if we want to create additional pages to this report we would click the little plus sign down here kind of similar to like what you have in excel whenever you create additional excel spreadsheets so what i want to do is i'm going to add in another report page down to the bottom and it probably makes sense to name these right now i haven't really named them something something very appropriate so i'm going to rename the first page you can rename them by right-clicking and selecting rename or you can double-click on them and you can rename them that way as well i'm going to go ahead and call this my report summary and then on page number two i'm going to go ahead and rename that one we'll call this our report page tooltip okay so i've given these two different report pages names here and the other thing that i'm going to do that's kind of unique is i'm going to hide this second page you can hide report pages by right clicking on a report page and selecting hide page and i'm going to explain a little bit more later on why we're going to do this but i'm going to do it now and then you'll see the you'll see the reason why we're doing it as we move along to this example so i'm going to go ahead and right click on report page tool tip that's the name of that my page here and i'm going to select hide page and you can see when i do that it adds a little i with a line through it indicator telling you there that it's been hidden all right so i'll explain more about why we're hiding it as we move along to this example now inside this new report page make sure you have if you're following along select the report page tooltip page that's the new page that we just created and we're going to make a few settings changes on this page before we even bring any visual onto our design surface we're going to make a few settings changes to the design surface of this report so to do that we're going to work our way over to the settings that's the little paint brush icon that we've looked at the format settings that we've looked at a few times now but we're going to go click on the format settings of this report page so i'm going to go ahead and select that here and then you'll notice that there's a few settings that you can turn on or tweak specific to the overall page of the report you're looking at so you have things like page information page size page background page alignment all sorts of things that you can set here and the first setting that i want you to modify here with me is the one called page information so we're going to go ahead and expand that and we're going to flip this switch that's called tool tip now i'm going to show you several settings here and this might be an area where you need to rewind me as we go a little bit further on because we're going to flip probably four or five different settings here to make this work and so you might need to rewind me in the in the youtube player to be able to catch up on some of the settings i'm gonna do but the first setting that we have to do this is a mandatory one for this example is we're going to flip on tool tip the next couple settings that i'm going to show you are going to be optional but what this feature did when we flipped this on is it made it so that the page that we're looking at right now this one that you see on the screen right now can be turned into a tool tip to replace the default tool tips so the first thing that we had to do was underneath page information is we had to flip this on to be available as a tool tip okay if this was turned off then we could never use it as a tool tip so we turned that on as a tool tip and then the next setting that we're going to do the next one that i'm going to have you do is more of an optional thing but i definitely recommend you do it is i recommend you change the page size and here's why you you guys stay here let me kind of show you the reason why we want to change the page size but you guys say right here the reason why we want to change the page size is because if i don't change the page size whenever we have the new tool tip that we're creating pop up it's going to be the same size as the report that we're looking at so that little gray box that you see right now that's popped up it's going to be huge if we don't change the size of our report page so that's why we're going to change the size because we want it to be a more appropriate pop-up size otherwise it's going to be something that's huge that 16x9 what we see right now on our screen is way too big to be a pop-up so do this with me we're going to modify the page size and we're going to change this from 16x9 to instead use the tooltip size that's kind of built in here you could customize the size by the way if you select custom you could make your own size here if you want for the page size but instead i'm going to make this a tool tip size okay and then the final little little setting that we're gonna do here is i'm gonna change the page background instead of being a white background because it doesn't really stand out if i have a pop-up that has a white background on top of another pop-up that's a white background i'm going to instead change this to more of a gray color and i'll just make this a light gray here on the left okay now you'll notice that the page color doesn't change at all for some reason power bi has transparency set to 100 and so i'm going to knock this down to zero percent so we can actually see that gray color that count comes through all right so just a quick repeat the three settings that we changed is the page information we turned on to tool tip is on the page size which we changed to the tool tip size and then the page background we made it a light gray and we made the transparency zero percent instead of a hundred percent all right now with that done we're going to start to build in a few report visuals all right so the report visuals that i want to bring in are going to be one a card visual you can find the card visual available right here that's the card visual and we're going to go ahead and select the card visual to add it to our design surface okay so go ahead and select that one i'm pointing at right here all right i'll put myself back on screen for a little bit so we'll select the card visual here and you're going to want to resize this because the card visual is going to take up way too much space here to begin with so you'll want to bump this down in size quite a bit and then kind of re-align it so that it doesn't take up so much space i'm going to move it to the left hand side here a little bit and then i'm going to go ahead and add in the total banks measure that's the measure that we created earlier i'm going to drag and drop or you can go ahead and check the total bank's measure to add it into that card visual that we just added so again the steps that we did here is we added a card visual right here and then now we're going to add in the total bank's measure into that new card visual that we just created all right so i'm going to go ahead and do that now you can see here the font size is way too big part of that's because the theme file that we created the theme file i created i made the fonts too big so i could go through and align that i could change the font size just for this one visual if i wanted to for interest of time just note that if i wanted to change the font size i could uh but i'm not going to do that because i want to kind of keep moving forward and keep making some progress here the other thing that i want to do though is i'm going to add in another visual to the right of the card visual and what i want to add to the right of the card visual is something that will show me let's say for example a list maybe even a map that highlights all of the failed banks that we have i want to be able to see whenever i go to hover above florida or georgia i want to see a map pop up to show me where those failed banks are occurring so to add a map power bi does have several maps that are available to you but make sure you first click somewhere in the background of your report so i'm going to click somewhere in the background of the report so it doesn't think i'm trying to edit the card visual now with the background of my report selected i'm going to choose the standard map visual that you see right here okay and that's going to be the visual we use on our screen do note that when you select this it's going to be really really large and you're going to have to resize it again the reason why it's so large is because remember we changed our page size for this this uh tool tip to be way smaller that's why everything's appearing so large whenever we add it so i'm going to add in the map visual oh actually it didn't make it too large that was actually a nice uh nice space that it took up here but i will make it a little taller now if it made it really large for you just resize it kind of grab the corners and resize it until it's a more appropriate size like we see here but with inside of this new map i'm going to bring in our total banks measure so i'm going to go ahead and select total banks here and bring that into our size section down here and you can just check it and it'll bring it into that size section and then i also want to bring in our city state column to be our location so i'm going to drag and drop city state into the location section of the report okay now you'll notice when i do this at first nothing works nothing happens there's no data points that are showing on my map it looks like something's wrong and what we've actually uncovered is a data modeling issue remember i told you a lot of times when you're building your report visuals you'll be able to expose and see data modeling issues as you build out your visuals this is another data modeling issue that we've just uncovered i do want to show you real quickly here the settings for this visual so again you'll see that we have total banks and then we have location is our city state so make sure you have those two in there but you'll again after you bring and match your visual to look like mine you'll notice that my map is still showing as blank the reason for that is there is a setting that we need to turn on on our city state column so to fix this issue with me select the city state column from your field list so i'm going to choose city state like so remember like how i showed you earlier where we uh modified the month sort remember we changed the month order the sort order this is kind of similar to that you're going to select the city state column and make sure you see that little gray box around it as you do it so you can see this gray rectangle around it make sure you see it selected like this and then once you have it selected we're then going to go up to the top part of our screen to look for column tools again we did this earlier last time when we did it we were messing with the sort order this time we're messing with a different property called data category again feel free to rewind me if i if i lose you a little bit just rewind me a tad and hopefully that'll catch you up all right but the data category here what i want to you'll notice right now is set to uncategorized and this data category is particularly helpful for you when you are dealing with geography data and that's what we're doing when we go to bring data into a map and so what i'd like to do is i'm going to set the data category to be configured as a place for my city state column now you might consider making this the city value here you might consider making this state but in actuality it needs to be place and the reason why you're going to use place is because if you remember what our field looks like our field is actually a concatenation or a combination of merged columns of city and state together and so that's why we're going to use the place setting here to be able to return back the value properly so go ahead and select place here for our data category and then when we do that check out what happens now we can see all of our data points starting to appear on our map now our map is particularly small here it's kind of hard to see what's going on with our map we may even want to turn off the title for example to make it so we can see it a little better but keep in mind this is going to eventually be a pop-up as part of our or a tool tip that's part of our report summary okay so you again you could turn off the title if you want i'm going to do that real quickly i'm going to make the the this is optional guys don't worry about following me on this one i'm going to select the map and go over to the format settings over here like we've been doing take myself off screen for a moment and you'll see i could optionally turn off the title if i want that way we just get a little bit more real estate so if i flip title off notice it gives me a little bit more real estate over here that's totally optional don't get too hung up on that if you miss me on that one that one that one's not worth rewinding all right so we've got the tooltip looking pretty good but there's one more setting that we need to do to finalize this and that one last setting that we need to to change is going to be found underneath the fields list right here so go back over to the fields list move if you if you might already be looking at that but i'm not looking at that right now because i turned off the title so i'm going to go back over to my fields list you may again already be looking at that but i'm going to match you and look at that as well and the final thing that we need to do has to do with this tool tip area down here on the bottom this bottom area is where we're going to define how does my tool tip get launched so how does my tooltip know to get launched and what you're going to do is you're going to bring in a field from your field list somewhere up here you're going to bring in a field that you want to pass into the tool tip this is basically like a parameter this allows you to pass in a value from one report to another report to be able to not only not only to display the tooltip but also to filter the tooltip based on whatever value you are hovering above now there's a couple different fields that we could leverage here we could bring in the state column if we want we could bring in the state column here and say hey whatever state we're hovering above go ahead and filter to that state only you could also bring in your measure you could bring in total banks either one of these would work you can bring in either one of these fields and drag and drop them into the tooltip section to be able to see the tooltip in action i'm going to bring in the state column for this example just to kind of show you what this looks like and so i'm going to drag and drop the state column down into the tooltip area okay i know there's a lot of settings for this one guys so hang in there with me on this one because this one has a big payoff at the end but we're going to drag and drop the state column from your field list down into the tool tip area okay so you're dragging and dropping state down into here once you've done that you're done and we're going to be able to see the big payoff here next so let's take a look at the payoff punch here or the payoff pitch i'll go with it's baseball season let's go with pitches so let's check take a look at what this did for us now that we've created this tool tip we're going to go back over to our report summary that's our first report page so we're going to select our first report page and take a look at what happens now if we go to hover above our bar chart that we have in the top left look at what's happening here i'm going to zoom in on this for you notice now we replaced our standard old tooltip we had before with this new hotness i'm going to go with that this new hotness this new kind of tool tip this is the report page tool tip where we actually told power bi to use a tool tip that we want instead of the one that you give us by default so really powerful setting here again you can hover over any one of the values you can even go down to missouri here if you want and it'll show you exactly the values that you have for whatever area you hover above so super powerful things from a data visualization standpoint is really impactful allows your users to be able to interact with and see their data from a different perspective so yeah it's great that i can see this from a bar chart but i'd like to see this a little bit more detail well now you can do something like a tool tip to get a little bit more of a granular view of your data super powerful stuff highly recommend learning more about this i'm obviously showing you this in a very short time frame but there's a lot more a lot more data story telling type things that you can do there's things like drill throughs you can do there's report page tool tips that we just showed here there are all sorts of different things that you can do to really make your reports pop so i recommend we we have a data storytelling class with power bi that's part of our on-demand learning i highly recommend you take a peep at that that is a very popular course that really digs deeper into understanding how to build out reports that tell stories and aren't just a static view of data it's great that you can create reports but what can your users do with that data after they've viewed it are they able to ask more questions are they able to dig in deeper what can they do with the data after you provided it to them the last thing i mentioned there was two things i wanted to show you this was one of them the second thing that i want to show you i have maybe two minutes to highlight here before we have to shift over to talking about sharing our solutions so the last thing i'll kind of highlight here is a feature known as q a now q a stands for question and answer as you might guess and what q a allows you to do is to really kind of interact with your reports and your data model more specifically to ask questions about your data so what q a is kind of an analogy for it's kind of like having a search engine like a google or a bing it's like being able to google data against your data set so to give you a little example of how this works there is a q a visual you can find the q a visual right here okay that's one way you can launch q a the other way you can launch q a is if you just double click in the background of your reports if i find some white area white white background area of my report and i double click here this will automatically launch q a for me and i'm going to show you a very brief one minute view of this because it is a really cool feature i think you'll like uh and you can explore it a lot more and from some of our other classes as well but what i'm going to do is i'm going to ask it prompts me it says ask a question about your data and so i can type in here i can say hey i want to know what were my total banks tell me show me the total banks we had fail so just like that q a will produce a visual that shows me the total number of failed banks it produces a card visual similar to what we did before all right that's interesting but i want to see total banks let's say between 2015 and 2020 now q a is pretty smart whenever you go to type in things like a between q a realizes that you are trying to filter your data and so you can type in and search and ask a question and get it to return back the correct results based on how you ask it it will interpret a filter or a way of visualizing the data or a way of being able to present the data in different forms so here i said between 2015 and 2020 it knew that that meant i wanted to filter the data you can also tell it that hey i want to get a little bit more specific and let's say i want to see total banks by city state between 2015 and 2016. let's actually remember my column is called city state with an st here it's not spelled out but i'm going to say total banks by city state between 2015 and 2020 and right now you can see it's produced a map visual for me so automatically it produced a map visual the reason why it produced this as a map visual by the way was because of that data category setting that we did earlier remember we did that data category setting where we told power bi that the city state column was a place that's where that comes into play why it automatically visualizes this as a map now you all can also can control what visual produces so i can say hey this is great but i want to see this as a let's say as a let's go tree map trying to think of a different visual we could choose and when i say hey show this to me as a tree map you'll notice that it produces a tree map visual for me that shows me a breakdown of all of my failed banks by city state and if i wanted to switch this instead of saying by city state to just by state i can do that as well and see the breakdown of all of my failed banks by state so you have the ability to kind of customize things as you go you can leave the search bar here if you want or you also have the option here where you can make this and turn this into a standard visual if i click on this icon right here that will turn it into a standard visual the q a search will go away and it just makes it a standard visual at that point so a really powerful feature i definitely recommend expanding and learning more about how q a works there's even another feature called synonyms which allows you to prepare for if your users type in a column name different than what you're expecting them to there's some settings in here that allow you to fine-tune the interaction with q a there's a whole bunch of settings that you can find whenever you have q a launched as well to be able to show that interaction by the way you'll notice my report page tooltip is still working here so pretty cool stuff that you can set all right so here's how we're going to wrap up the last few minutes that we have of our day is we're going to take the solution that we've been developing and we're going to publish this to the power bi service so that we can share it with others now this last eight to nine minutes of our day uh not everyone's going to be able to follow along because this is the portions of this are going to assume that you have a power bi account so if you don't have a power bi account ready to go for today no worries just watch for this last few minutes we only have a few minutes left anyway so don't stress out too much about it but what i'd like to do is i want to take this report that we've been designing and i want to publish it to the power bi service this is that fourth step of that power bi uh end to end view that we talked about before remember this guy from the very beginning of our day we're on to that fourth step it's above my head where we talk about how do i share the results we've already talked about these first three stages here let me point the right direction those first three stages we've already talked about now we're on to that fourth stage which is a pretty quick stage that's why i don't have a ton of time devoted to it but we're going to take the solution we've designed share it with others it looks like i have the word data refreshed there twice i don't know why i duplicated that but data refresh can be done here security can be done here um sharing can be done here there's all sorts of things that we can do once we have our solution published to the cloud that's the cloud part of power bi which is the power bi service so to get started with this i'm going to click the publish button which you see on the top of my screen right here and again feel free just to watch during this portion of the class but i'm going to click the publish button in the top part of my home menu this is the home menu you'll see the publish option that i'm highlighting above me right now when you click the publish button you may be prompted to sign in and it's prompting you to sign into your power bi account which is likely also your office 365 or i guess they call it microsoft 365 now i am already signed in you can see right here that i'm already signed into my account so it's not going to prompt me to sign into my office 365 account like it might prompt you so just be aware of that that if you do get prompted to sign in that's because you do need to sign in to be able to publish this to your power bi tenant all right so i'm going to go ahead and click the publish button here and when i do it's going to first prompt me to save my work that i've done to my local workstation so you do need to save a local copy of your work before you go to publish it to the cloud so i'm going to go ahead and hit save and i'll go ahead and save this as my learn with the nerds file and i'll just hit save this is saving as a pbix file by the way that's the file extension that power bi uses so i'm going to go ahead and hit save that saved a local copy of my work to my workstation and then it's going to prompt me to choose a workspace to publish to now a workspace may be a totally new word to you if you're new to power bi but a workspace you can basically think of as a container that you publish or deploy your power bi stuff to okay so as you're going to share your solutions with others you need to publish to a workspace now for today's purpose for the purpose of today's class i know we had a limited time i already created a workspace ahead of time called learn with the nerds you can see it right here i did a quick search and found it but you likely will not have that workspace but you likely do have a workspace called my workspace this is if you have a power bi account everyone has a my workspace that is specific to you that is a kind of a testing ground to test out your solutions that you've created you publish them to your my workspace you can go view them from the web browser then and test them out and see what they look like from the web browser however you're not going to generally be sharing your solutions from your my workspace your my workspace again is like your own personal repository your own personal testing ground but when you want to share your solutions with others you're going to need to create a separate workspace from your my workspace like i have done here i've created one called learn with the nerds and i'll show you how to create one very briefly as we move forward but i'm going to go ahead and select this learn with the nerds workspace and then hit select to publish to this workspace that i created okay and that'll take a few moments here it'll take just a few seconds actually but once it's done we'll be able to actually go look at that solution from the web browser and now it's already done and if i click on the option here that says open learn with the nerds in power bi if i were to click on that right here it will launch open my web browser for me and allow me to view my solution now from the cloud okay so i'm going to go ahead and select open learn with the nerds pbix in power bi and when i select that it's going to launch let me move my youtube stream out of the way here that's going to launch nope i'm not signed in the wrong the right account let me go ahead and sign into the right account here i'm signed in to brian right now i could really pay be i could be really mean to him and mess with brian here a little bit but let me not do that let me go ahead and sign in to power bi under my account all right so i'm going to sign in and i'm going to sign in as devin this time okay and let's type in my password guys i thought i was already signed in ah and now i got to do my two factor authentication let me make sure i do that sorry it's asking me to log in from my phone now there we go we should be good now glad i had my phone near me all right so now i can see our power bi solution that we designed right here you can see learn with the nerds and by the way if i had clicked on this from the the power bi desktop if i click on this again it'll take me straight to that solution in fact it opened up in a different web browser let me bring it over here but here is where it normally would take me if i had clicked on that from the web browser it would take me immediately to view my report from the web browser now we're on to our last few moments here of class so i do want to highlight a couple key things here we've published our solution to the power bi service now we can view our solution we can interact with it you can see all that same interaction we had before is still here with inside the web browser we also have the ability to still see our tooltips work all of that stuff is still here everything still works but now from the web browser now the next step that i would like to do is share with others now there's multiple ways you can share you can share an individual report you can share dashboards you can share workspaces you can share what's known as a power bi app unfortunately we're not going to have the time to get into the nitty-gritty of each of those but where you can share your solution you'll find the share option right here and so i can tell it who do i want to share with ideally you share to a group of users rather than individual users so this does tap into your azure active directory and when it taps into your azure active directory you can choose do i want to share this with individual users or do i want to share this with a active directory group of users so you can leverage the sharing capability here to to decide who do you want to share this solution with so if i select share report on the right hand side of my screen here now you'll see where i can actually choose who do i want to share with okay oops let me zoom out all right so that's one mechanism for sharing that you can do all right the other thing that i want to highlight is you can also share through what's known as power bi apps that's the power bi apps are kind of generally the more recommended way of sharing power bi apps can be shared at when you select the workspace so i can select the workspace name here and you can see in the top right hand corner you can create an app that's another way that you can share it's kind of a different methodology on how you share this is the preferred way of sharing we'd have to get a little bit we'd need another half hour to get super deep into that i also told you i show you where you can create new workspaces you can create new workspaces by expanding the workspaces section right here so you can see workspaces right here if i expand workspaces like so towards the bottom of my screen you can see where you can create a workspace on the bottom down here if you select create workspace that's basically creating a new container where you can store your power bi collateral okay so the last thing i want to highlight here obviously we didn't have a ton of time to talk about the power bi service take a look at some of our other training we go much more in depth on to every element of what we talked about today but the last thing i want to highlight here for you is where you can schedule the data to refresh within your data set so you can see we have two objects here we have a report and a data set that are separate objects that we just published if you want to refresh your data you would select this little icon i'm pointing at right now which would allow you to set up a scheduled refresh and if i were to select that it would take me to a section here where i can set up a schedule refresh i can turn it on and then i can determine how frequently it is that i want to refresh my data so this is the area i want to make sure i point you in the right direction at least where you would go to make sure you can set up scheduled refreshes of your data set because otherwise i have kind of a static view of my data and my users are looking at old data that i haven't actually updated on a regular basis so that's what the scheduled refresh area does is it ensures that your users are always looking at up-to-date data that as they look at their solutions okay all right what a whirlwind right a lot to cover today hopefully you got something out of this feel free to go back and watch elements of the class again rewind go back and watch again don't forget that again we do have that special sale going on right now where you can get 75 percent off of our on-demand learning that is going on through next tuesday through the end of next tuesday you can get that 75 off uh enter in the code event 75 when you go into our shopping cart and that will give you that additional discount i'm sure we will send an email with some follow-up on that as well if you're still interested and haven't uh wrote written that down in any way i also want to let you know this is not the only or last learn with the nerds that we're going to be doing uh learn with the nerds is an event that we've been doing for several months now about half a year i think now at this point and you can find our other streams that we've done in the past that are still recorded we've done some on power apps we've done some on power bi we've done some on power virtual agents they can all be found on our youtube channel so make sure you take out take a look and subscribe to our youtube channel to see any of these upcoming events that we're going to be doing including we're going to be doing a an event which i'm sure julia from my team is posting in the chat already if she hasn't already uh that's going to be focused in on power bi data flows so power bi data flows is not something we talked about today but we're going to have a learn within earns in june that focuses in on power bi data flows so here's your two sentences on what power bi data flows are power bi data flows are all of that data cleansing power query stuff that we did this morning that was in our first hour of the day but done from the web browser so you can actually you don't need the power bi desktop you can create data flows and data cleansing processes from the web browser uh using power bi data flows from the power bi service so look for that coming up that's going to be in june we usually kind of do these every other month so look in june for that next upcoming learn with the nerds i want to thank everyone for joining us uh hopefully you enjoyed today's class i of course enjoyed as always we love teaching power bi uh but let us uh follow up with you in some other way as well see how we might be able to help you uh whether that be through hackathon or on-demand learning or maybe it's even a private training of course let us know we love to help you out and thank you so much for joining today and enjoy the rest of your day appreciate it everybody thank you you

Channel: Pragmatic Works

Views: 339,474

Keywords: power bi, microsoft, dax, report, excel, data, dashboard, bi, training, pragmaticworks, live event, beginner to power bi, power bi beginner to pro, intro to power bi, power bi tutorial, power bi beginner, new to power bi, power bi full course, how to use power bi, power bi demo, introduction to power bi

Id: 5X5LWcLtkzg

Length: 185min 45sec (11145 seconds)

Published: Thu Apr 15 2021