Microsoft Excel: Master Power BI Dashboards in 120 Minutes! | Bash (BizTech Matters) | Skillshare

Microsoft Excel: Master Power BI Dashboards in 120 Minutes!

Bash (BizTech Matters), Online Teaching Excel Expert

Play Speed
  • 0.5x
  • 1x (Normal)
  • 1.25x
  • 1.5x
  • 2x
33 Lessons (2h 9m) View My Notes
    • 1. Welcome to the Power BI Course!

      0:31
    • 2. Excel Power BI Introduction

      3:26
    • 3. Power BI High Level Flow

      2:20
    • 4. Excel Power Query Introduction

      4:43
    • 5. Excel Power Pivot Introduction

      1:03
    • 6. Excel Power View Introduction

      1:34
    • 7. Install Power BI and Sign Up

      2:44
    • 8. The Big Picture

      1:40
    • 9. Power BI - Get Data

      6:43
    • 10. Power BI - How to Get More Data

      1:03
    • 11. Power BI - Modelling Data

      3:00
    • 12. Power BI - Adding Relationships Manually

      2:06
    • 13. Power BI - Visualization

      4:35
    • 14. Power BI - Visualization Customization

      1:25
    • 15. Power BI - More Visualizations

      2:24
    • 16. Power BI - Visualization Format and Analytics

      2:56
    • 17. Power BI - Ask a Question

      2:07
    • 18. Advanced - Real World Example

      5:44
    • 19. Advanced - Get Data

      13:03
    • 20. Advanced - Modelling Data

      5:06
    • 21. Advanced - Visualization with multiple charts

      11:14
    • 22. Advanced - Publishing

      4:34
    • 23. Advanced - Power BI Website

      8:22
    • 24. Your Project - Create your own Dashboard!

      1:08
    • 25. Advanced Tips - Introduction on the Data Setup

      2:22
    • 26. Advanced Tips - Using Bookmarks to Save Space

      9:30
    • 27. Advanced Tips - Drillthrough

      4:24
    • 28. Advanced Tips - Date Hierarchy

      4:45
    • 29. Advanced Tips - Forecasting

      5:12
    • 30. Advanced Tips - Word Wrap, Mini Map and Advanced Editor Options

      2:38
    • 31. Advanced Tips - Format Painter

      1:51
    • 32. Advanced Tips - Bookmarks and Filters

      3:54
    • 33. Thank You!

      1:07
31 students are watching this class

About This Class

Last time you opened an Excel Power BI Dashboard and are overwhelmed by the number of things to do. You don't know how to make the best use of your time.

But it doesn't have to be this way!

You Will Walk Away With...

  • MORE TIME!
  • Create your own Power BI Dashboard from scratch in just 120 minutes!
  • Understand the essence of the Power BI visualizations, and see them in action!
  • See how Power BI is used with real examples!
  • Understand how Power Query, Power View, and Power Pivot are used together!

After this class you will be able to:

  • Actually SMILE when you open a Power BI Dashboard :-)
  • Brag to your friends about how you can use Microsoft Power BI confidently!

If you're like me, you use Microsoft Power BI on a daily basis for important tasks, text processing, or reports. Whether it's for business or personal related projects, everyone wants to be able to use Power BI freely and easily.

dbb640ce.png

We will learn how to create your own report dashboard from your source data! You will learn:

  • Installing Power BI Desktop for FREE
  • Getting Data from a data source (Excel Power Query)
  • Cleaning Data (Excel Power Query)
  • Creating a Data Model (Excel Power Pivot)
  • Create cool visualizations and charts (Excel Power View)
  • Publishing it to your own dashboard online (Power BI Website)

And you will use live source data in creating your own Power BI Dashboard!

d3f61c92

Transcripts

