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

Summary

Highlights

• Instructor Devin Knight offers a lively introduction to Power BI, ensuring you get off on the right track with this powerful tool. 💡
• Learn to use Power BI Desktop effectively by connecting to data, transforming it, and preparing it for storytelling. 🖥️
• Discover the importance of building a solid data model, helping your reports stay accurate and dynamic. 🍰
• See the advantage of creating powerful visualizations that communicate your data's story clearly. 📊
• Engage with advanced features like DAX, report themes, and custom visuals to wow your audience. 🌟

Key Takeaways

• Power BI is an evolving tool great for both beginners and seasoned users looking to refresh their skills. 🚀
• It's crucial to use Power BI's built-in tools like Power Query for data cleansing before visualizing data. 🧹
• Building a solid data model is essential and can significantly influence the accuracy and usability of your reports. 🏗️
• Creating custom visuals and themes can enhance reports, making them more engaging and user-friendly. 🎨
• Understanding DAX can take your Power BI skills to a whole new level, unlocking powerful analytical potential. 💪

Overview

Chapters

• 00:00 - 90:00: Introduction to Power BI In the introductory chapter titled 'Introduction to Power BI', the instructor Devin Knight introduces himself and the class. The chapter sets the stage for the Power BI course, giving students an overview of what to expect and preparing them for the learning journey ahead.
• 90:00 - 180:00: Breaking Down Power BI Phases The chapter 'Breaking Down Power BI Phases' introduces the course 'beginner to Pro', which is updated for 2023. The instructors have previously taught this class almost two years ago, highlighting the importance of refreshing one's knowledge as technology evolves. This course aims to provide an updated and refreshed perspective on Power BI, acknowledging the changes that have occurred over time. The chapter mentions the popularity of their YouTube channel, where the previous course video is featured, emphasizing their excitement to present a modernized version of this content.
• 180:00 - 270:00: Data Discovery and Power Query The chapter 'Data Discovery and Power Query' involves a class session featuring Devon and Manuel Kintana, where they guide through the topic of data discovery and the use of Power Query. Manuel participates actively by answering questions in the chat to aid the learners. The session promises an engaging and informative experience for attendees.
• 270:00 - 370:00: Data Modeling and Date Table The chapter introduces a beginner-level PowerBI course titled 'Data Modeling and Date Table.' This course is designed for newcomers to PowerBI, and the instructor promises a comprehensive overview of the basics, with no prior experience assumed. The chapter emphasizes the aim of making participants comfortable with initial PowerBI use, setting the foundation for more advanced learning in the future.
• 370:00 - 460:00: Data Visualization and Themes The chapter introduces the topic of data visualization in Power BI, aiming to cater to both beginners and those who have some experience with the tool. The instructor expresses a desire to share useful resources and tips that can enhance the learning experience for all participants. The importance of having certain resources ready for the class is emphasized, indicating that they are essential for following along with the lesson. The chapter begins with the instructor preparing to share their screen to highlight necessary resources for the class.
• 460:00 - 540:00: Report Page Tooltips and Navigation This chapter discusses the Power BI Desktop, a Windows application used to design data solutions for publishing and sharing in the cloud. Unfortunately, it's not available for Mac OS users.
• 540:00 - 595:00: Publishing to Power BI Service This chapter focuses on the prerequisites and initial steps required to publish a report to the Power BI Service. It covers the requirement of having a Windows operating system to install Power BI Desktop, with alternatives such as using Parallels or virtual machines on Mac systems. The chapter also mentions class files that are not essential for understanding the content of this lecture but are available for download.

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

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