1. Welcome to the Power BI Course!: Welcome to Master Excel, Harvey. Just 70 minutes are starting out that excel R B I, and this is perfect for you while you learn from scores are to follow cleaning data with our green cray the day tomato using part, even great eye popping dashboards. So much So, what are we waiting for? C inside, of course. And master R B I 2. Excel Power BI Introduction: hi and welcome to the often world off par be I So I'm just showing you here right now on what we see on the par be a website. It's actually one of the reports that will be creating later. Okay, So before we get started, this talk about what power bi I is all about. So, par B I is a cloud based data analysis tool which can be used for reporting and analyzing data from a wide range off data sources. So there's gonna be a lot off data sources that we could use to create our reports and perform an axis on it. So par be iess, a simple and user friendly, enough tool. So even you or business, and in this or power users can work with it and you will reap benefits out of it. Okay. But on the other hand, it's also a powerful and mature enough tool that can be used with enterprise systems. So if you're a developer, or if you love working with complex data and modeling scenarios, then par be I. It's also perfect for you. Okay. Before we dive further into part B, I I wanted to discuss first about the different components off part B I So let me just open my par be I desktop. And the first component that I want to show you is deep are chary. Okay, so for park for you. This is where we load our data and then we mash up our data. Okay, So you're not a word to be transformed Our data next up is power pivot. So what? I'm just jump over here and for par. Pivot. It's an in memory tabular data modelling tool. Okay, so once we have our data loaded and transformed and this is where we create our data model , Okay, Next up is power view. So poor power view. It's actually our data visualization to Okay, So that's where you create your charge, your visualization so that it's easier for you to understand the data that you're trying to analyze other components, right, That we're not showing here at the moment. ISS power map. It's ah, treaty data visualization tool. Okay. And then we have power Q and A. So it's a natural language question and answering engine. And then we have this one that we're using Power bi desktop. So this the Windows application that I'm using at the moment. And this is a powerful companion development tool for Part B I. It's more of like a holistic development tool that combines park re power pivot and par view into a seamless experience. Okay, so if you're overwhelmed right now, don't worry about it, because we'll be going through all of this step by step from getting data all the way. Once we have our report and visualizations ready. Okay. So aside from par, be a that stop. We also have the power bi a website, so they just jump over here. So over here, we actually have the power bi a website. Okay, so you can publish your reports year so that it can be shared to your colleagues or friends or different people. OK, then there's also mobile labs, actually, and we will see this website in action later. Now, if you've used this individual components before, then this is where you'll be discovering the beauty off. All of this integrate together into one seamless solution. So we'll have couple of examples for you to work from start to finish and look forward to it in the next following sections 3. Power BI High Level Flow: to have a better appreciation and understanding off part B. I I want to go over the high level overview off the entire par be I flow. Okay, so the first step is to start off with parkway. So in Park Khoury will load the data in here and then will perform transformations to clean our data and remove the dirty ones. Okay. And then we have something that's usable and useful for us. So that's the first step. And then the next step is to load it into part PVA into the data model. Okay. And then this is where we start establishing relationships between our tables. Okay, so that we add more meaning into our data, okay. By linking the related tables to each other nexus par view. So now we have the data model ready. Then we use our data model and create visualizations that show the trends in our data or something more viscerally meaningful. That's easy to the ice. Okay, So this is where we use part of you for and then afterwards wants your report is ready and part of you you can now publish it straight to the par be a website and Now you can share your work with other people or create dashboards outfit. So that's pretty much the high level float. Okay, for our par be I process. So I'll just show you the screens quickly for each one. So if we go over to park re, this is the screen that we're gonna be seeing where and will be cleaning our data in here. Okay. For Park re next off with par Pivot. What we see here is we now start to establish relationships between our tables so that we can relate them. Okay, all of them together. Sedans for part PVA. Now we have par view when we start creating visualisations off our data model. Right? The data that we have just cleaned up, we've set it up. Now we create graphs and visited stations out off it. Okay, so that's for a part of you. And lastly, ISS we now publish so you could see it, right? That it's the same report that we created in par view. And now we publish it to the par be website over here, and you can share the other people or you can create dashboards. Okay, so this is for the par be I flow and we'll be going Tru detail for each one of them. 4. Excel Power Query Introduction: so before we get started into creating our own part B I solution. Okay? I wanted to go true with you in detail, first on the individual components. So let's start off with park worry. So for park, for it's a data transformation, right? That we used to load the data as a starting point and then perform armor transformations to make it into something useful for us. So Part B. I can actually be downloaded as an add in for Microsoft Excel or it can be used. That's part off the this one, the party I desktop. Okay, so there's really a lot of data sources that it covers. So if we select, get there over here, you can see the most common ones over here of Excel sequel server websites. Right, But to have a better view. So let me just click this. There's a lot of their service. You'll be surprised. So even here, right, if we go to foul, you'll see excel workbooks. You can get it even from multiple worksheets, text files or comma delimited CSP files. We have XML, Jason folders. So which means if you have a folder of files, then you can grab data from all of those fouls. And just one go. You have SharePoint if sequel access Haruko DB two my sequel, Sybase. There's just a whole lot of data basis in here. Okay, let me just jump over par be I data says when those Asher's. So if you have data in the cloud, you can even grab it a swell online services SharePoint myself exchange right and dynamics . Over here we have Salesforce, Google Analytics, right. Facebook get hub even mail chimp. So there's just a whole lot and the cool thing with power bi desktop ISS It gets updated fairly frequently. So you would count on the number of data sources to increase even more even stripes in here , right? If you do payments, if you have quickbooks that you could just grab the data in here and trade cool visualizations out off it. It is one of my favorites, actually getting data straight from websites from live data in websites. Right. So there's just a whole lot in here that you can play around with getting data with park worry. Okay, So the user interface, if we jump over here to our park re editor window Don't worry. I'll show you how we can do this later. But let's just go over here right? There's a whole lot off operations that you could do in Park Re mainly transformations, so it allows you to add column. So if you just go here right, there's a lot of adding columns in here. It allows you to change data types. If you go true transform, for example, it can let you change the data type to a different one right over here. It allows transformations, numbers, dates and time. Right? Tex, if you want to speak the column, if you want to extract data out off it, extract the first few characters If you want to. People and people columns. If you want to move them. Replace. So there's a lot of transformation that he could do in here, right? Even Trench posting, right? So that's the cooling with Parker, and there's just a whole lot that you can play around with it. And then what happens after you do? The transformations is all of the steps are listened out inquiry setting so that it can have a quick overview on what has happened so far, and you can even modify them if you don't like. If you're not happy with specific stepped in here and you can even changed And midway okay , as you see fit and then the queries are listed on the left side. OK, on, let's say you loaded data for sales and then for sales person. So that results in two separate crease. And once you're happy with the result set, once you close it and load it, it will go into excel or the power pivot model. OK, so it gets noted there. One thing that I want to take note off its park re also uses a powerful formula language. This is called em. So I m is much more powerful than the user interface built for it. So which means AM has a lot more functions. Then you can see over here, right? So if you're ready, overwhelm or if you already amazed with how much you can do from this interface, M has a lot more to offer. Okay, there are not more functionalities in em that cannot be excess in here. You can only access it through the code off them, so that's pretty much for Park Re 5. Excel Power Pivot Introduction: Now let's talk about part people. So what I did right now must jump over to the data section of part P. But so they can see what slowed it right now into our model. Okay, so, party. But it's a data modelling engine, and you could eat or create new measures in here. Calculated measures and columns, right? Could see over here. And then you could also build relationships true, the different entities that you have. So, for example, we have sales and sales person, right? If you jump over to relationships, you can actually see on how our data model is looking at the moment, which means they have a relationship between this table right in this table. Okay, So part people also uses the data analysis, expression, language or, as we call us Dax, the X for building measures and calculated columns. And it's actually this decks is actually a powerful functional language and their heaps of functions for that in the library 6. Excel Power View Introduction: Now let's jump over to power view. And this is where I think it's the most fun part for everybody, because this where you can see the results of your hard work displayed in a cool manner. OK, so part of you is the main date of its realization component off par, be I and one thing that's really cool with it, it's It's interactive, right? Because if we create charge, sometimes we just think it's just charts. It's just something to look at and you can't do anything with it, right? It's like just having those charged planted on a poster, and that's it. But this one is actually interactive. What I mean by that ISS, for example. We have sales by first name over here, and you have the sales people right listed over here. So the amount of sales per person and if it's the amount of sales per year and if you click on it, the values changes will in the other charts that are related to it. So it's very interactive. We can actually play with it, right, and we'll go true with this in detail. Okay, So part of you has a lot of charts for visualization. So over here you could actually see the different types off visualizations you could use in power view. And we'll be using a bunch of peace wants. We create our own power bi i desktop solution. OK, so it can connect to multiple data sources. Put it in a cool grabs over here, and it's interactive for your users to use, so you can either use lighters for dicing and slicing your data. Okay, so look forward to using par view in tar be I. 7. Install Power BI and Sign Up: Okay, let's talk about what you need to be able to follow through our examples. So to start off, we need part B I desktop installed on your computer. Okay, so I'll be sharing the link A swell on the dentals on working down with this for free. OK, so won't you go to this page? Just go straight to power Bi desktop. Over here, there's a Donald button. And then what I normally do is I just go for the advance down adoptions. So that's click on it because it's easier for me to install fire here. Okay, so once we have here English okay, for the language go to download, just select the corresponding, like 32 bit or 64 bit version for you. OK, in my case, it was 64 bit. Just click next and it will get downloaded. So I'll just go straight here because I already have it. Ready. Okay. And over here, you just don't click on the installer and just continue clicking next on the steps. It's pretty straightforward to install. Okay, And soon enough you have power bi desktop insult on your computer. Another thing that I wanted to share ISS. You can also create a power bi I account. And that's pretty free activity. So what, you go to this link? Okay. So just scroll down here, okay? So sign up and get started today. Over here. So the reason why we're getting an account in par, be I online, right? Once we want to publish our reports in the par bear website, it will require from you a par be a account. Okay, So this is actually a good thing for you to him so that he can follow the entire example from start to fish. But if you don't have an account, don't worry. Slows you have power bi desktop installed. Then you can work. True the examples until you create the report or the power view visualization, but you won't be able to publish it to the website so that you can try out creating a dashboard or share your work with others. Okay. So I would recommend a swell creating an account in here. It's pretty straightforward to do. The only catch, though, is it has to be a work email address. Okay, So for personal emails like Gmail or Yahoo, even accounts, it won't accept it. So it has to have a company domain for your email. Right. So, for example, over here, I'll just tap it in here, OK? Around Hot mic. Sell online at com. I'll just use it free. Okay. So once we have this pace, you just feel out the first name, last name, password, and then whatever verification code gets sent to your email address. Okay? So it just go start, and you're good to go. Okay, So you have to her free par be account ready for you. 8. The Big Picture: So now we're gonna be working on your very first par, be I solution. Okay, so I wanted to go through first the big picture on what we're trying to accomplish here so that you can have a high level view on what we're going to accomplish. So first thing soft is we're going to be starting with this data source. Over here. We have the sales data Excel Workbook, and it has two tabs over here to work shifts, To be exact one sales person, right? And then we have the sales table where in we just show all the sales relating to a specific sales person. Okay, so we have six sales people over here, and then we have the sales figures for each individual sales person. Okay? And what we're gonna be doing here is we want to load this into part B. I this spreadsheet over here, and then do some simple transformations. So let me just jump over to par, be I. And now, once the data is old loaded in here will be doing some simple transformations. Okay? And then once we're done with it, we'll load it into our data model and want to be. Load that into our data model. Over here will be checking and making sure our relationships are correct for our data model . OK, so this is the line over here for their relationship. And once we're good will be going over to create our first visualization. And this is already based on the data that we have loaded into our data model and once Very good. Then you have your first solution complete. Okay, So I hope this excites you because I am. And let's go over and start working on our solution. 9. Power BI - Get Data: Okay, now let's start with par by Desktops will make sure to run the program and start it up. And once we're presented with a black workspace, the first thing we need to do is get there. OK, so with any solution that you want to create in part B I it always starts with the initial data because that's our starting point on what we need to transform and analyze and create visualizations out of it. Now, what we need to do is get data. So we just select this one. Let's go to excel, Okay? And what we want is the sales data. And before we load this up, what I want to show you quickly is our data over here. So it's a less off sales people, OK? And they're uniquely identified by the sales person. I d off 1 to 6 over here. We just had the first name, last name, nationality and their court case. So nothing out of the ordinary for this s people and in the 2nd 1 is sales. Okay, so we have the customer the date, right, and then the sales numbers and then the sales quarter Cheddar. OK, but one thing that's very important here. Yes, this first called him a sales person. I d. Because this is were the sales table, right? Or this specific seal? It's related to a sales person in our first table. So for example, this one, the value of 24 tiles on 6 $40 Over here it's sold right by sales person I d number four. So if we jump over salesperson 90 number four Yes, yours truly, Brian. Okay. And which means Brian was the one who made this sale over here to Long Island's off soft drinks. Okay, so it's the same day as well over here. So they did six. Then you just check over on who the sales person is. And that's homer for number six. OK, so it's pretty much the same thing over and over again. So we should swine, I you could see that it's always sales person. Want to six over here? A swell. Okay, for the I. D. S. Now let's jump over to Park re. And now, once we see the sales data, this is the file actually that I just showed you that's to load it up. And what you see over here. It had tried to load off the potential their sources inside that workbook. And if you select sales, Okay, it is actually the one that we just saw a while ago. And it's looking good, right? And if I just scroll down something weird with power bi I but that's perfectly fine. Okay, so if we scroll down, you could actually see that It's perfectly fine, right? It's saying that the data in the preview has been truncated. Just reload. This is to make it look better, Diego. So if you just scroll down, okay. The data in the preview has been truncated due to size limits, so it doesn't show you the entire table contents. It just show Teoh a portion. But from looking at it, it seems that the structure is perfectly fine. So that's good. Let's make sure that stick check sales person 1 to 6. Perfect. So this is the one for the sales people. It's checking the swell. OK, once we're good, let's go at it. So which means we have this to tick. Let's go to edit, and it will bring us straight to Park re editor. Okay, so already loaded this before, so it's just asking me to refresh. Okay, I've done this exercise before, so we should swap is showing me that notification. But on your end, since it's your first time doing this, it won't show you that. Okay, So one thing that I want to show right now is with Parkway editor dish a whole lot off transformations that you could do here. One of the goals that we want to do. ISS. Let's go to sales over here, right? Let's do a refresh, Okay? And then for the sales, what do you want to do is just extract the year from the ordered A in Case 2012. We want to extract this and then create a new column out of it. And to do that, there's actually two main tabs that we play around. When it comes to modifying values for transform, transform is changing that specific column. Okay, so you're affecting that Karlan directly. Ad column, on the other hand, is your performing some sort off transformation? But you want that result to be placed in separate column, which means you won't be touching this one, but you'll be adding a new column instead. So in our case, we want to add a new column that contains the year. So we'll be going straight to this tab. At column, make sure order date is selected. Okay. And then what we're gonna be doing ISS go to date over here, go to year and select your And once you do that, this is the Year column Now, its newly added it wasn't there before. Let me just drag this for left click and moved it over here. Okay, Now, once we have moved, it's right beside order date so that you just have a quick comparison. And if we scroll down with our data, right, he could see the year 2013 it was extracted correctly. Okay, 2014 the Chandra. And if we scroll to the left, you could see that this is the entire data that was loaded from the Excel work ship. Okay, so which is pretty cool, So if it just called down, right, 5 76 rolls if you jump over to the excel spread Shit. Okay, If I just rolled down, let's just do a quick check. And Yep, 5 77 So he just might us one, because off the headers. Okay, so that's 576 So it's the same thing. It's well over here. If we jump over to sales person, right, we have six people. A swell. Okay, So one thing I want to stress is on the right side for the queer settings. Right? You could see here that there's a step inserted year because there's the one that you try to do, which is you inserted the year column. Right. And then you have for ordered columns wherein we moved the year column right beside the order date. So the Kolding with this one is it just shows you step by step, Mom, What it did so that if case you're unsure what happened, what did you do? If you did anything wrong, you can quickly review it from here. Okay, on the applied steps and all of your transformations definitely will happen on the top. OK, so once we're happy with the data, it looks good. It looks good on our end. We have the year. We have thesis ales. We have the sales person swell. Once we're good will be moving over to modeling our data. Okay, so stay tuned for that 10. Power BI - How to Get More Data: Okay, so we're still inside the park re editor window and one of the common questions that we get ISS What if we want to enter more data or load more data into the park re editor? Okay, so now we have sales and sales person in here, so it's very easy. So if you're still inside the park re editor, you'll just go to home, okay? And then you can see here. New source. You could just go here, right? Or click the button and you'll be able to see the different types and you'll be able to load more data over here. Okay, so this is one of the ways, if not Okay, so let me just close this window over here. So if not, if you're inside the par be I desktop window, what you can do is just go to home, see to get date over here, and you'll be familiar with this one. It's exact same options that you see, and you can also click the button and you'll be shown the same option to swell. Okay, so you can add data. Either Wait for this to matters over here in Park Re 11. Power BI - Modelling Data: Okay, So now we floated our data were happy with what we did in King, and we've applied our first transformation, which is extracting the year from order date. Okay, so we've done a great job so far. We're happy with both of them. Sales person and sales. What we're going to be doing next is go to home said that close and apply. And this will now get added to into our data model. So it's pretty quick. In case if we jump over to data over here and you can see that sales and sales person have already been loaded, you just scroll down. It's the same data that we're seeing right now. If we jump over to sales person is the same six people over here. So that's looking good as well. And the next thing for us with our data model ace, we want to ensure that they are correctly related to each other. Okay, so what do we mean by that? So let's jump over to this tab over here, the model and you can see now the relationships. So we have sales. We have sales person. But you might be wondering, where did this come from. Okay, so this line over here signifies on house sales. Person is related to sales, and you could see if we over over the line, you could see sales person I d being hide it. Okay, so let me just double click on the line. But if we can have a better look over here, Okay, So if we jump over to what's highlighted is showing that Barbie I was smart enough to infer on what waas the relationship or the related columns between the two tables? Because it's correct. And you could see that sales person idea of sales person table waas related to the sales person i D. Column off sales. Which means what we're saying is, this one over here would pretty much represents well, whoever to sales personnel to specific sales wrote, and it was able to infer a swell that there's many to one for the card in ality. What this means is many to one so many sales, right? It's related to one single sales person, which is true because, for example, this one Michael Jackson, has a sales person, i e. If one and there's only one sales person i d with the number of one in here. Right? But over here in the sales table, there's a lot off wants over here because Michael Jackson has made more than one sale. Okay, so same thing for two sales person tree, Right. So for John, for example, if we jump over to the spreadsheet so that you can have a quick look So if you jump over here, you could see that there's a lot of trees over here, right? Because there's many instances off sales person i d number treat. So this are old sales that are made by John over here. Okay, so if we jump over back here, So which means disc relationship is working fine. So the inferred relationship by par B A is fine, so we're good with it. Let's go, OK, which means our data model is good to go. 12. Power BI - Adding Relationships Manually: one of the common questions when it comes to the data model ISS. Okay, the relationships looks good. Par be. I was able to infer on what the relationship is, but what if it wasn't able to infer anything at all again? So which means it's just these two tables, and then we don't have a relationship establishing how seals it's related to sales person. Okay, so here's what we'll do it just to have ah, quick review. Okay, let me just double click on the relationship. So this is the relationship that we have were in sales Person. I D is the one connecting this two tables together, and we have this relationship off, many to one thinking, because many sales relate to one sales person entry. So what we'll do right now is that we just cancel and let me just right click on this and delete. Are you sure you want to delete this relationship? So let's go. Okay, Now let's add a new relationship McKay so that we can see on how do we connect this one to the other? So here's what I will do once we go to manage relationships, so you could just go for auto tech are new, but in our case, we want to define one manually. So let's go for a new now which tables So we can go for sales and then we can go for a sales person for the second table, OK, and then just make sure this is selected sales person i d. We wanted to relate to Salesperson 90 as well for sales person, and it was able to suggest that many to one. And that's the one that we want, right, because we know that many sales is related to one sales person. So that's the selection. Okay? And then make this relationship active. This one is good for across fielder direction. And then go. Okay, once you okay, the relationship gets listed out over here and just go close. And now your relationship is establish so you can continue on adding more relationships through this window over here, right in manage relationships, and then you'll be able to see it reflected in your data model. 13. Power BI - Visualization: Okay, Now we're good with our data model. The last step for us is to create our visualization. So let me just over here, right? See the report and select it. And now this is where the fun part begins. We're going to be creating a column chart that has sales and first names, which means that we want a list of people, and then we have the sales numbers displayed in a column chart. And then the next one is we want to create a simple pie chart that shows the number off sales like the total amount off sales figures, okay. And categorize by year. Okay, so what we're gonna be doing right now is let me just grab over in sales, right? And sales person, What we want to do is get the first name. Right. Okay. So once we drag this over here, it just defaulted to a table desalinization. That's fine thinking. So we'll just get the field ready first. So you want the first name, and then we want the sales figures. Let me just drag this over here, okay? And this is what we have so far. So this is the amount of sales that Brian did Homer dead John did. Kyle did right? And once we're good with this one, it looks good, but it's still not a column chart. So what I'll do is let me just over here that's selectee clustered column chart. Want to select this? And now you have your first call them chart and let me just expand this so that you can see it bigger. OK? And this is three column chart that we have and just like that, Okay, you were able to create that. And if you notice this is something that's pretty cool as well. We created a chart that contains data or uses data from two separate tables. If you notice we got it from the first name right off sales person and we got the sales numbers off the sales table. And if we were to be working with this original spread shit over here, we won't be creating this chart pretty easy, because we wouldn't know what is the name of Sales Person. I. D. Number four, for example, What is the name of sales Heidi Person number six. We would end up with a chart that has ID's off sales person and the total sales numbers off that which is not exactly readable. But thanks to our data model that we did a while ago, we were able to establish a relationship between the two off them and power bi I waas intelligent enough to understand that relationship, so we should smile. I want to drag the first name. There was no need for us to involve ourselves with using the sales person i d. You could just drag first name and then dragged sales numbers and it works perfectly fine. Okay. And now let's work on our next chart, which is the pie chart. So let me just selected pie chart here so we can actually click first and add a black chart . Okay, Now, let's work on getting the sales numbers by year. So I'll be doing here. We have the year, right? This is the new column that we created and extracted from the or today. So let me just grab year over here, okay? In our legend, and then I'll grab the sails into our values section just like that. Now we have our pie chart off the sales figures. Okay? You can even over and check the actual values. If you're curious, right? Okay, so which means it's actually 2013 with highest person Tasia off sales. And one thing that I wanted to stress is the interactivity off the charts. Because if we select Brian, for example, over here, it shows you on What is the amount right that Brian has sold in 2012? OK, so that's really cool, because if you do for the other people, a swell, the values change and it's very interactive. It knows on which values or which charts are related to one another, and it updates accordingly. So, for example, if we select 2013 then it shows you the sales figures off each individual cells person in 2013 so its able to filter it for you because you've selected a specific section off the pie chart. So that's really cool. And for our part, no additional action was done right. We just drag and created the column chart, which is drag and created the pie chart. But there was no specific setting right that we did to be able to enable this interactivity . So look forward to creating MAWR complicated reports like this, and you will be fascinated on what you can do with it. 14. Power BI - Visualization Customization: So let's discuss the visualization properties more in detail. So what will be selecting? It's our column chart over here. OK, and then what we have for the properties for the access we have first name. Okay, so this is reflected over here. So you could see the first names listed out over here. And and for the legend, this just drag first name and added to the legend. And now you'll have it quarter coated, and the legend is added on top. Okay. For each first name and 40 sales values this one, right for the value, you could see that it's reflecting on the left side over here. Okay, on how high the total sales amount this and you can actually change it. This well. So, for example, over here, you could go for the average sales. You could change it, right? So you could see Now it's 60,004 the maximum over here for the range. And you could just change the setting swell for each field over here. And if we scroll down to filters. So if you see here that we have average of sales first name. So, for example, let's just go to first name. And now you can do some filtering over here. So, for example, you just want Bren, You want Kyle? You want John? Okay, so now you have your charters Will reflecting the filter off what you have selected. Okay. So I can actually play around with the settings off your visualizations if you want for your costume stations to it. 15. Power BI - More Visualizations: Now let's play around with par B I and add more visualizations. So let's say I want to create a visualization. Where in? I want to see the order date based on the order date. How a sales growing per month. Okay, so what I'll do is let's just select a line chart. Thank a over here and then I'll just drag and the order date for the Axis and then sales for the value. So now you can see over here. Okay, now it's doing it by year for the sales for 2012 for 2013 and then for 2014 now. I wanted to be by month because I want Let's say I want a report that shows me which month has the greatest amount of sales and which month has the least amount of cells. So let me just remove year from here. Removed the quarter move day, OK, and now we have the sales figures group by month, so you could see that Marsh is the one that's the lowest and that we have December. That's the strongest, okay, and you can actually play as well with the visualizations. Let's say you're not happy with the line chart. You can quickly changed it, for example, to an area chart. And once you click on it right, it will use the same settings. And now you have a different type off. This a decision, which is really nice. So if, for example, if you want to change it to this one line and clustered column chart, okay, if you select it now, it's a different type. But it still shows you using the same data. Okay, so it could still see here. That march is the lowest, right? And then we have December asked the highest part. So let's just go back to the air chart. That looks good. Okay, so you can play around with the visualizations. Now let's work on another visualisation. Let's say we want to see a chart that shows us the products and the sales numbers for each product. Okay, so let's try this one. The donut chart. So let me just select this. Now let's add in here the products, okay, and then the values would be our sales numbers. And just like that, you can now see on which one has the greatest share, OK? And we're looking at tonic and bottles looks to have a bigger. It's pretty close, though, if you look at it. But just like that, you now have a new chart okay for the products, and there's a lot more charts over here and have fun with the different facilitation and let us know how it goes. 16. Power BI - Visualization Format and Analytics: Now that we have more visualizations in here, we can play around with some of the settings. Okay, so let me just select this area chart, and you could see two more sections over here format and analytics. Okay, so let's go The format. And you could see your There's a lot of settings and if we play around with it, Okay, so let's go to x axis. You can turn it off. You can remove it, right? Okay. He can do the same. A spell for the UAE. Access their colors. You can actually change it to a different color if you wish. So let's say change it to yellow. Now, let's go to data labels. You can turn it on, and that looks cool. Okay, You can see the numbers we have shapes. You can actually make it more dicker over here making a ticker. Okay. And then you can change your life. Dollars fell to the head. Our dish, It's still dick, so I couldn't see it. So let me just make this murderer gay, and you can see that it's now Dash has changed some some markers. Okay, you can chase it marker shape. So there's a lot of things that you could do. So if you want to change the formatting off your chart, then just go to the former tap. Let's see if there's background this well, a background color. You can even add color to it. Okay, here you go. Transparency. You can change it to. Okay, let's make it this way, Borders. You can add border for swell whatever color you want or dicker borders. Okay, so if we just click away, you have a border. A swell Now? Yeah. Okay, so that's pretty much it for a format. Now we can go to analytics, and you could add more likes to it. So, for example, let's say you want the average line over here. Let's just go ad. And you could see here that it added an average line over here so that you can quickly compare it against the other values Samos Well, for minimum or maximum, or even adding a constant line if you want a specific, let's say if you want it, so just type in one million over here and you have another constant line added. OK, so you could also add two million. Okay, so now There's two lines, right? One that constant line of two million and then another constant line. Four are average. And if we scroll down, you can also add person towel over here. Okay, so let's say you want to see the top. Hold on. Let me just scroll down. OK? So if you want to see the top 90% so it adds a line for us. Well, so it's a dynamic line, depending on where the values are and one thing to take note off. So, for example, if I select the pie chart, analytics are not available, so it really depends on the visualization that you have selected but 40 pie chart. You can play around now with the former Tamp and then make some changes to it. Okay, so that's pretty much for all of the cost conversations of visualizations. So just have fun with it and play around to make your charts look distinct. 17. Power BI - Ask a Question: Okay, now let's discuss about Q and A so it's a pretty cool feature in part B I. And it's actually over here under home as a question. So if we click on it before I do that, let me just recife the charts so that we can have more space for our new chart. So what ask the question will do for you if it will generate a chart based on what you're asking for. Okay, so it's like having your own virtual assistant. You're talking to that person and and create a chart based on this fields that I want and it will just suggests, Ah, chart for you. Okay, so let's just try it out. Let's say total sales, whatever I'm thinking of right now, Okay, bye and say, customer and just like that. Okay, so it was able to create a bar chart for me, where in the sales are listed and we have the customers on this one, right? So now you have a chart and you haven't even done anything. It's just more off asking a question, and it will create one for you. So let's try another one. I'll click. Ask a question again. Okay, so let's think of something. Let's say sales, total sales or let's say average sales okay by sales person. Okay, let's see what it comes up with and then sorted by every sales. Let's see if it's able to understand and this is really cool. It was able to create right the average sales. And then the name says, well, on the left side. And then you could see that it was sorted by the sales. You could see the highest number of sales. Okay. And then homers at the bottoms. Okay, so if, for example, you're not happy with the bar chart, that will suggested to you, maybe you can change it to a column chart. And just by typing column chart Now, it has changed it into something like this, right? And it still sorted. And it's really cool because you can play around, just ask questions, and it uses natural language processing to be able to infer what you're after. Okay, it won't be perfect all the time, but it's something for you to play around this. Well, OK, so enjoy 18. Advanced - Real World Example: now let's work on a real world example, which is N. B. A. Said that six. So this more off the basketball side and we're gonna be working on this sites. That's that. End ea dot com So don't worry, as we won't be using all of this numbers. So even if you do not know about basketball and what are the statistics involve, you will still be able to see the power off power. Bi. I okay, because what I'll be doing is will be just pen picking a couple off. That's in here, and I'll be walking you true on what each means. OK, so that you can gain an appreciation off how it's related and what are we doing with this data? OK, so the goal here is to show you how do we mash multiple data sources? Because from here, what we'll be doing its we'll be getting stats and numbers from tree different sources. OK, so we're doing this life a swell. So which means we're working on a live website. We're getting the data, and then we're getting it straight from online source. So that's pretty cool, actually, So I'm pretty excited and let's go over the different data sources that will be working with. Okay, so the 1st 1 iss more off teams that So this are the traditional stats in here, and what I'm doing is I'm getting it from the year 2017 to 2018. So that's a date in the past. So we have full numbers from a regular season. So this is just a regular season wherein each the team battles against other teams. OK, so they play against teams over here, and you could see here on the left side is we have a less off all of the teams. The N b A teams over here, right? And then we have a couple off statistics. So we have games played. How many games did they play? How many winds? How many losses? What if the wind percent aged? Okay, so it's just more off winds divided by a number of games played, then the number of minutes and then the points okay, that they scored. And for the rest of dissident six, we won't need this anymore. Okay, so we're just after the points scored for each team over here. Okay, so that's 40 traditional team stats. Now we're going over to team defense. Okay? So for team defense What? We want to see a so same thing You could see all of the teams listed out over here. We have the games play. They have the winds, we have the losses. Okay, what we're after is the defense or the defensive rating over here. So it's just a score to say how good the defense off team is. Okay, so that's what we're after. Okay, so for the rest will be removing. This was well, later. We won't need the rest of the data. OK, so that's for team defense. Now let's move on to the turd. One teams clutch. Okay, Team clutched that. So when we say clutch day, so clutch. It's just a fancy way off the finding, the last five minutes of the game and the difference between the scores off the computing teams ISS five points or less. So, in other words, it's just simply put, it's a competitive game. It's a game that's very close, and you're not sure on what will happen in the final few minutes because you don't know who's gonna win, because the score is very close. Okay, so that's actually the most exciting type of games to watch because you'll be on the edge of your seats while you're watching on who's gonna be winning until the very last second. Okay, so over here is you could see here that same thing, right? We have all of the times listed. And if you notice a while ago if I just moved back to the first list, you could see that it's 82 games. 82 games for all of the teams over here, right? Because in one season or in one year, you could think of it that way. It's Each team plays 82 games. That a lot. Yeah, and if we move over to the clutch, not all off. The games are clutch games or are close games, right? Some of them are close, some off them. The scores are pretty far from each other. Okay, so which means each team would have different experiences and they have different clutch games. Are number of close games played right? So I could see that Cleveland, for example, has played in more close games as compared to Houston. Okay, over here, what we're after is the number off clutch games played okay for each team and we could just keep a couple of columns in this data, and then the rest will just remove them a swell. OK, once we start working with the data, Okay. So now we have all of the tree data sources explained. Right? So what I'll do right now, it's just show you on. What is the solution that we're after? So what we're after is, so I'll just go over here quickly and go to our creates. So what we want to happen right now is we want to load all of this tree sets of data into our part B I solution. Okay, So once we have it here, we'll just be doing some couple of transformations to clean our data. Okay? And then afterwards, once we have that ready. So we have here the team stats team defense team flush. Right. Once we have that ready, will be loading that into our data model, right? So that we can link them together and have them to be related with one another. Okay, so we can establish the relationships between this tree entities and once we're done we're going to be creating our visualisations. And this is the fun part because you can now see the cool data. Okay, on what we have here. Don't worry. I'll be walking through you over here. It's that. Okay. Step by step for each one. How we created all of this. Cool. Visually stations over here. Okay, so look forward to it. And now we'll get started with loading our data into part B I desktop. 19. Advanced - Get Data: Okay, now let's start to work with getting our data from this three data sources. Okay, so have your power Bi desktop open and what we'll do. Nexus. Let's go to get data and we want to go and get the data from a website. So that's a Web. Okay, so now it's asking for You are ill. So first things first is let's start with our first date of source, so we'll be working with the teams. That's the traditional team stats, so I'll just be copying the complete your l from here, so I'll just right click copy, right? And then let's go over here and let's pace it over. Once you have the your ill, just go OK, and it will start to parse or analyze the website on which sets off data. You could retreat from it. Okay, so it will take quite a while, but hopefully not that long. And once you have that ready, we'll explore what data it has in store for us and what we're after. Definitely iss the table, right? The table that we're seeing with the teams the point. Okay, and then let's see if it's able to get it successfully. Now, you could see here that it got tree sets of data here. So let's just select document and see what's in here. No, this is not the one. Let's go to table, Jiro. Yep. If you look here, it looks good, right? This is the games played. If the winds loss the team names, right? And then if you just grow over here and you have all of the other statistics, let's go to Web view. Okay? And you could see here, right? It has the same webpage that we showed a while ago, and it has highlighted the table correctly over here. Right? So which means this is the part that it retrieved the data from four Table, dear. Oh, so once we're happy with this, Okay, this is what will be selecting table zero. Okay, so that looks good. Just out of curiosity, let's have a quick look at table one just to see what it looks like. And this is not the one we're after, right? So we're happy with table zero. Make sure that selected and let's go to edit. So once we have that, it's gonna be loading it straight to the park. re editor window. And now we can work on our transformations. So this prompt over here is just showing, so just close this, okay? Because I already loaded this before when I was working on this exercise, right? And for you, it shouldn't be showing up if it's your first time noting this data. Okay, so I just close that one. We can just ignore that. Okay. And the first transformation that we want to do. Yes. We want to remove this column over here. This is data that we don't need. So I'll just right click on the column, header and select. Remove. So OK, that's true love right now. And then what we're gonna be doing next. Yes. We want to get the location off. The team's over here. Okay? Because if you look closely the formatting over here for the team is the location followed by the name. Okay, So you have Houston asked the location, and then your team name is rockets. Okay. Same thing as well. Golden State is the location. Who are yours? ISTEA team name over here. So the pattern just repeats for every single team over here. So what, we're gonna be doing? It's We will be transforming this column right and splitting it by the space in case so that we can get the occasion and then the team name afterwards. But a question right now is what do we do with applications that have to write two words over here, for example, Golden State. So what we're gonna be doing is we're gonna be splitting it by the right most space. Okay, so which means this space over here, we're going to be splitting it to another column, right? Same thing, a swell for that. Say Oklahoma City, right. We're gonna be spitting the Tonder out, right? With the right most base. So what we'll do right now is transform. Let's go to split column by the limiter. And if we look here right, we have want to split by space and make sure it's the right most. Demeter, once you're good with DeSisto like okay. And just like that, you've already split the column into two. And you have the team names over here, and you have the locations over here. Okay, so if we scroll down, it's looking good at the moment. Except for one thing, right? One of the records don't look like a location. Which is this one? Portland Trail Blazers. Because the team name is actually Trailblazers and educationists. Portland, this is the only team that has a team name of two words. Okay, if you look at the others, they're all looking good. Except for this one. OK, so the question now is what should we do? OK, so this one thing that's very nice with park worry. Because even though, if something doesn't look right, if you did the initial step, you made some assumptions, and it doesn't look right. Right? You could move back a step, right? You could move to any step, actually. And what we're gonna be doing its nets move a step back up over here before we did the splitting. So this is the step where? And we remove the numerical column, right? And I'll be doing an additional change over here. So what? I'll be doing iss for the Portland Trail Blazers. Just to make the formatting consistent with the rest of the team names. I'll be changing the trail Space Blazers into just a single word off Trail Blazers. Okay, without a space. So what? I would be doing right now is l stick with transform, and then I'll go to replace values. Okay, so it's just saying Do you want to start a step mid way and came because we're we're editing it right in this step, but we're here, and that's perfectly fine. Okay, so I'll just go insert. And what I want to find is the value off Trail Blazers and change it to without the space. Okay, we're happy, but DeCicco okay. And if you look now, it's now Trailblazers this one word, and here's a cool thing. You don't need to split it again, because what park we will do now is since we have to split step defined. If you click here, you could see that it applied the same transformation. Okay? And it was able now to split it successfully because Trailblazers now doesn't have a space in between. So the right most base, Okay? It was able to split it correctly, and now it works great for everything. So the cool thing with Parker is a most able to apply it right? We made the change midway, but it was able to apply the steps even afterwards. So there was no need for us to redo it. Okay, so now we're happy with this, Okay? Displaying looks great. Now we need to do some renaming on the columns just to make it better. So I have just double click on the column name and type location in here. Let's go to team to its. Changed it to team, okay? And then just to make it look better and change this gp two games played. Okay, so we're done with this. And before I forget, let's rename us. Fill this name over here. Two teams. That's because table zero, it's not really that descriptive. So you could see once I changed this table zero over here in the crease, right section also changed to team stats. So it's looking good for a first date of source and now will be working on the next one, which is team defense. Oh, and before I forget, I mentioned a while ago that we only want to keep until points, and we want to remove all off the columns until the very end. So what we're going to be doing, IHS, this is the column that we don't need. Make sure that selected right that scroll over to the right. Hold, shift and click the last column right, and then we can remove all of them. So let's just go to home. Select remove columns. And there you go. We have a much smaller and cleaner table. Okay, so we're good with team stats. Let's jump over to the next data source, which is team defense. So let's go over to this page. So this is team defense with our defensive rating over here. So what I'll do is let me just copy this year l Let's go back to our parkway and okay, you can actually add a new source of swell in this window. So just go to new source. Same thing, select Web. And then let's pace in. Are you Earl here for the defensive? Stats are defensive rating and wait for the retrieval to complete. So it's gonna be the same steps, actually. So now we have our document. Okay, so let's just check if it has the defense reading. Okay, that's looking good. So we're good with this document. Is this data over here? Let's just ticket esque, okay? And let's see if it's able to load it correctly. Okay, Now it's looking good. We have the number, we have the location. So just make sure whenever you load something from a bad patient, just double check. The source is right. And just make sure that it has to correct data because sometimes it behaves differently. You might be seeing a different less. But in that less one off the tables or one of the items in there would have your data. So you would just be following the same steps and just select it and then load it in here. Okay, So now, once we have it in here, what will we be doing is let's make the same transformation. So let me just right click here. Remove can actually go to home, remove columns as well. So let's do that. Once you have that removed will be doing the same steps right for the getting the location in here. So we'll be replacing first. Let's go to transform. That's changed. The Trailblazers. Okay. To have a no spacing between. Okay, so that's your place. Values Trail Blazers. Okay. And then put in outer space. Okay. So that the format will be consistent across all of the team names. Now let's go to split column the limiter And now we'll be splitting it by the right. Most space still okay And there you have it. Okay, that's double click Rename location. Same thing here in double click rename. It's a team, and what we're after is just this the defensive rating. So we don't need all this data. Just select this condom over here. Let's move all the way to the right. Hold Schiff, right. Click here and go remove columns. Now we have it cleaned up. It's looking good for this one, and let's rename it to team defense. Okay, now we have two Koreas ready teams. That's team defense. Now let's jump over to the turd one. Let's go to team clutch and see what's gonna happen next. Now let's jump over to team Clash over here, and let's just copy the u R L copy and let's jump over here and you should know the drill by now. Let's go to New Source and Citic Web, and we'll be pasting in our less and turd data source. Let's go, OK, and once you have it loaded, let's have a look now and see if the clutch that's are being displayed over here and yet for document, it's looking good. We have the number off clutch games played over here. Okay, so this is what we're after. We have all of team names as well. Let's select this and go. OK, okay, Now that's just removed the notification. We now have our data listed here. OK, so it's looking good. Now we'll be doing the same transformation. That's just remove this column. Right? Click remove. That's changed. Trailblazer again, this go to transform replace value. Okay. And then true leaders Trail Blazers without a space go OK, and we'll be spitting this column. So you should know the drill by now. Right? Most space go OK, and let's rename this to location, rename this team. And then we can also renamed the Games played over here to touch games. Okay. And then what we can do is let's just keep all of the data until the wind percentage, so I'll just go here. Select this column, hold Schiff, select until the very end and right click. Remove columns. Now we're good with the data. Just rename this to Tim Clutch and we're good to go. So now we're pretty happy with all of the data that we have, right? And what we're going to be doing next is we're gonna be loading this three data sources into our data model. 20. Advanced - Modelling Data: Now that we're happy with our data, we have done all of the steps involved. We've floated it here. We've done our transformations. It's looking good. So we're going to be going to home and select, close and apply. So once we do that, it's gonna be loaded into our data model. So it's applying for your changes, and then it's just gonna be taking quite a while for it to load everything in our data model. Now it's loading the data to the model, and then it's gonna be attempting to create relationships. A swell. Okay, so there you go, detective relationships. And now we can see that it's finished, right? You could see here and let's just go straight to data and let's see what happened. So if you look a team clutch right, we have our data in here. The clutch games. Okay, team defense. Let's like this. It's looking good. We have the defense rating over here. Defensive rating scale to team stats, right? We have all the teams. We have the point to swell, so that's looking good. Okay, So what we can do right now is we can just make some changes, right? So for example, in win presentations say it's being displayed as a decimal. We wanted to be displayed differently. Let's go to modeling and let's change the format. 2% person take over here just to make it look better. Okay, so that's a go to team clutch. Any changes that we want to do. That's a win percent age. We can change the format again to presentation. We're here. Okay. Just to make it look a bit different, let's go to team defense. Okay? Nothing. I think that we want to change over here. Okay, now it's looking good. Let's go to this tab over here for the model. We can look at our relationships. So let me just move this a bit so that you can see the lives better. Okay, so what part B I has tried to do? OK, is it tried to infer the relationships between our tree tables over here so you could see there's a line relating team clash and team stats. There's a line for defense and stats, and then there's a line for clutch and defense. Okay, so if we over one by one, you could see that it linked at via location. Okay, so when we look back at our data, Okay, so let me just move back here. You could see that team clutch, for example. Right? Because all of the data over here for the tree tables that we have, they are all uniquely it and fight by the team. OK, so you could think of it. Asked the location as being unique right across all of them. And then we also have team as well, being unique. So which means any two off the columns over here, either location or team can be used to uniquely identify a specific grow over here. Okay, so for simplicity, let's just select location. Okay, So the good thing with this oneness location is consistent across team defense, team stats and team clutch. So which means this rover here for Houston, we would know that this row of stats would be related to this row of status. Fall over here, right for Houston as well, right? And same thing. A swell for team stats. If we go here right, the roll off Houston would also be related because this location with uniquely identify and relate the tree separate rose from the three tables together. So saying goes too slow forward that say That's just pick New York, for example. Then we would look for a row in New York a swell, and you would know the stats here would be related to the same stats that has the value of New York for the location. Okay, so the reason why I explain this concept is because it's going to be crucial in XT establishing relationships in here. So if we over here and surprisingly par be, I was able to infer that location is the one that's relating the team. Clutch values and the teams that's value. So what I did Waas Just redo that. I just hovered over the line and double click on it to open the relationship term over here on the relationship window. And you could see that is, this one is related to this column of swell for the location, and that's good and you can see it's 1 to 1. So which means one row it's related to one roll in from team stats to team coach or vice versa. Okay, so that's what we want. That's looking good. It's like, OK, if we over here okay, you don't see that It's team. It's relating. Team from team touch to team defense. And actually, that's fine. But for consistency, What we want to be related right would be location and application. Okay? And it's 1 to 1. OK, so we just made a change. The relationship over here and once we're happy. Just the neck, OK, now if we over again Yep, it's looking good. It's now location and location. So if we do this a swell the Czech team stats and team defense and it's looking good, right? It inferred and use location. So we're happy with that now, once we're happy with, the relationships will now be able to move on from our data model, and we can now start with our visualization. 21. Advanced - Visualization with multiple charts: Okay. Now, once we have our data model ready, we're gonna be going to the most exciting part. Which ISS this one. The report. So select the report tab. We'll be creating our visualizations, and we have a number off chart types that we want to play with. So first things first, ISS will be working with a column chart wherein we want to combine the locations and the number of wins. So what I'll be doing is that's look for the location and number of wins. So it's under team stats, so I'll be dragging location over here, okay? And it's gonna be the faulting to a table by the fault. Okay. Over here. Yes, We just dragged field. So let me just move over and grab the winds over here, so let's just drag us over here. So now we have a table showing all of the locations and the winds for each location, or reach each team, actually, but what we're after is a column chart. So what I'll do is let me just go back here, make sure that still highlighted or selected and select a clustered column chart, and that's gonna magically change into a column chart over here, so I'll just expand this so that you can see all of the teams listed out over here. Okay, All of the locations, take a seat at Houston is actually the most number of Wentz with 65 wins all the way to Phoenix with the fewest wins off 21. And we just that right, you're able to create your own column chart. Now, let's go over here. You can see the access s location, the value SD wins. Call them over here. Let's go here to the former tab. You can actually make some changes. Whatever formatting that you want to play with for us, Nets just changed the color. Okay, let's just play around with it. And we have our column. Truck now. Next, ISS. We want to create some visual statistics over here on the number off teams the games played and the number off clutch games played. Okay, so that streets that affects that we want. And to do that, we want to use a card over here. So let's work with the 1st 1 number of teams. How do we display that over here? So I'm just reciting the card Let's move that over here and let's make smaller bit. Okay, this is pretty cool and number of teams, right? So which means we can grab here the team and drag it over here for the card. Now it showed you the first team. Okay, but we can make changes to that. Let's go to Fields, select here and you can change it to count. So what is going to be doing is it will be counting the number of teams inside the teams. That's table and it will show you this card over here, right? That's pretty cool. Now let's work on the next card. What we want. ISS the number off games played. So it's just dragged us here. K Reese ice. There's and the number of games played. Okay, so we have games played inside here, just dragged us here and that's actually the total number of games plate which is 2004 to 60. That's a lot okay. And the last car that we want to do yes, and to create another one and the last one is the number of clutch games that was played with all of the teams so that we just moved us here. Let's go to Team Clutch and let's get the total number of clutch games drugs over here. And we have 1270. And now we can play around again with the former thing just to make it look better. It's go to D former tab. Let's go to background, turn it on and then has changed the color to whatever you want. Same thing here, through the same steps format on this card. Changed the background color. Make it. Let's go for blue and touch games. Go format change the background color. Okay. And it's picked the color yet. Just like that. You have your tree cars over here with the statistics. Okay, so next thing yes, let's create a gauge. This cage over here, just like it. Okay. It's just put this over here. Three size it to fit here. Okay, so, four to gauge what we want to do. Yes. We want to have a gauge off the points and then showing it the minimum, the maximum and the average. Okay, so this is pretty cool. So what I'll do is four points. We'll just drag the value over here, okay? And then let's drag it to minimum maximum and the target value over here and now. We'll be doing some costume stations now because for the minimum value, we want the minimum of points to be shown for the maximum value. We want the maximum. So let's change it. And then for the target value, we want to show the average, and now you can see it over here. The minimum it's 98 Maximus 1 13 and the average ISS Llano six has shown over here. So that's where the gauge and same thing let's change the corner just to make it different . So let's go to Dana Collars. Just change it to say violent over here. Okay, cool. Now let's work on next with maps. So for maps did, it's really cool, because what it can do, it's never just select the map. What I can do right now is just show you the locations right inside the map, and the circles will represent the size of whatever value that is select. So for our map, what we want to show is all of the locations of the teams and then the circles the size of the circles will be reflecting the number off wind. So which means the greater the number of wins for a team than the bigger the circle. Okay, so we would expect Houston, for example, to have the biggest circle in our map. Okay, So what we'll do right now is let me just drag the location, okay? To location for the map, right? And then four d SISE. We'll be dragging the number of wins in here and just like that. So let me just precise that so that he can see it better. You can have see all of the locations listed out over here, and it was intelligent enough to determine that most of the locations are within the United States. We should swine. I it zoomed into United States over here and the size right in the circles. Okay, so if we select, for example, this one right, if it's Golden State, okay, If we say that this one right, it's a big circle. So you know it's Houston over here. That's to go to the circle that we think that's the smallest one. Is this one over here know, just Memphis. Let's go to Finnic. So we're here. There you go so I could see that it hide it, Fix. That's smallest circle over here. So it's pretty cool, right? We don't even doing anything. It was able to find the locations for you, and then it represented the number of wins. Ask different circles so that it provides you a geographical obvious worldview. Okay, Another cool thing is, whenever we select that one of the records over here, Okay, you could see, like, for example, this is Denver, right? It was able to interact with the rest off the graphs or the visualizations we have over here. It's just one team, right that we selected. That team played 82 games and Denver played 40 clutch games, so we could do the same for the others. And the numbers will change because they're related to one another. Okay, so that's really, really cool with the interactivity off the visualizations in part B, I Okay, so let me just select outside so everything gets selected again. OK, so this system app and we can actually go to format and make some changes again. So if you want a different map style, for example, let's change this to an aerial view. And now you have a different a different view over here. Okay, that for the map. Now let's try now for the column in light chart. So let's have a look. Here we have the Lining Clustered column chart. Select that. Okay, so let's play around with it and what we want to show here. It's the location. And then in the column, right? The columns over here. We want to show the number of wins, so it would look similar to this. But we want to add a couple off different stats in here for the lines we wanted to be. The defensive rating and the points and the number off clutch games. Okay, so that's a lot off data that we want to fit it, but it's gonna be looking good. So first things first. We want to add the location in here in the shared access and the column values. We want to show if the number off went so let's just drag this year column values. Okay, so it's looking very similar to this, but chart. Okay, so let me just extend, isn't it? I think I might need to make this smarter and then Let's move this chart over here, Okay, now we have this for the lines. What we want is the defense of rating, so we can just go over to team defense. Lying values. Just drag defense rating over here and now it gets Yeah, we can see it now over here and nexus. We want the points so we'll drag the points from teams stats to lying values. And there's another line exhibiting the points, the point values. And last but not least, we want to show the clutch games as well as a line value. Drag it over here and you have your touch games now shown asked the yellow line over here. And what ISS wonderful with this one is. Thanks to our data model, we were able to relate all of the records to one another between this tree tables, and you were able to drag different values from different tables. Right? In just a single graph, you were able to drag three fields over here. Such games defense rating right points over here as line values, and they're from separate tables and power bi. I was able to determine from which team right each fairly be lost to, thanks to our relationships that we did with the data model. Okay, so the last chart that we want to play with ISS the pie chart. So let's go to tie over here. Okay, let's add this pie. And what we want to show is the location and the number of clutch games for each location. So we'll just be going over here to location as the legend and then for the values drag over the touch games. And now you have your pie chart shown over here for all of the locations. Okay, so I hope you had fun over here. You can play around with it even more. Try on the different visualizations because it's very easy to play with your data. And it's very appealing. A swell s. You enjoy the process. Okay, so the next one will work on after is once we have our report ready, such as this one will now be publishing it to the power being website so that you can share it with your colleagues 22. Advanced - Publishing: Okay, now we're happy with this report that we created, and we can't wait to show it to everybody else. Okay, so what we'll do now is you have to make sure that you have done the creating the account for the par be a website. Because we'll need that too low again. And let's go to home. Let's go to publish. I want to save your changes. Yes, let's save it first. Okay, so I will say this. Now ask 2017 2018 that we're here. And once we have that saved, we need to sign it. Right, Because we'll be publishing it to D Power bi I website Go sign in. Okay. And then it's gonna be asking for the password and the default. It's my workspace. That's looking good. It's got select. Okay, so I've already done this before, right? So it's just asking me to replace the data said So let me replace that. And it's publishing it now to the power bi a website. Okay, so let's just wait for it to upload everything and wants to. That's pretty quick math success, and that's click on this link so that it will bring us straight to the power bi a website. Okay, Now let's go to my work space over here and already published a few before. And let's go to reports. And it is where your new file has been uploaded to. So if we remember, the name was N ba 2017 2018. So let's click that. Okay, so you should be having that a swell inside your workspace and this deck sack. Same report that we did, right? All of the charge, the cards, the gauged pie chart, right. And even the map over here. Okay, now, once we have this over here, at least you can see the exact same thing. You have the exact same experience as well over here. So clicking on it will give you the same behavior, right spell as what we did on par be a desktop. Okay, so the next thing that we're gonna be doing its we want to create a dashboard because dashboards are pretty cool. If you want to get specific parts off reports and merge it into a single dashboard and you can do that Easy t in here. So for here for for example, we don't want all of this reports that say It's overwhelming for you to see all of this charts in one single page or one single dashboard. So what we can do is just let's say you want the pie chart and we want this one the map for here, Justice to into a new desk word. You can select the pin and you can just do that by hovering over that say for the map and said I pin right and then party. I was gonna be asking you, Where do you want a pin? This? Let's say you dashboard and then it's a N b a new dashboard and to go to pin Okay, it's gonna be saying that it's pain to the dashboard. Do you want to see it now? No, not yet. I want that's closeness. I want to add first the pie chart as well. Let's go to pin now. It's gonna be asking you, Where do you want a peanut to? Since we created our NB new dashboard thinking, Let's select that filled up in It's kind of saying, OK, that's done. Now let's see art, you dashboard and there we have it right. He just have this to visualizations the one that we selected into our own dashboard. So which means this just got it from that report and created a new dashboard out of it. And it's pretty cool. So which means if you have multiple reports and let's say you don't want to go back and forth between those multiple reports, they just want specific parts off those multiple reports. You can use that pin functionality and create your own new dashboard. Not if it okay, so that you have one central location to look at and you have all of your information in one place. Okay, since we've already publish it here in the power bi website, then you can also share it okay with others so you could see here shared with me. And you can do that for other people and the holding with this one ISS. It's a seamless experience because with our experience right now, when we created this part B i desktop solution, you don't even care if you're working with part P. But you're working with park re or your recommit part of you, the Kolding. It is a seamless experience for you because they've all been integrate together. OK, so I hope that you've enjoyed this exercise off using live data all the way from getting data and up to the way off publishing here in the par be a website. 23. Advanced - Power BI Website: so we depart, be a website. What we've done so far is we're able to publish reports to it and then create our own dashboard. So I wanted to go true with you right now for the website. There's a whole lot more that you could do it. It's so let's go over them one by one. So what we have right now is the home page. So you can see over here to favorites and frequents on which dashboards and reports that you've been opening and reviewing s Well, okay, so over here already have a couple created. OK, so you could see the recent There's some recommended APS a swell and then somehow to videos is following your home page. Okay, And then let's go to the favorites. So if we go to favorites, there's not at the moment because I haven't marked any. It's pretty easy to do so by clicking the star icon and Kane s. So let's do that later. Now let's go to recent. So it's like your browser history where you could just see on which ones have you accessed about ago or days before? Okay, And let's go to app. So APS On the other hand, it's only available for pro licences. Okay, so right now I'm just using a free one. So let me just zoom out a bit, so this doesn't have it. So I wanted to show the difference is a swell if you have a pro license. So let me just click the get apt over here. And abs are pretty much ready made. Okay, Ready made for you. So if you want to use it quickly, then you could just use APS a swell because they already prepared dashboard, for example, for you to use. Okay. So forgettable. Let me just select this one. Microbe sample sales and marketing preview. If you get it Now, it's just gonna tell me you don't have a license yet for the pro version. So it's gonna ask me do you want to get one? Okay. So let me just wait for your sweet me And there you go operate to power. Bi I pro and came so you could actually try it for free for 60 days. Or if you want to upgrade it a swell, and you could do that. Okay, but for now, we'll just be focusing on the free features off part B I website. Now let's go to share with me. Okay, so there's no content yet shared. But if other people would share with you that you would see it here. So if, for example, you views one drive, then experience will be similar. Because when you share folders with other people or if sure fouls that it will appear on the district section. Let's go to work spaces. Okay, So for workspaces to default, this my workspace. And if you create another workspace right to, for example, group your dashboards or reports it's the same thing. Okay, is only available to par by pro. But if it's not important to you, then it's still workable. You could just put everything under my workspace. Now let's go to my work space, and this is pretty much everything that's in here. Okay? Their dashboards that reports. Okay, workbooks and it sets. Now what? I want to focus on ISS with the dashboards. This is the one that we created. Okay, so for the MBA and then for the reports and one of the favorite features that I have over here Okay, so is de insights and can't quick insight over here. So they just go over. I comes one by one. So we have share. Okay, We have analyze in excel. We have quick insides. We have fewer dated. We have settings, Okay? And then we have to eat forward. A shared Oh, this is not available for free. So you'll have to use part by pro, and then you can share and collaborate with other people. Okay. And then this is analyzing excel. So it's just same thing, right? It's with power bi pro. But one thing that's really nice. It's day quick insights. So if we click on this, Okay, we have sales numbers. Where? And we have sales numbers from different salespeople, and it also has chronological information. Right? So which means the dates When did it take place? Okay, so let's just go to view insights and what it will do for you. Yes, could see, over here, a subset of your data was analysed and the following insights were found. And the cool thing is, it just performs analysis and create scraps for you. Okay, so if you're running out of ideas, if you want to see other trans that you might have overlooked. Then this could be a good tool for you to give you new ideas. OK, so you could see here, write it even suggests on what patterns or trends it has discovered. So there is a correlation between sales and China partners. Okay, so if we just scroll down there, there's quite a lot. There's quite a lot in here. Okay, Some of them might not be useful, but some of them could be okay. So even here, you could see sales is trending upwards for John. Okay, so if you miss that, then at least this quick insights tool will tell you. Okay, So China partner is he's trending downwards for French and soft drinks. McKay so rd becoming more health conscious, for example. Ok, so these are some of the things that it could could help you out. Okay, so there's there's quite a lot, actually, if you see a scroll bar over here, this is quite a lot. So let me just go back here so he could play around with this one, okay? And then what we'll do right now If the seaview related Okay, so no related content, and then just the settings and then the So what I'll do right now is let me just go over our dashboard, OK? We've created some nb a dashboards over here, so let me just open and being new dashboard over here, okay? There are a couple of options over here. So, for example, add tile. Do you wanna add any major a text box, right? Or video? So you could cost the mice your dashboard as well over here and comments. Okay. So just go for a start. A conversation, right? So it's just add a comment in here. Hello? Thank a post. Okay, so you just start a chat. We related. That's the one we saw. Logo status featured. So if this is your featured dashboard, if you wanted to be this way, So just go said it's official passport over here. Okay, Favorite. So this is the one we discussed of all go free markets, his favorite. Then it's gonna show up straight in your favorite section, subscribe share, and it's gonna tell you that Great first. Okay, review. So that's about view if you go phone view. So if you used the par by full nap, then you can view it this way. Okay, so it's just a preview on how it looks like if you open it via mobile. So let's go back here. Hopes went back. So I'm just hoping this again. And there's not a cool thing in here. So there's asked a question about your data so you could go for Q and a axity. So if we say how many wins thus Houston have because we're talking about the N b A and for this team, how many wins? And it was able to filter your data and show you. OK, here's D data for Houston, right? Pretty cool. So you could see her there. Six if I went. Now let's check first now and let's go back and you can actually get different phrases from it. So team stats, for example. Okay. And then let's go for by win. OK, so you could see it created a chart for you. Okay, so I could play around with it, so that's a sort of by team. Just by typing this out, it was able to show you the data and that it sorted it alphabetically by the team name. Okay, so you see 76 years on top. And then at the very end, it's the Wizards. Okay, so if we say that's trying descending order and see if it can understand that and it's critical, right, it's able to understand and restarts now on top, right? And then the 70 Sixers are at the bottom, right, because it's now a range by the team name but in reverse order. Okay, so that's with Q and A. So once you're done with that, and then once you start clicking on the dashboard tiles over here. So, for example, if I click this, it will just redirect me straight to the report that contains this actual pin. Okay, so that's really nice. So I could see here and being you dashboard and that it redirected me to N ba 2017 trading 18 report. Okay, so that's pretty much it for the par be a website. So have fun, but it just play around of it, and there's a lot off things for you to discover 24. Your Project - Create your own Dashboard!: Now it's your turn to create your own part b a dashboard. And for this assignment, we want you to create a dashboard with tree charts. Okay, so the 1st 1 is we want you to get the total sales by channel partners were in the channel . Partners are sorted alphabetically. Okay, so that's number one. The 2nd 1 is total sales per year, and it split by quarters 1 to 4. Because there are 4/4 in a year. And the ter chart is total sales per product in the years 2012 2013 and 2014. Okay, so please ensure once you finish creating your visualizations, please ensure to save your work in a zip file and then upload it. Okay, So that we will be more than excited to review it and then give you feedback on your work. Okay, So when it comes to determining which visualizations you want to use, it's all up to you. Okay, so we're living it to your imagination. I want you to get loose, OK? And have fun using part B. I desktop. So whatever you think is the best way to show that information. Go for it. I can still have fun and looking forward to see your assignment 25. Advanced Tips - Introduction on the Data Setup: Hi, this is Brian Hall and we will be discussing about the different tips and tricks when it comes to using Power BI Desktop. Ok, so what we have here right now is this is our default Dashboard that we're going to be starting with. Ok, so what I want to go over quickly is the data structure that we have right now, which is pretty much the data that we have already loaded into this solution. Ok, so you have an idea on what we're working with. I won't be showing you the step-by-step on how we were able to load this data over here, right? But I'll just show you what we have inside this Power BI solution, okay? Over here we have on the data, we have sales and then salespersons. So it's a, pretty much a simple structure over here where in we have this two tables and then we have a relationship linking these two tables together. Ok, so over here on sales, right, you have the costumer to products that date, that year to sales amount, okay? And then we have the sales quarter, and then we have the channel partner. And then on the leftmost column, you can see over here there's a sales person ID, so we don't have the salesperson details over here, right? But the ID would be able to tell us if we jump over to the Salesperson table. Okay. Then you would be able to see right on the details off the salesperson. So 1-2-3, 4-5-6 over here, and then we have the names and nationality and then the date of birth. Okay, so pretty much we have this two tables linked together through the sales person ID so that when you go to the sales table, you can just retrieve the salesperson information based on the ID. So just to show you quickly the relationship, you can see over here, right? We have the salesperson and then we have the sales linked together. So if you just double-click on the relationship, right, you can just check quickly that it's based on the sales person ID that we're linking disc two tables together. Okay, so that's pretty much for the relationships and the data. And then we'll just jump over quickly, right, so that we can start creating the different vis walls over here. And then I can just show you the tips inside Power BI. 26. Advanced Tips - Using Bookmarks to Save Space: Okay, so for this Power BI tip, what we will be discussing about the use of bookmarks. And then we can use it creatively to increase the amount of space on our Dashboard over here. But first question is, you might be wondering what our bookmarks all about, OK, so I'll show you quickly on where we can see the bookmarks functionality over here so that you can have a better idea. I'll just go to View and then select bookmarks over here. And you can see this one like the description, use bookmarks to capture key data for yourself and others feel their data to get to the state you want to capture, kick add. Okay, so to explain this better for bookmarks, it's pretty much a snapshot off the state, off your dashboard at the moment. So it's a really cool way if you want to keep whatever settings that you have over here, right, or whatever selected are filtered, et cetera, then bookmark will allow you to save that over here. And then once you click on it, it will just restore to that state that you have bookmarked. Okay, so we'll see this in action. But there's another coup way that we can use bookmarks for and which is increasing the space over here by hiding. And I'm hiding specific panels that we created over here. Okay, so let's just go straight to the action over here. And what we're going to need first is create a button, okay, so that once we click on it, we want to show a panel over here that has a couple of slicers that we can use to filter data on our Dashboard. Okay, and then there's going to be another button that we click later, right? And it will hide the panel, okay? And this is where bookmarks are going to be used. Okay, so that's just go to insert and then create a couple of buttons. So I'll click buttons and then this just slag bookmark, and then we can have some fun with this. So let me just drag the bottom over here, right? And then I can just extend this to make this somewhat bigger. Okay, cool. Then on the bottom text, I'll just turn this on, right? And then let's just type in, let's say Fordist button. What we want to happen is once you click on this, right, it will show off our slicers. Okay, so it's a bit small, sol just increase the text size to maybe 25. Okay, looks good, right? So we have this bottom over here, but of course, nothing's happening yet. Okay? So that's just complete all of our elements first in this dashboard. And then what I'll do now is let's say, let's add another rectangle. Okay, so this is going to be our panel, right? Let's just put it on top of the but on over here, right. And then let's just extend this all the way down. Okay, looks good, right? And then this is our panel. And then let's say we want to add a couple of Slicers over here. And I'll just click away, right? And then let's go to slicers. Okay, so this is our first nicer, right? I'll just put this inside the panel. And then let's say for this slicer over here, nests judges at the name of the person. Okay, so let's just add this to the field over here, right? And then we have of the first names right off the salesperson, Brian Homer, chatter. So let's say a set of Homer, right? Then it will just effect. Filter anything that's on the Dashboard. Okay, so slash, working perfectly fine. That's just a justice a bit. Make the smaller. And then let's say that's at a and under slicer over here that say I want a slicer, right? That's just click away and add another one and add a slicer for the order date. Okay, so they've just a justice add the order date to the field of the slicer, right? And then we have another next lecture over here. Ok, so looking good, you just drag and drop to specify which dates afforded data that you want to include inside your dashboard. Okay? So we have our two slicers ready, right? And then we have our panel. So what we need next is another button so that once we click on this right, it's going to hide the panel and high D slicers. Okay. So let me just make some space. Okay, I'll just move this laser is a bit down over here. Okay, and then let's just add another bottom. So I'll just go again to insert and then that's select buttons. And then let's go for bookmark. And you know the drill by now, right now let me just try to move this over here and then I'll just make some quick editing to make this into another button over here. Same thing, but on text. Okay, and then let's just add in NYC hide slicers, four detects, and then I'll just go for the same text size from a while ago. Okay, let's just go for 25. And pretty much from a user interface perspective, right? We have everything set up, but we still don't have the functionality. And this is where the magic will happen, ok, this is where bookmarks come in. So once were pretty much happy with this election over here. So let me just unselect this homework, okay? And then we have distinct, okay? So what we have right now, right? This is pretty good when it comes to showing the slicers panel over here, okay? Which means this is a place where a good place for us to add a bookmark. So what I'll do is I'll just add the bookmark over here. Okay, a simple list that once I've click Add OK, I'll just do a quick rename over here. And then let's say this is the show slicers. Okay? This is the show slicers state, okay, because right now what we have is everything is visible over here, okay. And then one thing that I want you to do a swell is when it comes to adding this new bookmark is unchecked. Ok, the data option over here, okay, so what about filters? Because when you use filters as well, Beatle one, the field theirs, getting overridden every time when you select their specific bookmark. Okay, so we have this shall slice, which right now, we're pretty much happy with this. And then what I'll do right now is we need to hide everything over here, okay? So that we can have a state. We're in, the slicers panel is hit it. So to be able to do that, like how do I toggle the visibility? We can do that quickly. Over here. I'll just go over to the view, okay, the View tab in the ribbon and then let's just go for selection. So this one, the selections pane, will show you all of the elements in your screen quickly, okay? And then you will be able to toggle the visibility of those swell. Okay, so what we have over here, right, if I select this one, this is the hide sliders button. And that's just hide this. So I'll just toggle this. And we have the slicer, I'll hide this as well. We have the second slide or so, I'll hide this as well. We have the rectangle, okay, I'll hide this as well. And we have this button right now for to show slicers. Okay, so this is the state. Ok, I hope you're getting the gist of it right now, right? This is the state that we want to show when the Slater's panel is hidden. Okay, so which means this is the time for us to add another bookmarks. I'll just click add and then same thing, right? I'll just rename this and let's say this is the hide slicers bookmark, okay, this is the state. And then same thing. I'll just untick the data option over here. And now we have two bookmarks, OK, showing and hiding. If I click on the bookmark right, show slicers and you would see this one being shown depending on, if I click on hide slicers, right, then everything gets hidden and then you just have the shoals titers button over here. But you might be asking right now this question, right? We're seeing the flow. We're seeing the flow on what we want to happen from showing and then hiding, right? But this buttons that we have right now still don't have that functionality. So I'll just click on the button right? And then under d options, there's a specific one that interests us, which is to action. And over here is this is the action that it will execute when you click on that button, ok, so over here we have E type bookmark, which is already selected for us, right? And then over here, the specific bookmark is what we want to trigger, right? Which state that we want to show when this is clicked. And definitely we want show slicers to happen. Ok, so once we click on it, right now, let's just try it now. So I'm holding Control over here, right? Control-click. If I click on it, then we have this state over here, right? We have the panel showing up right now because it was able to trigger the show Slater's bookmark. Now, we're still not done because for the second button, I'll just click on this. And then same thing. We want to set the action and the action as well would be the type bookmark. And for that specific bookmark will just say that the height slicers, ok, so once we click on desk, it will trigger the state forward to hide slicers and everything will be hidden. So I'll test it out. I'll hold control over here and click on the button and everything gets hidden. Okay, so I hope this shows you on how powerful you can use bookmarks and you can get created on how to move from one state to another. 27. Advanced Tips - Drillthrough: Now let's talk about using drilled through inside Power BI. So this is a really neat feature out of the box and that's included inside Power BI. And it allows us to be able to drill down to the details that we will be selecting. So in our example, let's say what we want to happen is over here, right, in this specific column chart over here, right? We want to show or specific details for a specific salesperson. Ok, so let's say my name over here. If I click on Brian and then I can just drill down to the actual line items would make up this specific column of values. Okay, so it's just more off gridding down because it is a high level, right? And then you want to find out what are the specific details that make up the value over here, then it's very easy to do that inside Power BI. Ok, so what I'll do right now if let's just add a new page, I'll just click on the plus icon and then we have a new page over here. And then let's create a new table that would contain the drilldown details step we want to show. Ok, so I'll just go over here to this. Well decisions, I'll select table, right? And then we have this table over here. I'll just increase the size a bit and then let's add the fields that we want to show when it comes to drilling down. Okay, so let's say for the details, we want the firstName, so I'll just drag the firstname over here, right, two values, okay? So we have this one and then that's just go for sales numbers. Okay, that's pretty good as well as add to devalues, right? We have sales and then that's also display the order date. Okay, so I'm just adding drag-and-drop over here right inside our table now. And let's just make some couple of minor modifications. We don't want the sum of sales to be displayed. I'll just go for it. Don't summarize. Okay, so all of the land and MPS get displayed and then for the order date, I don't want it to be displayed like in this fashion, so I just want it to be the order date only. Okay? So I'll just select order date and then we have pretty much the table values over here. If I just scroll down right, you could just see everybody in here. But we're missing the crucial link. So to be able to enable the drill true function inside Power BI, right? We want to add the drill true fields. Over here. You can see at the bottom right it's a bit like not exactly visible, but you can add the drilled through fields here. What I mean by that is, let's say in our case we want it to be the first thing. I'll just add this over here, right? So we have the drill true field with firstname. If I go over to page one, you'll see the magic happen since we have everything over here right inside this chart with first names. If I right-click, let's say on my name, right. You could see the option of drill through and it was able to find out that you could just go over to page two and then find these specific details under Brian. Okay. If I click on this and you could see our table and right now, it's just now just filtered to Brian because this is pretty much the details that would make up this specific column over here, right? Since we have defined a drill through field, right, for this table to be firstName. So a Power BI was smart enough to be able to link this over here. If I just right-click on, let's say a different column that say for Homer. And you just select real through again and then it will just change the table values. So over here we have Homer. Okay, so really easy out of the box. And let's try another example. And let's see, let's go again to page one and then let's say I just selected year 2013. Okay? And then we have specific data right now. Alright, and then let's just go to Michael for example. I'll just right-click again, go to drill through. And then you would be surprised because it's smart enough and now it just shows you the details for Michael. And then since we selected 2013, right, then only 2013 order dates are displayed over here. Okay, so it's a really cool way if you want to get to the nitty-gritty of details off a specific result, then you could use drill through to provide that functionality for you. 28. Advanced Tips - Date Hierarchy: Okay, now let's discuss about a cool out of the box feature off Power BI, which is about Using Hierarchies on dates. Okay, so I just want to show you quickly on how we can use that to leverage our, let's say line charts over here and make it more interactive for our users. So let's create a new line chart to be able to see this in next show. So I'll just go over here, right, and then select a Line Chart. And then let's just expand this a bit so that you can have a look. Okay, so for this one is just add a couple of fields. So for our axis, we want the order date to be shown over here, right? And then for our sales, we want it to be shown SD values of this specific line chart. Okay, so what we have right now is we have a simple line chart for the year 201220132014. And then it just totals all of the sales amounts over here. Okay. So pretty much straight forward out of the box, right? But this is where the 3D cold thing happens inside the axis. If you noticed it before the order date, it has the default hierarchy created right away. We have the year, we have the quarter, we have the month, and then we have the date. And this takes like effect if we just have a look over here, There's that bought on, off, drill down. If I click on this, right, you can just go over a specific data point. Let's say on 2013, I'll just click on it, right? And then it just goes over to the next level down, which is insight 2013. If it gets over right, it's now by quarter, quarter two, quarter, three, quarter four. And if I just click on another quarter, right, it now jumps over to a specific month, right? And if I click again it say on September, then it shows me the data points per day over here, right? That's really cool out of the box because it's just follows the hierarchy over hero for year, quarter, month, and day. Okay, so let me just move out. I'll just go up over here, right? It's just go up this, go back to our original state over here. I'll just turn it off. And then we have the next option, go to the next level in the hierarchy. So what we will do is it moved, group it in the next level down. But regardless of the current level that we're in. Okay. So it's a bit unclear, right? But what I'll do is let's just show this quickly. Let's just click on this, right? What happens next is it will group your data right now by quarters, but it doesn't care about the year. So which means quarter one over here is pretty much quarter 1's off all 201320122014, right? Pretty much the three years combined. It doesn't differentiate between the years, right? Same goes for quarter three, quarter four, right? So I click on this again, and now we have the month, which is the next level down, but put in much the total of all Jen mars. Okay. All February. So chatter. Okay. So for the month over here with no nick separation between the years or say specific quarters. Okay. So if I drill down again, then it will just show you the different days. So all of the first days of the month, right? A chandra. Ok. So let me just go up again. Let's go back to our original state. And then we have this next option, expand all down one level in the hierarchy. Okay? So this one, okay, provides you now a timeline because it's now able to expand to the next level while keeping the previous hierarchy into consideration. So if I click this, so now it jumps from year to quarter. But you could see over here, right? It shows all of the quarter starting from 2012, right? Which is to start off our data until 2014. So it shows you the different quarters, quarter one for 2012, quarter to 34, and then jump over to the 2013 quarter one. And then he just goes on. Okay. So it shows you the entire timeline. So we could also show this or use this if you want, like a complete picture from start to finish. And if I click on this again, then you have now into the month level. And it shows it that right from the start of 2012, right. All of the months, all the way to 2014, which is the last month of our data. Then same thing as valid if I just go down again, then pretty much the day-to-day fluctuations off our sales data. Okay, so it's really cool out of the boss Power. Bi is able to provide this for you and you can just drill down to any level that you prefer. 29. Advanced Tips - Forecasting: Another cool feature of Power BI is it's able to provide forecasting abilities right out of the box. Okay, so for example, let's say you have a priceless, okay, over time, right off historical data, then you would be able to load this to Power BI and then be able to create a graph, right? And then afterwards you can even add a forecasting portion that it will try to predict on how your data will behave, let's say in a specific amount of time in the future. Okay, so that's really cool. So let's say over here it is spreadsheet, right? I have created just a random spreadsheet of values over here, right? But you have the dates from start to finish. Okay, so we have a lot of data points over here that we want to use and then display inside our Power BI dashboard. And in after wires we can use forecasting to predict on how our data will it behave, okay, so you could just assume that, and let's say this might be a price of a specific item, right? That you're targeting are an essay. This is a price of a specific stock that you are monitoring. And then you could just use Power BI to forecast future values for you. Okay, so we have this values over here, so I'll just do a quick get theta so that we could just load this inside our power BI dashboard. Ok, so I'll just go for excel over here, and then that's just look for our specific spreadsheet. So we have the price watch over here, right? And then let's just select this and have this loaded. Okay, so now we have our table, so pretty much straightforward. We're happy with this one. And then since we don't need any transformations over here, I'll just go for a load, it get us, we just need the data straight away. And then afterwards, right after this gets loaded, what will do is we will create a line chart over here so that we can use de forecasting abilities right off the boss for Power BI. Okay, so let's just go for Apply Changes. Let's have a quick look afterwards. Ok, so once we're good, let's have a quick look to our data that's just scroll over here and then let's go to the Price watch. So let's just have a quick sneak peek N, It looks good. We have our date and then we have the price. So let's just jump over, over here. Okay, and then let's just create a quick line charts. So I'll just extend this over here and then let's just add our values so far to axis, we want this to be the date. So I'll just go over to the Price watch table. And then I'll just drag the date over here, right? And then we have the value for our price over here, okay? And what we want to do is for the dates, I just want the date by itself. I'll just select Date, right? Not the hierarchy and then forward a price. And let's say we just want it to show this as the sum for the dates. Ok, so this one's good. Now we have our practice right, displayed by the date over here. Then we can use now the forecasting ability of Power BI. So to be able to look for debt is located over here, under analytics. So we have the analytics icon, the magnifying glass, then to be able to look 40 forecasting, let's just have a quick look at forecasts over here, right? And you can just select Add. So let's just play around with the settings. So for example, I'll just scroll down over here. We could just go for it. Let's say how many points of data that you want to be forecasted by PowerBI, let's say 100 points over here. So you can see the great area over here, okay? And then confidence interval, I'm happy with 95%, right? And you can just change the intervals of swell. And then let's say for seasonality and let's say, let's just add the 30.2 centrality over here. If your data, let's say fluctuates between every month, then I would place a seasonality value over here. Okay, so let me just go back over here somehow it didn't apply on, just go for 30. And then click apply over here, right? And then you would now be able to see on Power BI forecasting the specific values over here. Json will do if images resize this and move this somewhere here because I want to extend this graph and then you can have a better look on how it looks like. So let me just move this over here. And then you would be able to see that it was able to forecast a couple of points over here so you could change and play around with this. Let's say this is somehow it didn't take effect. Let me just change this again, a 100 points. I'll click Apply. And now it tries to forecast future values for you. And you could also change this to years, quarters, months, right? So that it's easier to, let's say, apply specific like durations over here. So that's a one-year and you could see that it tried to apply from October 20222021 out of the box. You can now play around using Power BI. 30. Advanced Tips - Word Wrap, Mini Map and Advanced Editor Options: Okay, so let's talk about this tip wherein we want to have a better environment when it comes to editing MCO inside park query, inside Power BI. Ok, so I'll just show this to you quickly because if you work with M coat a lot, then this will make your life easier. Okay, so what I'll do right now is let's just dive in and show you some M codes so that we can make a couple of quick changes. Ok, so over here, let's just select any query over here for sales. I'll just right-click and go to Edit query. And then we can just show you quickly on how the M code looks like and k. So M code is really useful when it comes to working on a deeper level so that you can further customize your query. That's not possible inside the user interface, okay? So it just opens up a whole world of possibilities for you when you're working with M code. Ok, so I'll just jump over to M code and just go to the View tab and then initial select the Advanced Editor. So once we have the advanced editor, this is where we want to spice things up, okay, so what I want to discuss with you, but this tip is word wrap, Mickey, meaning maps, and then more options that we could play around with the advanced editor. Because over here, right, we have our M code, okay? And there's a couple of things that makes it a bit difficult to edit the code because for example, nine numbers, there's no word wrap. Okay, so if you work with code editors before then it's bit lacking. Over here. It just shows to you if there are syntax errors or not. But over here on the right corner, upper right corner over here, there's display options. So there's a couple of options that can make editing a lot easier. So for example, display line numbers. This is one of my Definitely like core functions that you need on the code editor. And let's say render whitespace so they could see the dots over here on the whitespaces. And then there's also a minimap which is pretty handy because if you have long bits of code, you can just use this to navigate quickly and have a bird's eye view on how your code looks like. You just jump over swell. And then of course we're dropped. Okay, so once we have word wrap, I'll just removed the minimap for now, right? And now we can see all of your code inside a screen and there's no need to scroll left to right. Okay, so just jump over to the display options and any peaches change on how your advanced editor looks like to be able to modify and code. 31. Advanced Tips - Format Painter: Now let's talk about copying the formatting when it comes to our different visualizations. So for example, it's a, an Excel. When I have like cell formatting, it's very easy for me to just use the Format Painter to copy from one cell to another and then keep all the formatting without any need for me to redo the steps. Okay, so that's very, very useful inside Excel. But did you know that we have the exact same thing inside Power BI, which is this bottom over here, Format Painter. Ok. So what I'll do right now is I will just show you quickly on how we can do that. So damages drag this a bit over here, okay. And then I'll just say this card over here. I want to copy the formatting as well to a new card and asked to read easy to do. So what I'll do is let me just add a new card, okay, over here, right? And then let's just set this up quickly, lit the value to display, okay, and I just want to show the sales values. Okay. So I'm just making a carbon copy of desk card over here. So I'll just select this and then I'll select Format Painter. And once you click on this specific card, it just copies the formatting straight up. Okay, so you might be thinking, hold on, it's not carbon copy it. So a lot I'll do is I'll just go over here. Right. Because it doesn't have the title. I'll just go to the title, right. And then I'll just type in total sales. Once I have that, it's now a carbon copy and then I will just extend this a bit and they're exactly the same when it comes to formatting. So there was no need for me to go over decisis, right? The color, anything, our fonts or any customizations that you need from a formatting standpoint. Because you could just use format painter to duplicate and copy this quickly for you. 32. Advanced Tips - Bookmarks and Filters: Now let's talk about using bookmarks. Bookmarks inside Power BI, you can pretty much captured the configured view of your pages you're filtering or even the state of your visceral silver here. Okay, so that you can just go back to that state by going to that specific bookmark. Just jump over from one bookmark to another. Okay, so once we have like a list of bookmarks that we've created, then you could use it to tell a story. So let's say you have a presentation that any could just arrange the bookmarks that tell your story. And then you could just step through the different bookmarks that you have to show the different states inside your dashboard. So D could just highlight a series of insights, right? Or just make a story out of it. So what I'll do right now is I'll just show you quickly on how we can create bookmarks. And you could just jump from one state to another while sharing your story. Okay, so I'll just go over to the view, okay, over here inside our ribbon and then we have the bookmarks pain, and I'll just select bookmarks. So what I'll do right now is under filters over here, right? You could just add data fields on our filter, right? So let's say, let's just go for first_name. And then let's say we want to tell the story about different groups. So pretty much we have how many sales people over here? So we have 123456. Okay, so for our scenario, let's say we have a story where a, we want to have three different groups over here. And if we want to quickly compare and share story on how one group differs from another. So we're going to have group a, group B, and then group C, which would have to each. Okay, so lot of doing right now is I'll just select, let's say Brian and in Homer over here, right? So we just have, they're too like beta over here and the numbers over here displaying and in very quickly, and you just go for, Add a new bookmark over here. And then let's say, and it's just rename this and then give this a name off grew up a, okay, so very easy to use that, but more because of the leverage of this, minimize this one so you could see our dashboard better. So that's just undo this, right? And then let's go for group B. And let's say it's going to be Ian and at John over here, right? And then all of their numbers are displayed. And I'll just add another bookmark over here. So I'll just go for group B. Okay, looking good. And then let's undo this one's right. And ns go for col and my kill for our final group. And I'll add an under bookmark. Okay, let's rename this right to group C. Okay, so now we have all of our bookmarks and let's say you're discussing this, are presenting to a group of people and you can just jump right from one group to another it quickly. So I'll just select group a. Let's say we're discussing group a, right? We have Brian and Homer being displayed, alter numbers, total sales, etc. Alright, and then let's jump over to Group B. And then we have Ian and John. All right, and then let's jump over to Group c. And now we have cow and Michael, because this is very easy because it allows you to save the state and you want be, let's see, like clicking on the different selections and it just takes a lot of time. And also it will just lag or stall your presentation. And the cool thing with this one is it's not just for presentation purposes, but let's see if you have a unique set of settings that you go to every single time, then there's no need for you to keep on clicking and reducing the settings over and over again. You could just use bookmarks and interest on a single click. It will bring you to that state very quickly. 33. Thank You!: Thank you so much for taking this course. Okay, So if this has brought value to you and you have learned something new piece, leave your feedback as well. Okay, so just click on. Sure. And then you could just give your honest feedback to other students can also discover this class. Okay, So what I have here opened, it's actually one of my classes. If you want to learn more about what I'm teaching at the moment, just click on the link over here, right? My name is over here. Just click, OK? You just scroll down and you could see over what in my up to okay with my profile. And if you just scroll down, we have over here a lot more courses that I teach to you. So if you're more curious about Excel Goodness, I have a lot off Excel stuff to teach you. Okay? Shortcuts. Park re. Okay, par be. I accept formula. This are few to sequel. Okay, for data basis, writing sickle Caries. Check it back up a swell. Okay. And I'll be able to show you a lot more on what you can learn. Okay, So thank you so much again for taking this class. And don't forget to live on honest review