Power BI Full Course Tutorial (8+ Hours)

Hi everyone. I'm Trish Connor Kato, and I'd like to welcome you to Microsoft Power BI. This extensive course encompasses 13 modules, covering the basics through some advanced features in the application.

The first module will explore the meaning of data analytics and the different roles available in that space. We'll outline the important roles and responsibilities of a data analyst, as that's the role we'll be functioning in when we're working in the application. We'll also explore the Power BI licensing options and their implications on the landscape of the Power BI portfolio of products and services.

In module two, we'll get hands-on in the Power BI Desktop application. This module will explore identifying and retrieving data from various data sources. You'll also learn the options for connectivity and data storage and understand the difference and performance implications of connecting directly to data versus importing it.

Module 3 is where the real work begins. The module will teach you the process of profiling and understanding the condition of the data. You will learn how to identify anomalies, look at the size and shape of the data, and perform the proper data cleaning and transforming steps to prepare the data for loading into the model.

The next module will teach you the fundamental concepts of designing and developing a data model for proper performance and scalability. This module will also help you understand and tackle many of the common data modeling issues, including relationships, security, and performance.

Module 5 will introduce you to the world of DAX, that's Data Analysis Expressions. It's a function language that's used in Power BI to create calculations. You'll learn about aggregations and the concepts of measures, calculated columns and tables, and time intelligence functions to solve calculation and data analysis problems.

We'll move on to optimizing model performance. This is where you'll be introduced to steps, processes, concepts, and data modeling best practices necessary to optimize a data model for enterprise-level performance.

Module 7 introduces you to the fundamental concepts and principles of designing and building a report, including selecting the correct visuals, designing a page layout, and applying basic, critical functionality. The important topic of designing for accessibility is also covered.

With the exception of the first module, all of the modules on this slide will be conducted in the Power BI Desktop application. Once we get to module 8, we'll begin working in the Power BI service, the online component of the application. In this module, you'll learn how to tell a compelling story through the use of dashboards and the different navigation tools available. You will be introduced to features and functionality and how to enhance dashboards for usability and insights.

The next module will teach you about paginated reports, including what they are and how they fit into Power BI. You will then learn how to build and publish a report.

The next module helps you apply additional features to enhance the report for analytical insights in the data, equipping you with the steps to use the report for actual data analysis. You will also perform advanced analytics using AI visuals on the report for even deeper and meaningful data insights.

Since we'll be working in the Power BI service, module 11 will introduce you to workspaces, including how to create and manage them. You will also learn how to share content, including reports and dashboards, and then how to learn how to distribute an app.

Module 12 focuses on managing datasets in Power BI. In this module, you will learn the concepts of managing Power BI assets, including datasets and workspaces. You will also publish datasets to the Power BI service, then refresh and secure them.

The last module in this course is about row-level security. It will teach you the steps for implementing and configuring security in Power BI to secure your Power BI assets.

Welcome to module 1, where we'll get started with Microsoft data analytics. This is the only module in the course where I'll be using a PowerPoint slide presentation to give you background information on the field of data analytics, the licensing options in Power BI, and the products and services that will be available to you. All subsequent modules will have you hands-on in Power BI. You can access this PowerPoint presentation from the video description below. So let's get started with data analytics and Microsoft.

What is data analytics? It can be described as the process of analyzing raw data to find trends and answer questions. A successful data analytics initiative will provide a clear picture of where you are, where you have been, and where you should go. The field of data analytics is very broad and expanding, and as such, there are many roles that fit in that area. There are also four primary types of data analytics: descriptive, diagnostic, predictive, and prescriptive. There are supplemental slides in this presentation that will give you more depth on each of those four types. There are also additional slides that will give you insight on the wide variety of roles that are available in this field. When we get hands-on in Power BI, we'll be functioning in the role of a data analyst.

Data analysts provide real-time insights across an organization and Power BI. That means the data analysts will connect to and transform data with advanced data preparation capabilities. They'll also create interactive data visualizations and uncover important insights. The data analyst is typically the person who publishes dashboards and shares insights to drive informed action throughout your organization.

It is important to understand the license options for Power BI as your features may vary based on the type of license that you have. The Power BI free version includes the Power BI Desktop application as well as the online Power BI service. A user with a free license can only use the Power BI service to connect to data and create reports and dashboards in the default workspace known as My Workspace. They cannot share content with others or publish content to any other workspaces. They can, however, consume content that is shared with them. As of this recording, the Power BI Pro license is estimated to be $9.99 a month. With a Pro license, users can publish content to other workspaces in addition to My Workspace. They can share dashboards, subscribe to dashboards and reports, and share with users who have a Pro license. They can also distribute content to users who have free licenses.

Power BI Premium licensing has two variations. The per-user variation, as of this recording, is about twenty dollars a month. It can do all of the same things as the Power BI Pro license but can also share with users who have a premium per-user license. In addition, premium per-user license holders can distribute content to users who have free and Pro licenses. The other variation is the Power BI Premium per capacity license. It can do all of the things as the premium per-user licensing in addition to more things. It's usually implemented at the enterprise level. There is a Word document in the video description named Website Links and More Information where you can view the Power BI pricing and product comparison website to see how the feature sets differ by licensing level. In this course, I'm using a Premium per-user license and may have features on my screen that you do not have in your version, depending on your license level.

The landscape of products and services in Power BI is amazing. Depending on your licensing, the feature set varies, but even with the free version, you'll have access to a robust set of services. The three main components of Power BI are the Power BI Desktop application, the Power BI service, which is in the cloud, and the Power BI Report Builder. Let's take a deeper look.

As a data analyst, most of the time you will be working in Power BI. You will be working in the Power BI Desktop application. From there, you can connect to over 80 data sources, transform your data, analyze it, shape and model your data. You can create calculations called measures and calculated columns, create visualizations and reports. You can publish to the Power BI service and have access to the Power Query Editor, which can help you with your data transformation.

The Power BI service is cloud-based. It allows access to some data sources. You can also create visualizations and reports there. You can create paginated reports, and that is where you go to create your dashboards. There is some overlap in what you can do between the desktop and the service, as you can see on the slide here. The two are bundled together, and as I said earlier, even the free version has a very robust feature set. Lastly, the Power BI Report Builder allows you to create paginated reports in the Power BI service. We'll explore paginated reports in a later module.

Now that we've covered the background information, let's get our feet wet in the Power BI Desktop. Yay! We made it through the background information, and from now on we'll be hands-on. You may want to pause and grab the five files on the slide from the video description. I would suggest you put all of them in the same folder on your computer.

Before we load data into Power BI, let's review the data that we are going to put in there. I've opened a sample Superstore Excel file that you grabbed from the video description so that we could kind of take a look at the data. This file represents typical orders, sales, customer, and products information spread over three sheet tabs. On the Order sheet tab, you'll notice that there is additional information that's not related directly to the order; for example, customer name, customer ID—those fields really should reside separately from the order information on the sheet. We'll learn how to address that issue later on in this course. Then we'll take a look at the Returns tab, and that just has order IDs and the returned status, and we have a Users tab which contains manager information. I'm going to go ahead and save and close this workbook, and you'll see how this data comes into Power BI in just a few moments.

Now that we've explored the sample Superstore Excel file, we're going to get into Power BI. There are many ways to launch it, like any other application. I'm going to use my start menu to launch it, so I'm navigating down to my taskbar at the bottom of my screen, and in the lower left-hand corner, I'm going to click on Start. Once the start menu opens, I'm going to click on any letter that I see so it collapses all the applications, and I'm going to click on the letter P. Underneath the letter P, you'll see all of the applications that begin with the letter P. We're going to select Power BI Desktop to launch this application.

The application is loading; it really doesn't take a long time, and you'll notice that the application opens in the background with a splash screen on top of it. The first thing we're going to do is sign in. In the middle of the splash screen, you're going to click on the yellow Get Started button. It will show you a prompt to enter your email address and another prompt for your password. Go ahead and follow the prompts and log yourself in. We'll do a comprehensive tour of the Power BI Desktop after we load the data in from Sample Superstore, but in the meantime, if you look in the upper right-hand corner, you'll notice your name, and that indicates that you're signed into the program. If you want to take a moment, go ahead and click on your name, and on that screen, you would be able to sign out, sign in as a different user, view your account, and go to the Power BI service, which is the cloud-based component of this application.

The first lesson in this module is getting data from multiple sources. We're going to use the sample Superstore Excel file as our first source of data. We'll be using other data sources in this module as well. On the Home tab of the ribbon, you'll see the arrow pointing to the Excel workbook button. Go ahead and click on that icon, and it will launch an open dialog box. Navigate to wherever you put the class files for this module, and you should see your Sample Superstores file. We're going to double-click it, or you could click it once and choose Open. You'll have a navigator window appear on your screen, and it shows the three tables that were in that Sample Superstore file. As you select each table, the preview pane to the right will show, and it shows a truncated size of the data, but you can scroll through and see some of the data that you saw in the Excel workbook. We're going to put a checkmark in front of every table, and as you select a different table, the preview pane fills with that table's information. In the bottom right-hand corner of the Navigator screen, you have three buttons: Cancel would be the same as doing the X in the upper right-hand corner; Load; and Transform data. This time around, we're going to click on the Load button. You'll learn more about the Transform data button later in this course. When you click on Load, you'll see that it's working, and you'll notice a load box that will pop up letting you know the tables that it's working on and what it's doing. You also had a yellow band briefly across the top of the screen. At this point, it doesn't look like anything much has changed in your file.

Before we get into a comprehensive tour of the Power BI environment, it'd probably be a good idea for us to go ahead and save the file. Power BI has a quick access toolbar similar to what you find in the other Microsoft applications, so in the upper left-hand corner of the screen, so there's a save icon, undo, and redo. You can't modify the toolbar at this point in Power BI Desktop like you can in the other Office applications, but we're going to go ahead and click that save icon so that we can save this file. It should route you right into your working directory, wherever that is on your computer, in a Save As dialog box. Let's name this file Sample Superstore, the same as the Excel file, and you'll notice where it says Save as type; it's giving it a .pbix extension. There are only two extensions for Power BI Desktop files: the default one is .pbix, which is a Power BI Desktop file, and your only other choice would be a .pbit, which is a template file. We're going to leave it on .pbix and click Save.

Now we're ready for the grand tour of the environment. You'll notice that it also has a title bar like every other window. Now that we've named the file, it's called Sample Superstore Power BI Desktop; before it was just saying Untitled. You have a search function right at the very top center of your screen, and again, to your right, you will see your name with all of your account information in that menu. We have a ribbon just like in the other Microsoft programs. The ribbon will change depending on what view you're in; it takes a little bit of getting used to, but I'm sure you'll get comfortable with it, and you'll see that play out very shortly. I mentioned earlier there are three views in Power BI Desktop, and the default view when you first log in and when you load data in is Report view. That's the view we're looking at right now.

The view buttons are on the left side of the screen. Like I said, we're in Report view right now, and this is the view button indicating Report View. The other two views that you have available are Data View and Modeling View. You'll see both Data and Modeling view in just a few moments. In the middle of your screen where it says Build visuals with your data, that is known as the canvas; that whole blank area is the canvas. If you look below and to the left of the canvas, you'll see that we are on page one in Report View, and you have page tabs that are very similar to Excel sheet tabs. You can add pages, delete them, duplicate them, and hide them, and you'll see that during the course. Underneath your page tabs, you'll see an area; it's a gray area at the bottom; every Microsoft program has it, and it is called the status bar. Right now we have one page in this report, and the status bar is reflecting page one of one. The status bar will populate with different information depending on what view you're in in Power BI Desktop.

Let's take a look at the right side of the screen. It may look different on yours than it does mine, but the right side of the screen has three different panels; they're actually called panes. My Filters pane is collapsed right here, so you just see it saying Filters, and it has a leftward-pointing arrow which I can use to expand it. Report view comes with these three panes, so there's the Filters pane; we'll speak more detail on it when we start working on creating visualizations in Power BI. You also have a Visualizations pane on the right side and Report view; that's the area where you go to when you want to choose what kind of visualization you'd like to put on your report. You have a host of options here, and again, we will cover that area very thoroughly later in this course.

The last remaining pane over there for right now is the Fields pane. Well, when you look in this Fields pane, you'll see the instances of what were on the three sheet tabs in Excel, so we have an Orders table, we have a Returns table, and we have a Users table. Even though they're on sheets in Excel, once you bring the data into Power BI by connecting to it through Get Data or Excel workbook, they are known as tables, so you can expand the Orders table. All of the tables come in collapsed. You can expand the Orders table, and then in Report view, you see the fields that are in the table, but not the data that's in the fields. Just to go over some symbols that you'll see in a field pane: if you notice, you have the sigma symbol in front of Customer ID as well as a couple of other fields; that's an indication from Power BI that that field contains numeric data, so whenever you see the sigma symbol, it means the field contains numeric data. If you notice the Order Date field has an expand arrow in front of it, and when you expand it, you'll see what's known as a date hierarchy. Notice the symbol in front of the hierarchy; it's indicative of a hierarchy in Power BI. You'll learn about hierarchies later in the course, but what I will say now: a hierarchy is a container of sorts for fields that you would like to kind of group together; that's what it does. So when I expand a date hierarchy, I see that the Order Date has been broken down into year, quarter, month, and day; that's what's in the date hierarchy. As we progress in the course and we do different actions in Power BI, you'll see new symbols in the Fields pane. If you'd like to take a moment and look at the fields that are in the Returns and the Users table, you'll notice when you expand the Users table that it has two columns, and they're named Column 1 and Column 2. When you're working with data, you're going to want to make sure that the column names are indicative of the data that's being stored in those columns. In the next module, you'll learn the process of transforming and cleaning your data, and that's where we will clean up that table.

Let's go over to our view buttons now, and we want to access the view that will allow us to see the data that's in the tables that we imported from Sample Superstore, so we're going to click on the Data View button on the left side of your screen. It's the second view button, and it will take us right into Data View. When we get into Data View, you'll notice that you have a new tab on your ribbon: Column tools, and as I said earlier, the ribbon will change depending on what view you're in, and it could change again depending on what you're doing in that particular view. You'll get used to it after working in this environment for a while. Data View also has the Fields pane on the right, and I can use it for navigation purposes, so if I'm looking for a particular column, I can just click on it in the Fields pane, and in Data View, it will navigate to and select that column for me. The other thing that's updated here is the status bar. In the lower left corner, you'll notice now that the status bar in Data View is telling you what table you currently have selected in the Fields pane and how many rows are in that table. It also is telling me, because I happen to be in the Profit column right now, that in that column there are over 8,900 distinct or unique values, so depending on the view, the status bar will show different things.

The last view that we're going to explore right now is called Model View, so make your way over to your view buttons, and it is the third and last view button in the list. Go ahead and click the Model View button to switch to that view. We're going to have a deep dive into table relationships once we get to module four. What I will say about Model View is that it will show a card for...

Every table that you've connected to from the outside data source, which in our case is an Excel workbook, we'll be creating table relationships in this View again in module 4. But in the meantime, this view also has the fields pane on the right side of your screen, and just like in the other views, you can expand or collapse the tables that are showing in the fields pane. Another thing is there is a search box at the top of the fields Pane, and if you have a lot of tables, it's very handy so you can just search for the field that you're looking for. You also have an additional pane that you haven't seen before in this View; this is the properties Pane, and again we'll do a deeper dive into this in a later module and explain all of the choices that you have on the properties pane here.

This view also has page tabs down at the bottom. You'll notice that there's a page tab; there's only one initially, and it's called all tables, and it will put every table in your data onto this one tab in the form of a card. When you're working with data sets that have several groups of related tables, you can create more pages in here and put the grouped fields on a separate page so it's not as overwhelming the deal in this view as it could be with a lot of tables. So you'll get more information about model view when we get to module 4 in this course.

One last thing, and it's just terminology here: the Excel workbook called sample Superstore is our data source. Once you bring the data into Power BI Desktop, it is known as a data set. So we're looking at the sample Superstore data set in Power BI Desktop. What we want to do next is bring in data from an access database. We don't want to mix the data together with the sample Superstore data, so we're going to start a new instance of Power BI Desktop and then bring in data from an access database. The access database is in the video description; it is called Northwind.

So what I'm going to do to start a new instance of Power BI Desktop? What I want to do is I want to go up to the top left corner, and I want to click on the file tab of the ribbon. When I'm on the file tab, you have many options, but that's one way of starting a new instance of Power BI Desktop. Sample Superstore will also remain open; the new instance opens in its own separate window. Go ahead and click on the file tab of the ribbon and click on new. So Power BI will relaunch in its own separate window; it will bring up an Untitled Power BI Desktop, and it's like a separate file, similar to having like a separate file in Word or Excel. Because we're already signed in before we brought in the data from sample Superstore, our splash screen now looks a little bit different than it did when we first launched Power BI Desktop.

Let's take a few moments to review the splash screen. I'll start on the left side. We can start the process of bringing in data from the access database by using get data on the left. We also have recent sources; our sample superstore.pbix Power BI Desktop file is listed there. If we're going to access that file a lot, it has a push pin; we can pin it to the list. If you're not going to want to access that file often, to clear it from the list, you can right-click on it, and you can choose remove from list. If you had a lot of unpinned files listed here, you could remove all of them at the same time. You also have the ability to open other reports; your desktop files are known as report files, so the only one we have so far is sample Superstore; we don't have any others to open at this moment.

In the center of the splash screen, you have the ability to look at many videos to get information about Power BI. The one thing I will say is that they really have help everywhere that you could possibly look for it in Power BI, both in the desktop application and the Power BI service online. So this one has a lot of videos; how to get started building reports. They have a link down in the lower right corner saying view all videos, and if they're more to show, it takes you online, and you have a whole host of videos that you can look at from Microsoft online. I'm going to disclose the internet for that. If you don't want the splash screen to show up when you launch Power BI, you can uncheck the box there. I find it useful; I can do a lot of things right from the splash screen.

On the right side of the splash screen, in the upper right-hand corner, you have the X where you can close the splash screen, and then it shows your username. There are more help topics on the right side. Power BI is constantly updating, so you might want to take some time and review what's new in Power BI; constantly updating. You have forums where you can ask questions and get answers and also interact with other users in the Power BI community. You have access to the Power BI blog that keeps you up to date on the latest news, things that are going on, and a host of tutorials. I go to the blog and the forums, and I'm a member of the Power BI Community; ninety percent of the questions that may come up for me about Power BI, I find the answers in those vehicles.

Now it's time for us to bring in data from the access database entitled Northwind that is in the video description. We're going to click on the get data button on the left side of the splash screen, and it's going to open up a window for us, and in this window, at this time, as of the recording of this video, there are over 80 different data sources that you can bring into Power BI. So on the left side of the get data screen, it defaults to all, and if you want to, you can take a look; you can scroll down the left side and look at all the different data sources. Now one thing I will say, some of the ones on the right side—excuse me, it's not the left side, it's the right side—all of the ones on the right side that have the word beta after them—I know I passed one—so here's one, app figures, and in parentheses afterwards it has the word beta—that means that Power BI is testing out this particular data source, and it may or may not work out. Sometimes you'll come in here, and things are no longer on the list, and new things are on the list, so it's always looking to allow you the ability to bring in data from more data sources than it currently does.

So what we're looking for on the left side, I'm going to click on other, and I'm going to scroll back up to the top, and you can see this is where you would find web and all kinds of stuff if you want to use categories on the left. I'm going to now click on database; an access database on my screen is the second one from the top. On the right-hand side, I can double-click it or click it once and then at the bottom click the connect button. It should bring up your working directory, and there's only one database file in that directory called Northwind. I'm going to go ahead and double-click it. So it's going to go through the process of connecting to the database, and it will ultimately open a navigator window similar to the one that we saw when we brought in the Excel workbook sample Superstore. There are differences, however; databases usually have four different objects in them. The tables are the only objects that hold the data, but there could also be queries, forms, and reports. There can be macros and modules as database objects. So what it brings in here is it will bring in any queries, and notice the icon for query; it looks kind of like a double table icon. So when you—you'll get used to the different icons that you'll see in here—but that icon represents a query in the database. Beneath all the queries, you will see tables, and that icon looks pretty much like a table. So sometimes a database will have temporary tables in them; we don't want to bring in the queries; we don't want to bring in the temporary tables. I'm going to scroll down, and underneath the temporary tables, there are seven other tables. I'd like you to click the check mark in front of customers, and you'll see that the preview is evaluating, and you'll be able to see the data that's in that customer's table in the Northwind database. We're going to check the box in front of employees, order details; we're going to skip orders for a moment; check the boxes in front of products, shippers, and suppliers.

What's different about this Navigator window from the one we saw with Excel is the button in the lower left corner that says select related tables because databases already have table relationships in them, and because Power BI can automatically detect those relationships, if you forget to select a related table, Power BI has the capacity to do that for you. So we're going to get a demo of this right now—actually, not a demo; you're going to do it with me—so that select related tables button; we didn't select the orders table on purpose. Go ahead and click select related tables, and it'll take a moment because it's looking through the relationships to see what other tables may be related to the ones that we've selected, and when it gets done, it will automatically select the orders table for us. So the Northwind database file that we just brought in, that we're bringing into Power BI, already has table relationships in it, and Power BI is able to detect those relationships. My preview is still evaluating, but that's fine; we're going to go down, and we're going to use the load button again to bring the data from the database into our Power BI Desktop. So you'll see that it's evaluating all of the tables that we selected; it's creating a connection in the model; loading data to the model; and you have the yellow band at the top of the screen that was telling you that you had unsaved changes, but once it's done with the load process, that yellow band disappears. If you take a look to the right, you'll see the seven tables that we imported in the fields pane. Let's go ahead and save this file, so we're going to go back up to the left to the quick access toolbar; the first icon is save, and we're going to call it Northwind. It's still in your working directory, and you can see the sample Superstore Power BI Desktop icon, so get used to that icon in front of sample Superstore; that's indicative of a Power BI Desktop file. So I typed in Northwind as my file name, and I'm going to click save. So your title bar at the top of your screen will update with the name of the file.

Because the database that we just brought into Power BI has relationships in it, we're going to start by looking at model view. So on the left side, click your last view button, and we'll go into model view. So you'll see the relationships between tables here. Power BI has the ability to Auto detect relationships, and it is a setting that you can change. Again, we will do a deep dive into relationships once we get to module four, but I just wanted you to see mostly that Power BI has the ability to detect them when you import a database file or any other file where table relationships have been created. I'm going to show you where to find a setting that allows Power BI to do this. We're going to access the file tab on the ribbon, and when you get there, you're going to go down to options and settings. You will learn more detail about options and settings throughout this course; I'm specifically bringing us here now to talk about how to make sure that the ability to Auto detect relationships is on or off. When you click on options and settings, you get an options link and a data source settings link; we're going to click on the options gear. So what I will say about options in Power BI Desktop is they come in two varieties; there are Global options, which are applicable to every file that you would have open in Power BI Desktop, and some of the global options are only in that category. You also have another option down here, another category, and its current file. So you have Global options and current file options, which only apply to the file that you're working in in that moment. This—the option we're looking for is under current file, so we're going to go to data load under current file, and on the right side of your screen, you'll see a relationships heading. Under that heading, you have three different check boxes, two of which are always selected by default. The one that controls its ability to bring in relationships upon data load is the first checkbox. The third one, which is also a default, is auto detect new relationships after data is loaded, and you'll see an example of when that happens um later in this class. I normally am in the habit of, for every file that I'm working on, I also check the middle one to update or delete relationships when refreshing data, and you'll learn more about refreshing data later in the course, but for now, my advice is to just have all three of those checked for every file that you're going to be working for—working on—if that is your intent to have Power BI help with detecting relationships. I'm going to go ahead and click the OK button to get out of options, and once we're out of options, we're going to go ahead and save this Northwind file again by using the save icon on the quick access toolbar, and we're going to close the file. A couple of ways of doing it; I just usually close the whole window, so I'm going to travel all the way to the other side of the screen, pass my name, and I'm going to click the x button. If you attempt to close a file and you haven't saved it, it will prompt you to save the changes, just like any other application. Because we did file new, sample Superstore is still open; the file for Northwind opened in its own separate window.

Our next task is going to be bringing in data from the internet. We're going to go ahead and close the sample Superstore file. I'm going to use the X in the upper right-hand corner of the window to close it. If prompted, save the changes. There's a Word document that you grabbed from the video description called websites and more information, and I'm going to bring that document up now. Before bringing the data into Power BI, let's follow the link and go to the website. So I'm going to hover my mouse over the link, hold down my control key, click on the link, and it will open the website for me. So this is just off of Wikipedia; most websites are designed with tables on them, and a website designed this way, you can typically bring in the data from the website. Since we're here, there's no need to go back to the Word document to get the URL. I'm going to click at the top of the screen in the address bar and select the URL, and I'm going to do control C on my keyboard to copy it. Now I'm going to minimize or even close; I'm going to go ahead and close that website, and I'm going to get the Word document off of my screen.

Since we closed all of the files in Power BI Desktop, we're going to have to launch it again. This time I'm going to launch it because I have it as a button on my taskbar; I'm going to click on that button and launch the application so that we can import the data from the Wikipedia site into Power BI. We can do it from the splash screen, or we can use the ribbon. This time I'm going to go to the upper right-hand corner of the splash screen and close it. We're going to go back to the get data button on the ribbon, just like we did for the access database, and we're going to use it to access web content. Go ahead and click the button, and when the get data dialog box opens, on the left side, you're going to click the last category, other. On the right side, under other, web is the First Choice; go ahead and double-click that. It'll bring up the from web dialog box. Now you want to switch over to the Word document that has website and additional information in it, and I'm going to bring that up on my screen right now. In this file, we have a link for median household income by state 2021, and let's take a look at the information on the internet. So I'm going to just hold down my control key and click on the hyperlink; it's going to open a web page, and you see it has some sort of a visualization on it, but most web pages are built using tables. So if we scroll down, we'll eventually run into a table, and that's probably what we're going to want to import into Power BI Desktop. Since we're here, we don't have to go back to the Word document to get the URL; we're going to just click up at the top of the screen in the address bar and select the URL, press Ctrl C to copy it, and then you're going to switch back over to Power BI Desktop. We're going to paste the URL into the box by using Ctrl V, and then we're going to click the OK button on the right. So it flashes on the screen that it's establishing a connection, and then you may see a flash of a box that says it is connecting, and when that process is over, the Navigator window will open. This Navigator is different than the one we saw for the Excel workbook and the one we saw for the access database. After clicking OK when you paste it in the URL, you may be directed to an access web content screen that's asking you for Authentication. Some websites, the first time you try to bring in data from their site into Power BI, it wants to know the authentication level. So if you get this box, you're going to leave it on Anonymous, which means there's no credentials required to view the content on that site. When the Navigator window displays, you'll see the tables that it was able to pull from the site, and you'll also see in the preview pane that you have another tab at the top; this enables you to see the data as it appears on the web. So right next to your table view tab, which we're used to seeing, you have another tab that says web view. As you click on the tables in here, you can either see the data in table view or you can see what it looks like on the internet. So we're going to start selecting tables, and I want to preview them in Table View. Foreign just to view the data, I do not have to check the check box in front of the table; I can simply click on the table and see the data. So table one looks like it has part of the data from this website. When I click on table 2, it has more parts of the data, and the same goes for table three. So I'm going to go back and select each table, and if you'd like, you can take a look at webview to see how the data displays on that website. So we're scrolling down; we were just on the site, and it looks exactly as it does on the internet. So it looks like it's bringing in the information from the table, median household income by state 2021. I'm going to go ahead and do the load button in the lower right-hand corner. You'll see your load dialog box pop up letting you know it's evaluating the information; it pretty much goes through the same process regardless of the data source in most cases. While it's evaluating in the load box, you'll notice the yellow band at the top of the screen, as you've seen before, that will disappear once the data is loaded into Power BI Desktop. You will see the three tables on the right side of your screen in the fields pane. Let's go ahead and save this file and call it median household income. This time I'm going to go to the file tab of the ribbon to perform the save; still in my working directory, so I'm going to just name it, and it defaults again to the Power BI Desktop file. You can press enter or click on Save. Let's expand the tables that we brought in over on the right in your Fields pane, and you'll notice some inconsistencies with the data. Earlier we talked about how important column headings are and that they need to be descriptive of the data that's contained in the columns; well, table one is looking good.

It has great column headings. Table two and table three are both going to need to be fixed. Let's take a look at the data in data view. So again, that's the second view button on your Ribbon, and we're going to go over there and click it now. So we saw this in the preview window that these three tables are really one table on the internet, but it came in as three separate tables. Later on in the course, you'll learn how to merge those into one table, so you just have one table with all of the data as it is on the internet.

So far in this getting data from multiple sources lesson, we have brought in data from an Excel workbook, an Access database, and a website. The next lesson that we're going to be getting into is optimizing performance, and you'll see another way during that lesson of how to get data into Power BI Desktop. For right now, why don't we go ahead and click on File, New, so that we launch a new instance of Power BI in its own window? We'll come back around to the median household income file later and we'll do some data transformation in it so that we can clean up these messy column headings. And the other thing that we'll probably want to do is rename the tables. Table 1, 2, and 3 are not very descriptive table names.

We're going to go ahead and close the splash screen, just so we're in the desktop interface. Before we bring in data from another file, I'd like to open a file and review its contents. So in the video description, there is a customer data Excel file. I'd like you to go ahead and open the file. When you look through the sheet tabs in this file, you'll notice that each sheet has a pivot table or a pivot chart on it, and the underlying data doesn't appear to be on a sheet in this file. This is an example of a file using a Power Pivot data model to capture all the underlying data. So the cool thing about this is, even if you don't have the Power Pivot add-in, Power BI will be able to read that data. And the decision that you're going to need to make in order to optimize performance is basically this one: Do you want to bring in the whole data set into Power BI, or do you just want to bring in the underlying data from the pivot tables and pivot chart? That's the question.

So far, we've been connecting to data sources through Get Data or Excel workbook. If you connect to a data source, it's only going to bring in the data from an Excel file that resides in the Excel application. If you want to bring in the other data, you're going to have to import the file into Power BI. So I have the Power Pivot add-in, and even if you don't, it's still fine if you inherit a file with a Power Pivot data model in it and you don't have Power Pivot; Power BI can access that data model. So I'm going to go up to the ribbon and click on Power Pivot, and then the first button is Manage, which takes me into the data model. Again, if you don't have access to the Power Pivot add-in, you'll be fine when we bring this into Power BI. What you're seeing right now is the Power Pivot data model; it is the underlying data that's providing subsets of itself for the pivot tables and the Excel application. You'll notice at the bottom of the screen there are different sheet tabs, just like in in Excel. So the data has been broken up into categories onto different tables, and the relationships have been created between those tables as well. So we're going to just take a brief look at the data. This is the customer's data. We have some calculations on the sheet at the bottom in a calculations area. You have an order details sheet where we have some more calculations at the bottom, so on and so forth. Those are the sheets that contain the underlying data that's feeding the pivot tables in the Excel application. If I want to look at the relationships in here, up on the ribbon I can go to Diagram View, and this is very similar to Model View in Power BI, which we'll be covering in Module 4. So relationships have already been created in the data model for this Excel file. Now I'm going to go ahead and get out of Power Pivot. I'm going to save my file, switch back over to Excel, and we can all close the Excel file now. If you've gone into Power Pivot, make sure that closes as well.

Now that we're back in our Untitled Power BI Desktop file, we're going to bring the data from the customer data Excel file into Power BI, but we're going to use two different techniques, and you'll see the difference as this plays out. The first technique we're going to use is this: Simply on the Home tab, we're going to click Excel workbook, and we're going to double-click customer data. It'll do its connections, and then you'll have your Navigator window. What I'd like you to notice here is the Navigator window; these look like sheet tabs. So these are the sheets from within the Excel file that have the pivot tables and/or pivot charts on them. If I click on Customers by Country, it's only showing me the underlying data that's populating that particular pivot table. What we're going to do is we're going to select all of the check boxes, and we'll bring all of these tables in or sheets in, and we'll load. So it's not showing the data model at all. By using Get Data or Excel workbook, it's just showing the stuff that's in the Excel application. We can go over to the Data View and look at the data in each of these tables, and you'll see it's a small subset of the greater data set that's in Power Pivot. We're not going to save this file, so I'm going to just do File, New, and it's going to launch another instance of Power BI Desktop. Go ahead and close the splash screen when it opens. Whenever you use Get Data, you're connecting to the data. In order for us to bring in the Power Pivot data model into Power BI, we're going to have to import the data. We can do that from the File tab of the ribbon. So click on File, and you'll see the Import option. Notice the four different types of imports. If we wanted to bring in a Power BI template or a Power BI visual from another file or a Power BI visual from AppSource, we would use the Import feature. The last choice is what we're going to ultimately use: Power Query, Power Pivot, and Power View—three add-ins to Excel. Even if you don't have those add-ins, it doesn't matter if you inherit an Excel file with Power Query, Power Pivot data models, or Power View reports; you can still bring that underlying data into Power BI because it's able to recognize it. So this is a Power Pivot file, an Excel Power Pivot file. We're going to select that fourth option, and it's going to take you to your directory, and you're going to double-click customer data. The screen that shows up can be kind of alarming when you first read it: Import Excel workbook contents. We don't work directly with Excel workbooks, but we know how to extract the useful content so you can work with it in Power BI Desktop. That's great for us. We're going to go ahead and click Start, and it will go through the import process. At some point, you'll get the Migration completed dialog, and if you do, the scroll bar on the right you'll see that it brought in seven items, which are queries—known as queries—it brought in seven data model tables, it brought in some KPIs and measures, which are calculations—22 of those—and there were no Power View sheets in the file, so zero items there. We're going to go ahead and click on Close. So when you look on the right side of your screen in your Fields pane, these table names are mimicking the sheet tabs in the Power Pivot data model, not the Excel workbook. If you go to Data View, you can see the underlying data in all of the tables—a much broader data set than what we'll use to build those pivot tables and pivot charts. Knowing when to import data versus knowing when to connect to data will optimize your performance in Power BI. If we didn't import the data model data, we would have to take the data tables that we did import from Excel, which is a subset of the overall data set, and we would have to kind of merge them together to get a complete picture of the data. This way, we don't have to do that. We'll go ahead and save this file and name it customer data.

Before we get into the last lesson of this module, let's go ahead and do some light housekeeping and close any Power BI Desktop files that you have open. Our last and final lesson in this module is resolving data errors. There are two errors that I'd like to show you. We're going to force one to happen to get started. So I'm just on my desktop, and I have my Windows Explorer set into my working directory where I have the class files for this module. What I'm going to have us do is you'll see your sample Superstore—both the Power BI and the Excel spreadsheet—in the same directory. What I'm going to do is I'm going to grab the sample Superstore spreadsheet and I'm going to just move it out to my desktop, so it's no longer in the same directory. While the directory is still open, I'm going to double-click my sample Superstore Power BI file to launch the application and open that file. So right now, when we open the file, we're not seeing any errors. We still have our tables in the Fields pane. You can go to Data View and still see the data in the table. So when you connect to data, it connects to the location of the data as well. So you can be working in the file, and it appears that everything is fine until you do one of two things. I'm going to go back to the Home tab here, and in the Queries group, I'm going to click on the Refresh button. So all of a sudden, I get errors, and it's telling me that it can't find the data source file—the actual Excel file. So when we move that file out of the working directory, Power BI does not update a change like that. So right now, we're kind of stuck. We wouldn't be able to get much done on this file without resolving that error. So the way to resolve the error is like this: We're going to close that refresh box, and then we're going to go to the File tab and we're going to click on Options and settings. So we were in Options earlier when we looked at the check boxes that enable Power BI to be able to load relationships into itself when bringing them in from a database file or, in our case, a Power Pivot file as well, but we're going to click on Data source settings this time. So when the Data source setting dialog box opens, you'll notice that it's pointing to the path that it was originally in on your computer before we moved it out into the desktop. That stays with the Power BI file. So in this situation, we have to tell it where the actual source data file resides. The sample Superstore Excel file doesn't reside in that path anymore. What you're going to do this time is in the lower left-hand corner, you're going to select the Change source button. And when we click on that button, it opens an Excel workbook, and this is particular to Excel right at this point. We're only dealing with that Excel file, and notice that it has defaulting to Excel workbook in Open file, as you have other file types down there. So depending on what kind of file it is, it normally will select the right type, but always verify that it's the right one selected. So where it has that path, I'm going to click on Browse, and I'm going to browse to where I put my sample Superstore file, which is just on my desktop now, and I'm going to double-click it. So once it has the path, it will be able to continue. Now go ahead and click OK, and it updates on the Data source settings dialog. You can send a Power BI Desktop file that you create to someone else; you can email it; you can get it to them. But if you do that and you're the one that created the file, also send them the source data file, because otherwise they'll be limited in what they can do in Power BI. It will let them load the data, but as soon as they refresh or do a few other things, you'll start seeing the errors. We're going to close Data source settings, and it tells me there are pending changes in my queries that haven't been applied—that yellow band at the top. In this case, this is different from loading data; we want to tell it to apply the changes that we just made. So I'm going to click on the Apply changes button, and it's kind of actually reloading the data into the file. So when you open a Power BI Desktop file, it loads the last data set that it had in it, but if the data source has been changed to a different location and you point to it, it's going to have to reload the data. If you'd already progressed to the point where you made visualizations and Report View and things of that nature, they would all update with the reloaded data. So let's see if we got rid of this error. On the Home tab, we're going to click the Refresh button again, and we shouldn't get the errors this time because it knows the path to the data source at this point. If we move the Excel file back into our working directory, we would have to update our data source again. So we're going to do that right now. I've minimized my Power BI Desktop, and I'm going to grab my sample Superstore Excel file and drag it back into my class file working directory folder, and then I'm going to bring my Power BI Desktop back up. I am going to click the Refresh button on the Home tab at a ribbon, and we get the same error. Some other errors might say Load; this one is saying Refresh because we clicked the Refresh button. I'm going to close that error. I'm going to go to the File tab and back to Options and settings, Data source settings, and at the bottom I'm going to click the Change source button and use the Browse button to get back to my class files working directory and double-click sample Superstore. I'm going to OK and close, and remember you're going to have to apply the changes in the yellow band at the top of your screen, so it reloads the data in from the path that you just pointed it to. Now when I click on Refresh, I won't get the error, and we will definitely be doing a deeper dive into Refresh in just a little bit.

Another type of load error that you may get is when you import a file that has Power View sheets. Power View is an Excel add-in, and it allows you to create visualizations in an Excel file. This is very similar to when we imported the Power Pivot Excel file. Even if you don't have Power Pivot or Power View, Power BI is able to read that data. So we do have a Power View file called It Spending Analysis in the video description, and it it's an Excel file with the Power View sheets in it. I'm going to take a moment to open that file. I don't have Power View, so you'll see what it looks like when you open it and you don't have Power View.

I pulled this file from the Microsoft site; they have several sample files out there that you can grab and use in Power BI. Some of them—this one may be—when you see the visualizations in Power BI, you might get some ideas for those same types of things on your data. So I don't have Power View, like I mentioned, and this file contains multiple sheets—just three of them—that have the framework for the Power View report that I can't see without having to add in. I'm going to go ahead and close this file, and then we'll switch back over to Power BI. In Power BI, I want to go to File, New. Don't want to bring that data from the Power View reports file into the sample Superstore data set. So we're launching a new instance; we've done this before. I'm going to go ahead and close the splash screen because we can't use Get Data to import this data, and we're going to go to File, Import. We're going to select the fourth choice like we did before: Power Query, Power Pivot, Power View, and you're going to double-click on that It Spend Analysis sample file. We get the same Import Excel workbook contents dialog, and we're going to click Start. So the migration is completed; there are nine queries that were imported, nine data model tables, and 15 KPIs and measures as well as three Power View sheets—the ones that we saw in the Excel interface. I'm going to go ahead and close. This is the error that I'm referencing. So sometimes when you bring in something from Power View, Power BI may not support that visualization anymore, as shown here. It either has a visualization in Power View that is not yet supported in Power BI or is no longer supported in Power BI. So we haven't gotten to the chapter where you're going to be creating reports and putting visualizations on the report pages, but this is another pretty common error. So the fix—forward, even though you don't know yet through watching these videos how to create these—you can learn how to get rid of them before you learn how to create them. So I'm going to click on the framework of where the visualization would be, where the error message is, and I'm going to simply press Delete on my keyboard. Once you know how to build visualizations, you can replace it with something that is supported within Power BI.

The last thing we're going to go over in this module are the implications of where your Excel source data files are stored and their performance in Power BI. If your Excel files are stored locally on your computer versus in the cloud via OneDrive or SharePoint, Power BI will treat those as different source files when it comes to refreshing the source data. I've made a copy of the sample Superstore desktop file and renamed it sample Superstore local, just to make a distinction between my locally stored Excel file and the one that we'll get to put into Power BI that's stored on OneDrive. If you have access to a OneDrive for Business account, go ahead and upload the sample Superstore Excel file that we used earlier. Right now, I'd like to point out a few things so that you can see the impact when we refresh the data from a local Excel file. The first thing I'd like to point out, since we're in Data View, is I'd like you to take a look at the top two sales figures in the Orders table. So the first one is 8617, and the second is 1527. I'd also like to point out that both of these orders are from a city in Utah named Kearns. Now in this file, I've already set up one small visualization, and I'm going to show you what it looks like by going to Report View. You'll learn how to create report visualizations when we get to Module 7, but right now, as I hover my mouse over the bar on the report, it shows me the sales are two thousand four hundred and twenty-eight dollars and 25 cents for the city of Kearns. I've opened a sample Superstore Excel file that's stored locally on my computer, and I'm going to change the values of those two cells that we saw in Power BI Desktop. I'm going to change the 8618 sale to 386.17, and I'm going to change the 1527 sale to 215.27, and then I'm going to save the file.

I've switched back over to the Power BI Desktop file, and as you'll notice on my screen, even though I made the change in the Excel source file, it hasn't updated those sales values as of yet. The reason why is I have to tell it to. The way I tell it to do it is by going to the Home tab of the ribbon, and almost in the center of the Home tab, there's a Refresh button. So notice when I hover over that button, it says Get the latest data by refreshing all visuals in this report. I'm going to click the button to refresh. You'll notice that it has the pop-up on the window, and when it's done and I go back over and I look at those sales figures, they have indeed updated to what I put in the locally stored Excel file. Now that is in the Power BI Desktop. If you make a change to a locally stored file—Excel file in particular—and you refresh in Power BI Desktop, it will update the information, but something different occurs in the power…

Bi online service, and I'm going to show you that now. I've already published that sample Superstore local file to the Power BI service, and it published both the report and the data set. So, when I come into the service and I look at the report, it looks exactly as it does in the desktop, with the same sales value of 2,428.25. When I hover over the data set, I see a refresh button. Refresh now. I'm going to click that button, and then I'm going to go back to the report. The report is the same; same sales value. I can also refresh at the report level by clicking this refresh button over here. Again, nothing changes. This is because the Excel file is stored locally, and the online service cannot refresh from a locally stored Excel file at this point. If I wanted to get the updated data set into the cloud Power BI service, I would have to republish it from the desktop.

Now we're going to bring in an Excel file that's stored in a OneDrive for Business account. It's the same Excel file as the sample Superstore file we've worked with previously; I've just renamed it dashod to represent OneDrive. The tricky wicket with bringing in something from SharePoint or OneDrive is you can't just copy the link that's up here; you actually have to open the file in the Excel desktop application. So I'm going to go to the more actions button, hover over open, and choose open in app. This is the only way to get a link that you can use to bring it into Power BI Desktop, just like you would bring in a web file. Once I have it opened, I can then go to the File tab and click on Info, and I'll see the copy path button. That's the way you have to get the path to the file, where its location is in OneDrive. I'm going to click on copy path, and then I'm going to just close this file. I don't need to be on OneDrive in this moment, so I'm going to switch back over to Power BI Desktop because I want to bring this into a new instance. We're going to go back to File, New, and launch a new instance of Power BI Desktop so we can just keep these separate: local versus OneDrive. I'm going to access Get Data from the splash screen, and when the Get Data dialog box opens on the left side, I'm going to click on Other and then double-click Web at the top of the list. So the path is on our clipboard; I'm going to do Control Z to paste it in. The second tricky wicket you're going to run into here is at the end of the path you'll have a question mark web equals one. You need to delete that part of the path, or this will not work. So I've deleted it, and I'm going to click OK. The Navigator window will open; it's going to be the same three tables: Orders, Returns, and Users that we used before. So I am going to put a check mark in front of each one and click the Load button. The normal evaluation process will take place; you'll have your yellow band at the top. Okay, it's loading the data to the model, and if I look over to the right in my Fields pane, I have the same three tables. I'm going to switch to Data View, and just as a navigational point, I'm going to expand the Orders table and click on the Sales field, and you'll notice that those top two sales values are the same as in the original data before we made any change in the OneDrive file. We hadn't made any changes to the data, so you have your 8617 and 1527 in terms of sales. We're going to go to Report View, and I'm going to show you how to build the report, the same one that I had in the locally stored desktop file. So what I'm going to have you do is over to your right in the Visualizations pane, you're going to click on that very first visualization. Thank you. It is a stacked bar chart, and then over to the right in your Fields list, you're going to expand your Orders table, and I'm going to just drag the City's field over to the framework of the visualization. And again, you'll go into deeper detail on how to design reports in Module 7. And then I'm going to go and grab the Sales field and drag it into the framework of the visualization as well. The last thing I have to do is filter it for just that one city, which was Cairns. So in my Filters pane, where it says City is all, I'm going to do the drop-down arrow, and I'm going to just in the search box I'm going to type Cairns, K-E-A-R-N-S, and I'll just check the box when it comes up. So there are eight sales that happened in the city of Cairns, and that's how I developed the visualization you saw in the other file. If you point to the bar, it will tell you the sales value for that city is 2,428.25, just like in our regular file. I'm gonna save this file, and I'm going to call it Sample Superstore Dash OD for OneDrive.

Now that we've saved our file, we're going to publish it to the Power BI service. The last icon on the Home tab at a ribbon is Publish. Go ahead and click it. It will ask you sometimes if you want to save your changes, even if you've saved; sometimes that prompt will come up, not always. And then you'll get a box that says Publish to Power BI. Select the destination. I have multiple workspaces; the one that we all have in common is My Workspace, and I'm going to go ahead and put this in my Power BI video workspace, but feel free to put it in your workspace. We'll do a deeper dive into publishing and what workspaces are and how to use them when we get to Module 8. So for right now, select your workspace, and it will let you know that it's publishing, and it lets you know when it has successfully published. We could get to the Power BI service from this box, but for right now we're going to just click Got it. Now at this point, we want to make a change in the Excel file that's stored on OneDrive. So I'm going to go back over to OneDrive, and I'll need to open the file in the Excel desktop app again. So I'm going to hover over open and open in app. We're going to make the same change in this file, the same two changes that we made in the other file. So I'm in cell W330, and I'm going to change that to 3,8617. And I'm gonna go to cell W332 and change that to 21527. And then I'm going to save. Now at this point, it is uploading, and if you attempt to do the X to close the file, you'll get a message on your screen that it's still uploading. I'm going to wait till it's totally saved and then proceed. So at this point, I'm going to go back over to Power BI Desktop.

Once I'm in the desktop, I can go ahead and refresh this Power BI file by clicking the refresh button on the Home tab of the ribbon. When I refresh, it's going to go through the dialog boxes where it's evaluating; it's looking at all of the data, and when I hover over the bar on the chart, I see the sales value for that city has gone up by five hundred dollars; it's now 2,928.25. If I go to Data View, I will see that those sales values updated as well. And this is the best part: it will also update in the service since the Excel file is stored in the cloud on OneDrive. Another way to get to the service is by going up to your login information in the upper right-hand corner and clicking on Power BI service. Now when you get there, over on the left side, the next to the last button is Workspaces, and I'm going to just navigate to my workspace where I saved and published this data. Once I'm there, I'll see that I have both the OneDrive report and data set and the locally stored report and data set. For the OneDrive data set, I'm going to go ahead and click the Refresh Now button, and then I'm going to click on the link to take me to the report. When I get to the report, it's now updated to 2,928.25. So if a file is locally stored, it will update in Power BI Desktop when you refresh it, but it will not update in the service, and you would have to republish it to the service.

In conclusion, for Module 2, getting data into Power BI, you brought in data from multiple sources. You learned about the implication of locally stored versus cloud-stored Excel files, how to import from PowerPivot and Power View Excel files versus using Get Data, the difference between a data source and a data set. You learned how to import from an Access database and from a website. We went over a few performance optimization issues, and you'll learn more throughout the rest of the course. And you learned about common load errors and how to resolve them. The next module, Module 3, is about cleaning and transforming your data in Power BI. Everyone, I'm Trish Connor Cato, and I'd like to welcome you to Microsoft Power BI. Now it's time to get started in Module 3, where you'll learn how to clean, transform, and load data into the data model. You'll learn the process of profiling and understanding the condition of your data. You'll learn how to identify anomalies, look at the size and shape of the data, and perform data cleaning and transforming steps to prepare the data for loading into the data model. We'll be using the sample Superstore Power BI Desktop file that we created in the previous module during this module, so feel free to pause the video and get that file open.

The first of the three lessons in this module is data shaping. Shaping data means transforming the data by renaming columns or tables, removing rows, setting the first row as headers, and so on. In this lesson, we'll be performing data transformation steps on the Users table and the Orders table. We're going to start by taking a look at the data that is in the Users table. Remember your View buttons are on the left side of your screen, and the second View button is Data View. I am going to go ahead and click that button to get into the View. When I get in there, I want to go over to the Fields pane, and I want to click on the Users table so I can see the data that's in it. A couple of things to point out: this table contains information about managers and their regions. So the first thing we're going to want to do is rename the table from Users to Managers. We notice that Column 1 and Column 2 are not good column headings for a table; it's not descriptive of the data in the columns. The data that's in the first row, Region and Manager, would make better column headings, and will make that change as well. The final change we're going to make is we decide we want to capture the managers' last names in this table, so we'll be adding a column to the table, and we'll input their last names. Let's rename the Users table. I'm in the Fields pane, and I'm going to right-click on it, choose the Rename options from the shortcut menu, and I'm going to just type Managers and press Enter for it to accept the change. Notice the organizational change in the Fields pane; all of your tables will always be listed there in alphabetical order. We'll have to go into Power Query Editor to promote the first row as column headings. In order to do that, we're going to go up to the Home tab of the ribbon, in the Queries group, we're going to click on Transform data. Power Query Editor opens in its own separate window. Once you're in Power Query Editor, your tables are known as queries, and they show up on the left side of the screen. What we're looking at right now is similar to Data View in the desktop in that we're seeing all of the columns in the Orders table. On the left side, we're going to click on Managers, so that's our table of interest right now. A few other things I want to point out: on the right side of the screen, you have a Query Settings group, and in that group you have Properties. It includes the name of the query, in this case Managers, and underneath your Properties you have what are called Applied Steps. Just by switching to Power Query Editor, it performed three steps: it looked at the source, it did a navigation step, and it changed the type. You'll notice that Power Query Editor has its own ribbon, and we're on the Home tab of the ribbon. What we're looking for in the Transform group is the Use First Row as Headers button. When we click that icon, you'll notice that it promoted Region and Manager as the headings, and it got rid of Column 1 and Column 2. The most efficient way to create a column to hold the manager last names is to duplicate the Manager column. I'm going to right-click on the Manager column heading, and I'm going to choose Duplicate Column from the shortcut menu. Now I have my original Manager column and Manager Copy column. We're going to rename these columns. I'm going to double-click Manager, and I'm going to edit it so it says Manager First, and I'm going to press Enter after I make that edit so it accepts the change. Go ahead and rename Manager Copy to Manager Last. The last transformation step we'll take on the Managers table is to replace the first names with the last names in the Manager Last column. We have to do these individually in Power Query Editor. So I'm going to click the first Manager Last, which is Chris. I'm going to right-click on it, and I'm going to choose Replace Values. It's already in the Replace With box, and Chris's last name is Evans, so I'm going to just type in Evans and click OK. We're going to replace Aaron's last name, and Aaron's last name is going to be Rogers. I'm gonna just type that in Replace With and click OK. Go ahead and change Sam's last name to Smith and William's last name to Jackson. Every time we transform the data, you'll notice on the right side of the screen that the Applied Steps list got longer, starting from when we promoted the headers; it added an entry for that, and everything after that is what we did: duplicated column, renamed columns, replace values, so on and so forth. If you do a step in here and you realize that it was a mistake, you can always go over to the Applied Steps, and when you hover over them you'll see the red X, or if you right-click on an applied step, you can delete that step just by clicking Delete, or you can delete that step and all of the steps afterwards from the shortcut menu. We're comfortable with the changes that we made, so we don't have to do anything with our Applied Steps at this point. Now we're going to perform some transformation steps on the Orders query. So on the left side of the screen, in the Queries pane, I'm going to go ahead and click on Orders. The first thing we want to do is decrease the data set by removing some columns. I'd like to direct your attention to the ribbon; on the ribbon you have a Manage Columns group, and it has two icons: Choose Columns and Remove Columns. I very rarely use Remove Columns; this is why. Right now, the Row ID column is the column that I am in, and if I click the Remove Columns button, it's going to remove that column for me. That may not be a column that I want to remove. Now, granted, if I did that by accident, I could go over to the right, and there would be an Applied Step where I removed a column, and it would let me reverse the effect of that and get that column back. I use Choose Columns; it gives more control. I'm going to go ahead and click the Choose Columns icon, and the columns that we're going to remove, I'm going to uncheck the boxes in front of our Row ID, Order Priority, Product Base Margin, and Quantity Ordered New. You may have to scroll down to see that one. So I'm unchecking the columns that I want removed and keeping the columns checked that I want to keep. I'm going to click OK at the bottom. If you look over at your Applied Steps, you have one now because we removed multiple columns, and it says Removed Other Columns. If we happen to do that accidentally, we could do the X in the front of that Applied Step to get those columns back. The next step we're going to take on the Orders query is to filter the data for just five particular states. I'm going to scroll across using my horizontal scroll bar at the bottom until I see the State or Province field, and similar to Excel, in Power Query Editor you have auto-filter arrows next to each field heading, and you can use those arrows to filter and sort very similar to what you do in Excel. Gonna go ahead and click the auto-filter next to State or Province, and this is a good example of something to be on the lookout for: it only loads a limited amount of data in Power Query Editor. So whenever you go to the auto-filter drop-down, always look at the bottom of the screen, and you may see a warning symbol, and it says List may be incomplete. If that's the case, you would want to load the rest of the list before doing your sort or filter to make sure you get accurate results. So I'm going to click on that Load More button in the bottom right, and the warning disappears, so I know that I'm looking at a list of all of the states that are available in this data set. We are going to uncheck Select All at the top of the list, and we want to filter for Arizona, so I'm going to check Arizona, California, Florida. I'm going to scroll down until I see New Jersey, and I'm going to check New Jersey and New York, and now I'm going to click OK. So now the data is filtered for just those five states. We decide that the last things we're going to do for right now is we're going to rename State or Province column to State, and we're going to sort it in ascending order. So I'm going to just double-click where it says State or Province, and I'm going to edit it so it just says State, and remember to press Enter or click away from that in order to save it. And then I'm going to click on the auto-filter arrow, and right at the top I'm going to click on Sort Ascending. So now I have Arizona first. At this point, all of the transformation steps that we took in Power Query Editor for the Managers and the Orders query, those changes are only in Power Query Editor. If we want to get the changes down to our data model in Power BI Desktop, we're going to use the first button on the Home tab of the ribbon. If you click the upper half of the button, it will close Power Query Editor and apply the changes in the desktop. I'm going to click Close and Apply. Now it switches you back over to the desktop, and you'll notice the yellow band: there were pending changes in your queries, and now it's updated. So we're still looking at the Managers table in Data View, and we'll see the Manager First and the Manager Last columns and the Manager last names also. You'll notice that the column headings are promoted; remember we renamed the table in the desktop. On the right side, I'm going to look at the Orders table, and you'll notice that, well, it's missing some columns, the columns that we decided to get rid of, but you'll notice that your State column is filtered for Arizona, California, and the other three states that we chose. What I'm going to do now is save the Power BI Desktop file.

Our next lesson, Enhancing the Data Model, is where we're going to merge information from the Managers table into the Orders table. Specifically, the two tables have a field in common, and that is the Region field. You remember in our Managers table, the managers are listed by the regions they represent. We decide that we would like the manager data in the same table as the Orders data. In order to do that, we're going to need to go back to Power Query Editor. So on the Home tab of the ribbon, go ahead and click Transform data, and the Power Query Editor window will open. You'll notice on the Home tab of the ribbon, in the Combine group, you have two choices that are available: Merge Queries and Append Queries. You also have subchoices for each of those. When you click on the Merge Queries drop-down, you can see that you can either merge the queries or perform the merge and create a new query. We're going to select Merge Queries as New.

We want to retain our original orders and managers queries and create a new one that gives us all the order information and the manager information in the same query. When we get into the merge box, you'll notice since I was on the orders query, the top one is orders. It has a small sample of the data set, and I'm going to scroll across until I see the region field. I'm going to just click in it, letting it know that that is the common field. Underneath that, I'm going to select the drop down and select the managers table. In the managers table, I'm also going to click in the region field. You'll notice at the very bottom of the screen, once you make those selections, it lets you know that it matches 2428 rows from the first table. It also has above that join kind.

Now, table joins can be a really intense topic, and we're going to spend a few moments talking about them before coming back into this box. Before we perform the merge, let's take a moment to review the different join types that are available in Power BI. This PowerPoint presentation is also in your video description so you can reference it in the future. The default join type is left outer. That means it will return all the records from the first table and only the matching records from the second table. Our first table is the table that we listed first, which was the orders table. A right outer join is where it will return all of the records from the second table and only the matching records from the first. That's the type of join we're going to ultimately use. The other join types are listed on the slide. If you use a full outer join, it will return all the rows from both tables. An inner join would only return matching rows, so on and so forth.

So in our merge dialog box, we want to change the join kind from left outer. I'm going to do the drop down and select right outer. You'll notice that the selection at the bottom, the check mark updated; it matches three or four rows from the second table. We're going to go ahead and click OK. Thank you. And now you'll notice that you have a new query on the left side of your screen, and it gives it a default name of merge one. That is the merge query. We still have our original orders and managers queries intact, and that's because we chose merge as new in a new query. The information from the managers table is going to be in the far right column. So I'm just scrolling across, and the last column is called managers. It doesn't have an auto filter drop-down arrow; it has an expand button in the column heading, and right now the column is populated with the word table. What we want to see in the column is the region for each order. So we're going to click the expand arrow to the right of the column heading, and you'll notice this list; it says expand or aggregate. You'll learn about aggregates in a later module. Even though we already have a region column in the orders table, we want the region manager first and manager last names to come in. So we're going to just click OK in here as they're all checked, and you'll notice if you scroll to the right you now have three additional columns: manager's region, managers first, and managers last.

Take a moment to change the managers.manager first and the managers.manager last column headings to just say manager first and manager last. You'll notice that the first record is for the central region and it's a null record. So we decide we're going to filter that out. I'm going to go to the managers.region auto filter arrow. I'm going to load more at the bottom because the list is incomplete, and I'm going to uncheck Central and click OK. So we're not seeing the null record, and we'll go back to Auto filter funnel now, okay, and we'll sort the regions again. The list may be incomplete, so load more and then sort them in ascending order, so we'll have East at the top of the list. To rename this query, we could right-click on it in the queries Pane and choose rename, or because we're in Power Query Editor, we can rename it under query settings and properties. So I'm going to click on the name merge one there and select it, and I'm going to name it ordersby region and press Enter.

We want to go ahead and close and apply our changes, so we're going to click the first button on the Home tab, and it will take us back to the desktop and apply all the changes that we just made. Go ahead and save your sample Superstore desktop file. Now you'll get to try it yourself. You're going to want to merge the orders and returns tables together into a new query, and you're going to want to show the return status in that merged table. Go ahead and get started on that. You can see my results on my screen and compare them with yours. I filtered out the null values in the returns.status column just to clean up the data a bit more. Once you're done with that, go ahead and close and apply your changes and save Power BI desktop file again. At this point in your Fields pane, you'll notice that you have your orders by region merged query and your returned orders merge query showing over there.

The last lesson in this module is data profiling. Power Query Editor has profiling tools that you can use that will give you an in-depth assessment of the quality of your data. We're going to go back to the transform data button on the Home tab of the ribbon, and when we get into Power Query Editor, go to the View tab on its ribbon, and you have a data preview group on the View tab with several different check boxes. One of the check boxes is always checked, and that's just showing white space. What we're going to do is we're going to review the check boxes and their impact on your data, the information that can be gained from using them. I am going to check the box that says column quality, and I'll notice right underneath my column headings for each column it's telling me whether the column is valid, whether there are errors in the column, or whether there are empty things in the column by percentage. Good insight into the data that's in your columns. The next check box is column distribution. I'm going to leave column quality checked and I'm going to check column distribution, and it expands that area underneath the column headings. It's showing me that I have 11 distinct values and zero unique values in the discount column, for example. Each column is listed with distinct versus unique values, and you have the opportunity there to remove duplicates if necessary. The last check box is column profile that I'm going to cover. That opens the panel at the bottom of the screen underneath your data set, and it's giving you column statistics: the count, errors, whether they're empty, distinct, unique, not a number, so on and so forth, and there's a little graphic that's showing the value distribution in that particular column. Now, the column that I'm in right now is the discount column, the first column in the orders table. So if I wanted to see distribution information for unit price, I would just click in that column, and that's how you can profile your data in Power Query Editor. I'm going to go back to the Home tab of the ribbon, and this time I'm going to click the close and apply drop down, the first drop down, and I'm going to just close Power Query Editor without applying the changes, and I will save my Power BI desktop file. Notice how frequently I saved the desktop file; that's where your data model is stored.

To recap module 3, we applied data shape Transformations by removing columns, promoting column headers; we duplicated a column, renamed columns, and replaced the data in a duplicated column. We also learned how to filter and sort columns. We enhanced the structure of the data by merging queries into a new query so we could combine data from multiple tables, and we ended by profiling and examining our data through the Power BI Power Query Editor profiling group. Module 4 teaches the fundamental concepts of designing and developing a data model for proper performance and scalability. We'll also understand and tackle many of the common data modeling issues, including relationships, security, and performance. We'll continue using the sample Superstore Power BI desktop file that we created in previous modules. So what is data modeling? It can be defined as making the data you use in Power BI as accurate and intentional as possible. It is a series of processes as you'll see play out in this module. This module has several lessons. We'll begin with working with tables, move on to dimensions and hierarchies, continue with creating model relationships and reviewing the model interface, and end with enforcing row-level security, also known as RLS. Lesson one is working with tables. Working with tables can mean many things. For starters, we're going to work with formatting some of the columns in the orders table. The first thing we're going to do is take a look at the data that's in the orders table in Data View, which is the second view button on the left side of your screen. When you go into Data View, you'll notice that it's showing the data for the first table in the fields pane to the right. Click on the orders table in your Fields Pane, and now you're seeing the data in that table. Some of the formatting changes that we're going to want to make here is we're going to want the discount column to be formatted as a percentage. We're going to want unit price and shipping cost columns to be formatted as currency, just to name a few of the changes we're going to want to make. We can do that right from the desktop; we don't have to do this in Power Query Editor. And the way that you do it from the desktop is go ahead and click anywhere in the discount column to select it, and you'll notice you'll get a new tab on the ribbon called column tools. Everything that we're going to want to do we're going to do from the column tools ribbon tab. To get started, you've already selected anything in the discount column, so that is the active column, and on the column tools tab in the formatting group, you're going to go ahead and click the percentage icon, very similar to Excel. So now the discounts are formatted as a percentage, and if we decide that we don't want any decimal places, right underneath percentage, we're going to change that to a zero, and you can see the impact on the data. Now I'm going to click in the unit price column, and in the same formatting group, I'm going to click on the dollar sign icon, which is the currency format. Go ahead and do the same thing for the shipping cost column; format it in currency. I'm going to use the fields pane on the right to navigate to the next field that I want to format, so I'm going to click on order date, and it will take me to that column and have that column selected. We decide we don't want the day of the week as part of our date format, so on the column tools tab, same formatting group, this time we're going to go to the format drop down, and you're going to click on the format that just says March 14, 2001. And the order date is formatted that way. Navigate to the ship date field and apply the same format. The last thing we're going to do in this lesson is categorize some location fields for mapping purposes. So we have region, state, city, and postal code fields in this data set. Later on, when you learn how to make visualizations on report Pages, there are mapping visualizations that will give more data about a location if those location columns are categorized. So we're going to just select anything in the region column, and on the same column tools tab in the Properties Group, you'll see the data category is uncategorized. Select the drop-down arrow, and you'll see the different things that you can categorize. So we're using location fields; we don't have a street address, we don't have a place in our data, but we do have a city, we have a region, we have a state, and we have a postal code. We'll use those fields in just a moment, but if you had latitude and longitude fields, you'd be able to categorize them. If you want a web URL to be within your data, you could use a web URL and categorize it. If you have a URL for an image and you want that image to show, you can use the image URL, and it also allows for barcodes. We're in the region field; let's go ahead and navigate to the State field and go back to the uncategorized drop down. We're going to categorize the state as state or province. Go ahead and categorize the city and postal code fields appropriately because postal code is a numeric field; it only gave you the option of postal code or barcode. The only effect you'll see of your categorization is in the fields list. If you notice, city has a globe icon in front of it, as does postal code and state. Again, when you learn how to create report visualizations, you'll see how the categorizations help on maps. Lesson two is dimensions and hierarchies, a subtopic of working with tables. To get started, we're going to talk about breaking down your tables. It's an optimizing performance tip, and when applicable, it's very useful. One large table is not the answer for any sectors data model. So what does that mean? You break a large table down into two or multiple tables. Let's talk about fact tables first. Fact tables keep numeric data that might be aggregated in reporting visualizations, for example, sales and profits. Dimension tables keep descriptive information that can slice and dice the data in the fact table. They require a key field, which you'll learn about very shortly. So, for example, data that should be in dimension tables would be customer information and product information. The golden rule for usable data is that you should not have fact and descriptive fields in the same table. By breaking down your tables more efficiently, you'll optimize the performance of your data set. Let's go ahead and save our file. We're going to start breaking down the orders table. If you'll notice, the orders table has customer information in it, product information in it, and it also has order information in it. If you scroll to the right, this is a good example of a large table that contains both fact and dimension columns. We're going to transform this data by working in Power Query Editor. So on the Home tab of the ribbon, go ahead and click on transform data to open that window. We are going to make a copy of the orders query and transform the copy into a customers Dimension query. In the queries pane on the left, go ahead and right-click on orders and choose copy. Right-click anywhere in the queries Pane and choose paste. We're going to remove the columns that we do not want from the orders to instance the pasted version, and we're going to do that by using choose columns on the Home tab of the ribbon as we did in a previous module.

The first thing I'm going to do in choose columns is uncheck select all columns, and then I'm going to select the columns that we want in the customers Dimensions query: customer ID, customer name, customer segment. We're going to grab region, state, city, postal code, and go back up to the top and select discount as well, and click OK at the bottom. So now we only have those fields, and we said that in a dimension table you need to have a key field. A key field, also known as primary key, is a unique identifier that makes each record unique. So we already have one in this table; it's the customer ID field. Each customer is assigned their own unique ID, and we'd like that to be the first column. So we're going to click and hold on the customer ID heading and drag it to the left so it's the first column. Go ahead and make customer name the second column, then City, state, zip, or postal code rather, and region, and again this is the order of the columns, and then we'll have customer segment and discount as the last column. The last thing we'll do is give the query a more appropriate name. So over to the right in the Properties Group, I'm going to select orders 2, and you'll see this convention sometime, so I'm going to type capital D I M for Dimension and then capital C customers. You'll see it identified as a dimension table by the name on occasion. I'm going to press Enter to make that change happen. Now you get a chance to try this on your own. Make another copy of the orders table, and you're going to want to choose the four columns that begin with the word product and include them only in a new table. I'm going to rename the query Dim products over in the Properties Group and press Enter. Now we said that a dimension table has to have a key field. In this case, we don't have a key field. I'm going to show you how to create one in just a moment. First, let's rearrange these columns. We want product name to be the first column. After that, we need another column that we can assign a numeric value to each unique product name. In order to do that, we're going to go up to add column tab on the ribbon, and in the general group, you're going to click on the drop down next to index column, and we're going to choose custom. We're going to say we want the starting number for our products to be 001, and we want to increment by one, and click OK. So notice it dropped off the zeros, and that's fine. We're going to move that index column so it is the first column in this data set, and each product name has its own unique index number. The last thing we need to do here is save our changes and load them back into our data model in Power BI Desktop. So we're going to go to the Home tab of the ribbon and click Close and Apply. Now that our data is loaded into the data model, let's expand dim products table in the fields Pane, and we decide we want to rename that index field that we added. We want to rename it product ID. So in the fields pane, I'm going to right-click on it, and I'm going to choose rename and just type product ID and press Enter, and we'll go ahead and save our desktop file.

The next part of this lesson is creating a hierarchy. We're going to create a hierarchy based on the region field in the orders table. A hierarchy is a container of sorts, a way of grouping related fields together. When you're creating a hierarchy, you want to start with the broadest category in terms of column and end with the narrowest category. So in order to do this, we're going to right-click on region in the fields pane under the orders table, and we're going to select create hierarchy. If you notice now, right underneath the region field, there is a collapsed region hierarchy, and I'm going to click the expand arrow, and you'll see that it only contains the field that we base the hierarchy on, in this case region. So now we want to add the next broadest category. After region would be state. So in the fields pane, I'm going to right-click on the State field, and I'm going to hover over add to hierarchy and click on region hierarchy. We're going to add two more fields to the hierarchy. We're going to go ahead and right-click on City, add to hierarchy region hierarchy, and do the same for the postal code field. Now, when you look at the region hierarchy, it contains region, state, city, and postal code. The only indication you have of the hierarchy is the symbol in front of region hierarchy, and this is something that you won't see until we get to reporting visualizations in a future module. It gives you the ability to drill down on a visualization through the levels in a hierarchy. Go ahead and save your file. In module 2, you learned that Power BI has the capability of detecting existing relationships when you bring data in from a database or maybe from an Excel Power Pivot file. Sometimes, though, when you're bringing in from a regular Excel file, it is not going to be able to detect relationships because they don't exist. We're going to do a deeper dive in this lesson creating model relationships. You'll hear the term relationships and cardinality used interchangeably. Basically, there are four types of relationships that are supported in Power BI: one-to-one relationship, represented by one:one, as seen on the slide. An example of that would be one manager has one region, if the manager's tables and the regions table were separate tables. The one-to-many relationship is the most common; it's represented by one:asterisk. An example would be one customer has many orders, two separate tables. The many-to-one relationship is the same as one-to-many.

It's just in the opposite direction, and it's represented as asterisk:one. Many-to-many relationships are the least common; they're represented by asterisk:asterisk. It we don't have a situation on our data that would require a many-to-many relationship, but the example would be many students are in many classes.

I'm going to switch back over to the sample Superstore Power BI file for this lesson. Let's start by reviewing the relationship settings in Power BI. I'm going to click on the file tab, options and settings, and then options. In the options button underneath current file, I'm going to click on data load. We briefly reviewed these settings in module 2, but they're worth going over again now. Under relationships, if you're bringing it in from an access database or from a Power Pivot data model, the first check box is defaulted to being on, so Power BI will automatically import relationships from data sources on first load. As you saw when we loaded the access database, the third check box is also a default; it will auto-detect new relationships after data is loaded. So if we load data, and then we change some of the data and it creates a relationship with another table, it will auto-detect that for you. I always like to check the second box, which is update or delete relationships when we refresh the data in Power BI. Again, that's just for the file that you're working in. The top two check boxes are defaulted to being checked. Gonna go ahead and click OK.

Next, we'll take a look at model view, and that's the third view button on the left where you can see the relationships that have been created or where you go to create relationships. So we're going to go over to model view, and we can see that some relationships have been created in this file. First thing I'm going to do is collapse the properties and the fields panes so I get more space, and I'm going to scroll to the right and see if there are any other cable cards that are out of my view, and I'm going to just move them as far left as possible so I don't have to continue to scroll. You'll notice that Power BI already created a relationship with our new dim customers table, and we're going to explore that relationship now. The join line is the line that's going between the two tables. One thing that might make it easier is I'm going to just move the customers card underneath the orders card, and you can see that it's joined to the orders table. It is also joined to the managers table. If I hover over the join line between dim customers and orders, you'll notice in dim customers it's highlighting the customer ID field; that is the matching field between the two tables.

Thank you. We decide that we're going to focus on our original orders, returns, and managers tables, so I'm going to hide the remaining tables. In each table's title bar, there is a little eyeball symbol, and if you click on it, it will put a slash through it, so it's kind of hiding the table. You'll see this when we go back to report view; these won't show in the fields panes. We're also going to hide returned orders, dim products, and orders by region, and I'm going to just move those kind of over to the right, the ones that I'm not going to use right now. Notice the relationship lines stay intact when you're moving them. As we look at these three cards, orders, returns, and managers, I'm going to size them so I can see all of the fields in each card. Makes it a little bit easier to work with, and you don't have to scroll for a field or it's out of your view, and we see that we have a relationship between orders and managers. It made it a many-to-many relationship as indicated by the asterisk on each card. We want to create a relationship between orders and returns; it has to be a matching field. In this case, the order ID field is in both tables; order IDs and orders and it's in the returns table. To create the relationship, I'm going to click and hold on order ID in the orders table and drag it and drop it on top of order ID in the returns table, and you'll notice that it automatically created the relationship, and it made it a one-to-many relationship. One return can be on many orders is what that is saying. If we want to see details about the relationship, we can double-click the join line in between the two tables, and you'll see in the upper half of the edit relationship screen it's showing a subset of the orders table data, and Order ID is the matching field. Order ID is also the matching field in the returns table, and it's a many-to-one or one-to-many type relationship. We can click OK to close the edit relationship window.

Let's go ahead and review the model interface. The first thing I want to bring your attention to is the manage relationships button on the Home tab of the ribbon. Let's go ahead and click on manage relationships, and the dialog box opens. Good tip about this dialog box: if you go and do major table transformation, come back to model view, click on manage relationships, and at the bottom click the auto-detect button, so it will let you know if it found no new relationships, as in our case, or it will create the relationships is it found any. We're going to close that dialog box. On the right side of your screen, expand both the properties pane and the fields pane. In the fields pane, expand the orders table and click on the order date field. You'll notice that the properties pane has updated to indicate that you're looking at properties for the order date field. In those properties, it has a formatting section. So when we changed our date format for the order date and the ship date, we did that in data view; we could also do a similar thing in this view. I've selected the returns card, and when I look at the properties, you'll notice that it's about the returns card, actually the returns table. You'll see that that table is highlighted in the fields list as well. If you wanted to, you could put in a description for your table; you can put in synonyms for your table. The synonyms tie into a feature that you'll learn when we get to creating visualizations called Q&A, question and answer. It's an analysis feature that's built into Power BI; it's available in both the desktop and the service. And so if someone were to type in a question using any of your synonyms, they would get results. The row label field is used for Q&A as well. Key column normally it detects the key column in a table; there are instances where it might get it wrong. For example, in the orders table, you have the order ID, you have customer ID; they're both key columns. One is for the orders table, one is for the customers table. So in this case, it's getting it okay, but if you needed to change it, you could change it here. We already saw what happens; we hid the tiles by using the eye icon in the upper right corner of their title bars. Featured table is an interesting feature that you can use here; it's actually pretty cool. Once this data set is published to the service, then the featured table takes effect. A featured table is an easy way to share specific table information with other users, either through workspaces in the online spaces, or you can actually get stuff into Excel this way via data types in Excel. Let's go ahead and save our file again and switch back over to Data View.

Now that we're back in Data View, we can see in our Fields pane to the right that the four tables we marked as hidden in model view have that eyeball with the slash through it in the fields pane. If we go to report view, our first view button, we'll notice those tables are hidden from our view. Our final lesson in this module is row-level security (RLS). RLS in Power BI can be used to restrict data access for given users. Filters restrict data access at the row level, and you can define filters within roles in the online service. Members of a workspace have access to data sets in the workspace. RLS doesn't restrict this type of data access. In the desktop, you set up the security roles, and in the service you would assign users to those roles, as you'll see in this next lesson. We're going to set up a role that allows users that are assigned to that role to view order information just for the East region. To do that, we're going to be using the modeling tab on the ribbon, and in the security group, go ahead and click on manage roles. To create our role, we'll start by clicking the create button, and we're going to name the role East. In the center of the manage roles box, you're going to click on the orders table to let it know that you're using a field from the orders table. Over to the right, you're going to put in a simple DAX expression. DAX is data analysis expression; it's a formula language, and you'll learn more about it in the next module. In the meantime, you're going to click in that table filter DAX expression box on the right, and you're going to type an open square bracket, as field names need to be enclosed in square brackets. You're going to type region and a closing square bracket, then you're going to type the equal sign, and then double quotes because it's a text-based field. You're going to type East and close the double quotes. So we're saying for this role that we're calling East, only allow users that we assign to it to see the Eastern region information from the orders table. In the lower right-hand corner, you're going to go ahead and click save. Go ahead and set up another role for the West region in the orders table. Power BI is really good about letting you know when you have errors and expressions, but when you want to check before it notifies you, in the upper right corner of the manage roles box, I can click on the check mark, which says verify DAX expression. If it found anything wrong with my expression, it would let me know. At this time, I'm going to go ahead and save. To test your roles, also on the modeling tab, right next to the manage roles button is view as. When you click on view as, you'll see your East and your West; you'll also have other user. Go ahead and check the box in front of East and click OK. You'll notice the yellow band at the top of your screen that says now viewing as East. Go back to Data View, and when you click on the orders table, you'll see that it's only showing the East region. We're going to click on stop viewing in the yellow band, and now we're seeing the full data set. We're going to save our desktop file, and then the last button on the Home tab is publish. Let's go ahead and publish it to the online service. You can use my workspace; that's the one we'll all have in common. I'm going to go ahead and publish it to my Power BI video workspace and then click the select button. You'll have the publishing the Power BI splash screen on your window, and when the data set is successfully published, you'll see success. You can click on the link right underneath success to go ahead and open that file in the cloud-based service. Power BI service opens in Report View, and we don't have any reports. What we want to do is go to our workspace, so the bottom icon on the left side will take you to your workspace that you saved this stuff to. So it saved the report, which is currently empty; it also saved the data set. To assign roles to our East and West that we set up in the desktop, we're going to hover over the data set, and we're going to go to the vertical ellipsis, more options button, and we're going to select security. On the left side of row-level security, we'll see the two groups that we created, East and West; they both have zero members in the group. In the members area, you can use distribution groups, mail-enabled groups, security groups, individual email addresses; you just cannot use groups created in Power BI. I'm going to enter an email address from my organization and click the add button. After I do that, I'm going to click save at the bottom, so it saves that person to my East group. Go ahead and add someone from your organization to your West group and save. Switch back over to your desktop file and click got it on the publishing the Power BI window. Go ahead and save your file.

To recap this module, you learned about the basics of data modeling through working with tables. We implemented dimensions and hierarchies; we defined relationships and cardinality and enforced row-level security, setting up the roles in Power BI Desktop and assigning the users in the Power BI service.

Hi everyone, I'm Trish Connor Cato, and I'd like to welcome you to Microsoft Power BI. In this module, we're going to create model calculations using DAX. You'll be introduced to the world of DAX and its true power for enhancing a model. You'll learn about aggregations and the concepts of quick measures, measures, calculated tables, and calculated columns to solve calculation and data analysis problems. You'll also learn about time intelligence functions and key performance indicators. First, let's talk about what is DAX. DAX stands for Data Analysis Expressions and is the formula language used in Power BI. The structure is somewhat different than basic Excel functions, as you'll learn in this module. DAX is a collection of functions, operators, and constants that can be used in a formula or expression to calculate and return one or more values. DAX context enables you to perform dynamic analysis in which the results of a formula can change to reflect the current row or cell selection and any related data. We'll be using the sample Superstore and retail sales analysis desktop files in this module. Again, you can find the files in the video description, and we started building the sample Superstore desktop file in module 2.

Okay, we'll be starting with the sample Superstore desktop file. If you want to get that open, this module has five lessons. We're going to start by creating calculated tables; then we'll create calculated columns. You'll learn about quick measures and measures and how to create them; then we'll work with time intelligence functions and end up using key performance indicators. Let's get started. We're going to start by using the DISTINCT function in DAX to create a calculated table that will return one column of all of the distinct order IDs from the orders table. To do this, we're going to go to the modeling tab on the ribbon, and in the calculations group, go ahead and click on new table. You'll see two changes on your screen: you have a formula bar that says `one table =`, and over in your Fields pane to the right, you have a new table called Table right now. We're going to name that table distinct orders count, so go ahead and double-click the word Table in the formula bar, and we'll just type distinct order count, and we're going to click after the equal sign to build our distinct DAX function. What we really want is a count of the distinct order IDs in the orders table, so distinct means here the total number of different values regardless how many times those values appear in the table. We're going to start by beginning the type the function name DISTINCT; it will come up on the list. I usually stop typing at this point so I make sure I don't make a typo. I can double-click DISTINCT from the list, or if it's already highlighted like it is in my case, I'm going to press the Tab key on my keyboard to tab it in so it gives me the DISTINCT function and an open parenthesis. Right underneath your formula bar in bold, it says column name or table expression; that is the only function argument for DISTINCT. So we want it to be for the orders table, and in particular the order ID field. I'm going to start typing orders as in the table name, and you'll notice the orders table shows up on the list as well as every field within the orders table. When you see Orders[Order ID], you can double-click it, or you could go down and highlight it and tab it in. The syntax here is the name of the table, and then the column within that table is enclosed in square brackets. At this point, we can press Enter, and it will perform the calculation and return our one-column table. Since we're in Report View, we're not going to be able to see the data, so on your left side go to your second view button, which is Data View.

And you'll see your distinct order count table on the right side in the Fields pane. When you click on it, you'll see that the table contains one column, and it only contains the distinct order IDs. If you look all the way at the bottom of the screen in the status bar, it tells you there's 1746 rows, so there are 1746 distinct order IDs in the orders table. In your Fields pane, click on the orders table and look at the status bar, and you'll see that there's 2428 rows. So the DAX DISTINCT function returns a one-column table populated with distinct values. Go ahead and save your desktop file. Our next lesson will be about creating calculated columns. Like calculated tables, calculated columns become part of your data set. We're going to use the DATEADD function to calculate the difference in days between the order date and the ship date in the orders table, and we're going to name the column days to ship. To get started, what I'm going to do, we can do this from the ribbon, or we can do it by right-clicking in the Fields pane. So what we're going to want to do is I'm going to right-click on the orders table in the Fields pane, and I'm going to select new column.

Thank you. The same effect happens when we did a new table where you in your formula bar you get `one column =`, and in your Fields pane you have a column called Column in this moment. Well, we're going to name it days to ship, so in the formula bar I'm going to double-click the word Column, and I'm going to type days to ship, and then I'm going to navigate to after the equal sign. We're using the DATEADD function, so I'm going to start typing `da`, and when I see it on the list, I stop typing again. You want to avoid typos wherever possible. In this case, I'm going to use my down arrow to highlight DATEADD in the list, and I'm going to use my Tab key to tab it in. Again, just like with the calculated table, right underneath the formula bar, you're seeing the syntax of the DATEADD function. The first argument would be date one, the second one would be date two, and the third would be interval. It's waiting for the date one argument right now, which is why that's in bold. So I'm going to start typing orders for the name of the table, and I'm going to arrow down in the list until order date is highlighted, and I'm going to tab it in, and we want the entire date, so a subsequent menu comes up; date is already selected, and I'm going to tab that in. Now we're going to use a comma, which separates the arguments. You'll notice now that date 2 is in bold, as that's the argument it's looking for. We want the orders ship date for that second date, so I'm going to start typing orders again, and I'm going to just down arrow until ship date is highlighted and tab it in, and again we want it to assess the full date, so I'm going to tab in date, and I'm going to type a comma. The interval we want the difference in dates in is days, so day is already highlighted; I'm going to tab that in, and I'm going to press Enter.

Okay, you'll see your screen flicker while it's doing the calculation, and now if you look all the way to your right, the last column in the orders table is days to ship in the interval of days. Go ahead and save your desktop file. We created the days to ship column by right-clicking on the orders table and choosing new column. Since we're in the days to ship column, we have the Column Tools tab on our ribbon, and that is another way that we can create another calculated column. We're going to create a calculated column that shows a ranking by sales, so the highest sales values will have the rank of number one, and the lowest ones will have the rank of a higher number depending on how many sales values are in the orders table. We're going to be using the RANKX function to do this, and it has several variations; we're going to go through three of them. On the Column Tools tab of the ribbon, the last icon is new column. Make sure you're anywhere within the orders table in the Fields pane.

And then click the new column button. It's going to do the same thing as when we right-clicked on orders and chose new column.

In the formula bar, we're going to go ahead and rename the column. So I'm going to double-click the word column, and I'm going to call it Sales Ranking Highest Sales Ranking Highest. And then I'm going to get myself after the equal sign, and the function we're using is RANKX. So if you start typing it, R A N, you will see it on the list, and you can double-click it or highlight it and tab it in.

So notice this particular function has five possible arguments. Whenever you're looking at the syntax, uh, arguments that are optional will be in square brackets. So there are two required arguments and three optional ones. For this example, we'll be using the two required arguments: table and expression. For the table argument, we're going to reference the orders table, so it has a separate table argument. So I'm going to just start typing the letter O, and the orders table by itself shows up on the list, and I'm going to tab that in. Now I'm going to do a comma so I can get to the expression argument. What we're assessing here is we want a ranking of the sales field in the orders table. So I'm going to start typing orders again, and then it'll show me the table as well as all the fields in the table, and I'm going to highlight Orders Sales and tab that in. We're ignoring the three optional arguments right now, and we can just press Enter here, so it performs this calculation in the newest column, which is all the way over to your right. Remember the highest ranking sales would be numbered starting with one; the lowest sales values would be higher numbers.

At this point, go ahead and save your desktop file. We're going to edit the RANKX function we just did because we want to rename it Sales Ranking Lowest, and we're going to use another argument so that the lowest sales values have the lower rankings starting with one, and the highest sales values have the higher ranking numbers. We're going to do our edits in the formula bar. So the first thing I'm going to do is I'm going to double-click the word Highest and change it to Lowest. And inside the parentheses, I'm going to click after Order Sales, after the closing square bracket and in between the parentheses, and I'm going to type a comma there, and the syntax will show up again. When we type the comma, it's waiting now for the optional value argument, and we want to skip that argument, so we're going to type another comma, and it will advance to the order argument. We have ascending and descending; it's defaulting to descending, which is why we're getting the lowest sales having the higher numbers and the higher sales having the lower ranking numbers. We're going to switch the order to ascending, so ASC is already highlighted; I'm going to tab it in and I'm going to press Enter, and you'll see that the Sales Ranking Lowest column, it's now doing the opposite: the lower sales values are having the lower number rankings, and the higher sales values are having higher number rankings.

Let's talk about how RANKX handles ties. So imagine we had two sales values of, I'll just say, one dollar each, because we have it in ascending order now. If those were the lowest sales values, they would each be ranked number one, and then it would skip the next number. Since you have two at number one, you wouldn't have anything ranked number two; it would move to the next number, which would be three, unless you tell it otherwise. And that's the last optional argument. So in order to demonstrate this, I'm going to go ahead and open the Excel sample Superstore file so we can make a change in there. We're going to change one value in this Excel file to force a tie in sales values. I'm going to use the name box up here in the upper left corner, and I'm going to just go into the name box, and I'm going to type W369 and press Enter, and it will take me to that cell. So cell W369 is selected, and I want to change that value in that cell to 52.97, mimicking the value above it. So I'm going to just type 52.97, press Enter, and I'm going to go ahead and save and close this Excel file. To get that change to show in Desktop, I'm going to have to refresh, and that's on the Home tab at a ribbon in the Queries group. Go ahead and click the Refresh button, so it will bring in the changes we just made in the source data file.

Now let's take a look at the Order IDs that we have the tied sales values in. So I'm gonna go to the Order ID Auto filter drop-down, and in the search box, you're going to type 8853, and you'll see several orders come up, Order IDs with those numbers in it. What we want to do is we want to uncheck Select All, and you're going to check 88538 and 88539 and press OK. You'll see that both of these orders have the same sales value, and the ranking is the same: 546. We're going to clear our filter from Order ID now by going to the funnel and selecting Clear filter, so we get our full list back and go to your Sales Ranking Lowest Auto filter, and when you get in there, go into the search box and type 547. So that would be the next ranking; we had two at 546, right? And you'll see there is no ranking 547 because it's going to skip that number. If we had three at the same value, they would all be 546, and then it would skip two numbers. Change your search to 548, and you'll see that that ranking is in the list, and you can just cancel the Auto filter box. Skipping is the default with the RANKX function. So again, when you have sales values that are the same and they tie, and their number in the ranking is like 546 and there's two of them at that number, it's going to skip 547 unless you tell it not to do so by using the optional last argument.

So we're going to modify this RANKX function again. We're going to first go up to the formula bar, and we're going to change it after the word Lowest; put No Skip, so we'll know this is the one that's not going to skip any numbers. And after that, you're going to click after ASC and inside the closing paren and type a comma, so you can see that last optional argument is the Ties argument. If you don't use this argument, it defaults to skip, and we've seen that behavior. We're going to tell it to use the Dense argument, so it won't skip any numbers. So since it's already selected, I'm going to tab it in, and I'm going to press Enter, so it recalculates. Now keep in mind, since it's not skipping numbers, the rankings have changed. If you want to look up Order IDs 88538 and 88539, you'll see that they're still tied but at a different number, the subsequent number. If you filter for Sales Ranking Lowest No Skip, it will be the next number; it won't skip any numbers. Go ahead and check that out, and then save your file.

So far in this module, we use the DISTINCT function to create a calculated table; it returned a table with one column of distinct values. Then we created a few calculated columns; we used DATEADD and the RANKX function to create these columns. Both the table that we created and the columns that we created become part of your data set. Now we're going to switch gears and talk about measures. Measures could be called virtual calculations; they don't become part of your data set; they only calculate when you add them to a report visualization. So if file size is an issue, you may want to use measures instead of calculated columns or calculated tables. There are two variations of measures: there are quick measures, which are templates of sorts, and then there are measures which you build from scratch. We're going to get started using quick measures. We want to create a quick measure in the Orders table that will give us the average sales based on customer segment. So you'll notice, you know, in the Orders table, we have the Sales field; we also have a Customer Segment field. To create the quick measure in the Fields pane, I'm going to right-click on the Orders table, and I'm going to select New Quick Measure. The dialog box will open for quick measures, and on the left side, you have Calculation and a drop-down, and on the right side, you have your fields from all of your table instances, just like you do in other views. Where it says Select a calculation on the left side, we're going to go ahead and do the drop-down. You want to take a moment and look through; they have different headings in here: Aggregate per category, filters, time intelligence, so on and so forth. The one that we want to use is we're looking for one under the Total section, and we're going to select Total for category filters not applied. That's the calculation we want to use. For the base value, we're going to expand the Orders table on the right side, and we're going to just drag the Sales field into the base value text box, and it says Sum of Sales, but we want the average of sales. So I'm going to go over to the ellipses, the More Options button, and instead of Sum, which is the default, I'm going to click on Average. And for Category in the fields list, we're going to grab the Customer Segment field and drag it into the Category box. So a quick measure allows you to make choices in this dialogue, and it will create the calculation for you. In the bottom right corner, go ahead and click OK. And again, it won't become part of your data set. So when you do a calculated column, it puts it at the end on the right of your data set. This is a virtual calculation, but if you look up at your formula bar, you'll see based on the selections in the quick measures dialog box, it built a nested calculation for you. It used the CALCULATE function with a nested AVERAGE and a nested ALL function. Foreign Pane and notice the icon in front of it that represents that it is a measure. Now you won't be able to see this like in Data View; you can only see it on a report visualization. We haven't gotten to visualizations yet, but let's go ahead and build a simple one so we can see the result of your measure. So on the left side, I'm going to go to Report view, the first view button. Foreign view, just go to your Fields Pane and drag your Average of Sales Total for Customer Segment measure directly into the center of your screen where it says Build visuals with your data. Again, we'll be doing a deeper dive in reporting visualizations in a later module, but I just want you to see the results of your measure. Also in the Fields pane, you're going to go ahead and grab the Customer Segment field and drag it to the Axis box right in the Visualizations pane, drag it and drop it. We're going to do one other field for this; we're going to grab Product Category field, and we're going to drag it to the Small Multiples box in the Visualizations pane, and I'm going to just use the sizing handles on my visualization to make it wider, and you can see the Average of Sales Total for Customer Segment and broken out by Product Category. So if I hover over the First Column under Furniture, you'll see the tool tip; it tells me the average of sales, 2139.07. For Technology, when I hover over it, you'll see the tool tip update, so on and so forth. So it didn't calculate that quick measure until we added it to a visualization. And with your visualization still selected, and you can see the sizing handles around it, just go ahead and press Delete on your keyboard to get rid of it and save your desktop file.

Now we're going to create a measure from scratch to calculate the average sales per product category for the Orders table. So I'm going to just right-click on Orders again in the Fields pane; this time I'm going to do New Measure instead of New Quick Measure, and it just brings up the formula bar. So we're going to name this measure Average Sales per Product Category. Remember whatever is before the equal sign is the name of the item, so Average Sales per Product Category, and navigate to after the equal sign so we can use the functions that we're going to use. We're going to start with the CALCULATE function, so start typing it in, and by the way, when it's highlighted on the list to the right of the highlight, it tells you what the function does. So this evaluates an expression in a context modified by filters. So we're going to go ahead and tab in CALCULATE. The first argument is expression; we want to calculate the average of Orders Sales field. So for the expression argument, we're going to use the AVERAGE function. Start typing AVERAGE, and when you see it, you're going to go ahead and tab it in, just plain AVERAGE, and we want the average of the Sales field in the Orders table. So I'm going to start typing Orders, and I'm going to just do my down arrow until Orders Sales field is selected, and I'm going to tab it in. Now we want to get back to the CALCULATE function at this point, so after Order Sales, we're going to go ahead and type a closing paren, and it takes us back to CALCULATE. We're still in its expression argument, and now we want to advance to the filter argument, which is optional, so we're going to type a comma, and it takes us to that filter. One argument for the filter, one argument. We're going to use another function, the ALLSELECTED function. So I'm going to start typing ALL. When I see it on the list, I'm going to highlight it, so it tells you that ALLSELECTED returns all the rows in a table or all the values in a column, ignoring any filters that might have been implied inside the query but keeping filters that come from outside. Let's talk about what that means for a moment. In a later module, when we do a deep dive into reporting visualizations, you'll learn how to use the Filters pane in Report view to apply filters for your visualization; that would be considered filters applied inside the query. A filter that comes from outside the query would be, for example, the slicer visual visualization, which would be a separate visualization, so it would be considered outside of the query. A slicer is a way of visually filtering. So when you're using ALLSELECTED, it would ignore any filters inside the query, for example, like on this Filters pane, but it would keep filters that you can control from outside the query. In our case, it's going to return all of the product categories; we don't have any filters, so it's going to return all of them. So I'm going to go ahead and tab in ALLSELECTED, and I'm going to go to the Orders table, so start typing Orders, and we want the Product Category field this time from the Orders table, and I'm going to get that in. We're going to need to type two closing parentheses at the end of this and press Enter. So when you do that, it sets up the measure for you; it shows in your Field Pane, and again, it will only show on a reporting visualization as it is not part of your data set. Foreign Superstore file.

The final lesson in this module is using time intelligence functions and key performance indicators. We're going to get started with time intelligence functions. In order to use these types of functions in Power BI, you have to have what is known as a date table in your data model. We don't have a date table in our data model, so we will create one. There are two different DAX functions that you can use to do this, one of which is named CALENDAR. For the CALENDAR function to work, you have to provide it with a start and an end date, and it will build a table for you with one column with all of those dates. CALENDARAUTO is the second function, and that one can scan your data and determine the earliest date and the latest date in your data model. We're going to use CALENDARAUTO; it will return one column with all of the dates in the data model. After we create that table, we're going to amend it by adding other columns. So let's get started. In your Fields pane, make sure the Orders table is selected, and then on the Table Tools tab of the ribbon, you're going to click New Table. In the formula bar, we're going to double-click on the word Table, and we're going to name it Dates, and navigate to after the equal sign. We're going to use CALENDARAUTO, so I'm going to start typing CA. When it shows up on the list, and you'll see that it says it returns a table with one column of dates calculated from the model automatically. I'm going to go ahead and tab it in, and we are just going to do a closing parenthesis at the end and press Enter. So if you look over in your Fields pane, you'll see the Dates table. When you expand it, it has one column called Date, and you can take a look at the dates. If you go over to your left, go to Data View, and you'll see that it has a date range starting from January 1st, 2010, and it goes all the way through 12/31/2014. So our earliest date and the latest date in our data model, and every date in between is in one column in this new table.

Foreign to having the full date column, we would like in this table to have the year of each date in a separate column, as well as the quarter and the month in two different ways. So we are going to nest our CALENDARAUTO function within an ADDCOLUMNS function. And to do that, you're going to click after the equal sign, right before CALENDARAUTO in the formula bar, and you're going to type, start typing ADD, and ADDCOLUMNS function comes up. It does exactly what it sounds like; it's going to add more columns to this table. I'm going to go ahead and tab it in. Now we're going to click after the closing parentheses after CALENDARAUTO, and we're going to type a comma. So this is now part of the Add Column syntax; it wants the name of the column that we're adding. We're going to have to put it in double quotes because it's text, so in double quotes, I'm going to type Year and close the quotes and type a comma, and I'm going to use the YEAR function to extract the year of the date. So I'm going to start typing YEAR; it shows up on the list; I'm going to tab it in, and what you're going to do is you're gonna just in square brackets, you're going to type Date. Well, when you type the squared brackets, you'll see Dates show up on the list, so I'm going to select it from the list instead of typing it. So to extract the year of the date, now I'm going to do a closing paren to close out the YEAR function, and I'm going to type a comma. Now I don't want this to be one long run-on function, so I'm going to press Shift+Enter to get down to the next row to continue this function. The next thing we would like to extract is the quarter, so we want another column. In double quotes, we're going to name it Quarter, comma, and we want the letter Q before the quarter number. So in double quotes, we're going to type the capital letter Q; we're going to use the Ampersand for concatenation, meaning combine the Q with the quarter number, and we're going to use the QUARTER function. So go ahead and start typing it and get it in on that bracketed Date field. So I type the square bracket, and it popped up on the list. We need a closing parenthesis, another comma, and you're going to Shift+Enter again to get down to the next line. We want to extract the month of the date in two different ways: we want the short name of the month or the full name of the month actually, and we want the month number in two separate columns. So we're going to name another column Month, and that's going to be in double quotes, comma, and we're going to tell it how to format the date. So we're going to use the FORMAT function, and you can get that in there, and we're doing it on the Date field. So I type the square bracket, and I'm grabbing it from the list. We're going to do a comma, and now we're going to tell it what format. So in double quotes, I'm going to type four lowercase M's to indicate, give me the full name of the month. If we did three M's, it would

Be an abbreviation of the month. We're going to close the double quotes, closing parenthesis, one more comma, and shift enter. We also want to extract the month number, so we want to have a column called "month number". We're going to do that in double quotes, comma. We're going to use the month function here; the month function extracts the number of the month, and we're going to do it on that bracketed date field, which is the only field in this table so far. We're going to do a closing parenthesis, and I'm going to do shift enter one more time and type one more closing parenthesis. Now, when you press enter, the dates table will update and it will show the additional columns that we added.

So calendar Auto only returns one column, the date column. We use the add columns function to add more columns to this table. Go ahead and save your file. Once you have your date table created, you need to mark it as a date table so Power BI will know which table to reference when you're using time intelligence functions. We're going to, in the fields pane, we're going to right-click on our dates table and open the shortcut menu. You'll see "Mark as date table"; hover over that and then click on "Mark as date table". The dialog box opens, and it needs you to just select the column; in this case, the column to be used for the date in our new dates table. We only have one column, and that is the date column that it automatically created, the column that holds the full date, so that's the only one that's going to show up on the list here. It tells you it was validated successfully, and you can click OK. So we've just created a date table, added more columns to it, and marked it as a date table.

The last thing we need to do is we need to relate the date table to our orders table. Let's go to model view on the left side, last view button, and we're going to do it based on the order date field in the orders table. So I'm going to click and hold on the order date field and drag it and drop it on top of the date field in the dates table and let it create that one-to-many or many-to-one relationship. Many order dates can be in the dates table; is what's that what that is saying. Go ahead and save your file.

Foreign table related to our orders table. Let's go back to data View, and we're going to scroll to the right. Since we did that relationship, you'll notice that our days to ship calculated column is filled with arrows because we're telling the orders table to look at the dates table. It's not recognizing the .date portion of the order date or ship date fields anymore. So, in order to resolve that, we're going to get rid of them in the formula bar. So, in your DATEIF function, after orders[order date], you're going to delete the .date that's in brackets after that. So we want orders[order date] and then just a comma, and we're going to do the same for orders[ship date]. We're going to get rid of the .date after it, the qualifier, and we're going to press enter after we do that, and it will recalculate. Now, sometimes it'll try to put the .date back in there; if it does that, just go back and delete it again and press enter, and it will update. So now you have your days to ship. The reason that happened is we did that calculation between dates before we created a dates table and related it to the orders table, so now it's looking at the dates table for information.

We're going to add two more calculated columns to our data set for the orders table. We want one to show the end of the month for each order and another to show the end of the quarter for each order. Go ahead and right-click your orders table in the field pane and choose "new column", and we're going to name this column. In the formula bar, you're going to name the column "end of month" and position yourself after the equal sign. Well, one at a time, intelligence functions in Power BI is ENDOFMONTH, so start typing ENDOFMONTH. When you see it on the list, you can tab it in, and it wants to know the end of the month for what date, so it's the orders[order date]. So I'm going to start typing "orders," and then I'll see the table as well as all of its fields, and I'm going to just go down until I have orders[order date] highlighted and tab that in.

Foreign. You can go ahead and press enter, and you'll see the "end of month" column populates, and it has the last day of each month for each order that's listed in the orders table. We're going to do another new column in the orders table, and this one is going to be for the end of quarter. So I'm going to right-click on "orders" again, choose "new column". I'm going to name the column "end of quarter" and get after the equal sign, and there is an ENDOFQUARTER function, which I'm going to use on the same orders[order date] column and press enter. So we just increased our data set by adding two more calculated columns, "end of month" and "end of quarter". Let's go ahead and format these two columns so they match the order and ship date column formats. I'm going to click in the "end of month" column, and on the column tools tab of the ribbon, I'm going to access the format drop-down and select the date format that says "March 14, 2001". Do the same for the "end of quarter" column. We're going to use a different desktop file for our final topic in this module, so go ahead and save and close your sample Superstore desktop file. Open the retail analysis sample desktop file that you grabbed from the video description, and we're going to use this file for key performance indicator visualization. This file has more detail than a sample Superstore file. On the left side, let's go ahead and go to data View, and if necessary, expand the sales table in the fields pane. We have comparative columns in this data, two of which are of interest to us for our key performance indicator. There's a calculated column called "Total Units Last Year", and there's one called "Total Units This Year" that gives us the ability to compare our progress. I'm going to collapse the sales table and expand the time table. The timetable has a "Fiscal Month" column, and we're going to use that to be able to see a comparison between last year, this year, by fiscal month.

A key performance indicator, commonly known as a KPI, is a critical or key indicator of progress toward an intended result. It's a visualization type in Power BI. Let's go to report View again; that's the first view button on the left-hand side. And when you get to report view, do the plus sign at the bottom of the screen to create a new page. When we get to module 7, we'll do a far deeper dive into reporting visualizations, but for right now, we're going to use the fields that we looked at to start creating our KPI. Now, before you create the KPI, you actually start building the report without the framework of a KPI. The reason why is once you convert the report that you build into a KPI, you won't be able to have sorting capabilities. So this is how it works: In the fields pane, we're going to expand the sales table, and we're going to grab the "Total Units This Year" calculated column and drag it right into the center of your screen onto the canvas. Now we're going to expand the time table in the fields pane, and we're going to drag the "Fiscal Month" field into the highlighted box on the canvas. At this point, this is not a KPI visualization; it's a clustered column chart. Let's expand the chart so it's a little bit wider. We can use the sizing handles, and if you want to move it, you can click in a blank area, and you can move it around on the canvas. This is the point where you would want to perform a sort before you turn it into a KPI visualization. We'd like to sort this in ascending order by fiscal month. So, in the upper right-hand corner of the visualization, you'll have the more options vertical ellipsis icon, and we're going to click that and hover over "Sort by". At the bottom, you're going to click on "Fiscal Month", and you'll see that it's sorted it by fiscal month. If we go back to the ellipses, you can see that it's sorted it in descending order, and we're going to want to click on "Sort Ascending". Now we have it sorted in ascending order. Go ahead and save your retail analysis sample file, and with your visualization still selected, you can tell it's selected by the sizing handles around it. We're going to convert it to the KPI visualization. In the visualizations pane, you want to find the KPI visualization; it looks like it has a green triangle and a red triangle on it; kind of looks like a table, and I'm going to point to it now on my screen. When you find that visualization, go ahead and click on it, and it converted our column chart into a KPI visualization. When you look in the visualizations pane now, you'll see that it put "Total Units This Year" as the indicator; it has "Fiscal Month" as the trend access, and now we just need to add a field for Target goals. So we're comparing "Total Units This Year" to "Total Units Last Year". In the fields pane, I'm going to grab "Total Units Last Year" and drag it into the target goals box, and now you'll see our KPI visualization is complete in terms of the comparison. In the visualization, the shaded area in the back is your goal area. It tells you the value, and then it tells you the goal, and along with the goal, it gives the percentage difference. In this case, we're negative, and so it also indicates a negative response with the exclamation point to the right of the value. The last thing we're going to do here is rename this page, "Page 1". We're going to double-click on it, and we're going to name it "KPI".

Foreign. Press enter so it will accept the page name change, and go ahead and save your retail analysis sample file. In this module, we explored the world of DAX, Data Analysis Expressions. We learned that it's the formula language used in Power BI. We use DAX for simple formulas and expressions, and we created calculated columns and tables based off of DAX functions. We also created quick measures and measures, both virtual calculations. We worked with time intelligence functions after creating a dates table, and you learned how to create a key performance indicator visualization. In the word document that's in the video description, it's called "Website Links and More Information"; there are links to more information about DAX functions from various sites. Module 6 is optimizing model performance. You'll be introduced to steps, processes, concepts, and data modeling best practices necessary to optimize a data model for enterprise-level performance. We'll be using the retail analysis sample desktop file from the previous module and the sample Superstore desktop file we created in module 2. In this module, we'll use the direct query method to access a data source. We'll understand the importance of variables and how to use them in DAX functions, and we'll cover some other optimization techniques.

So far, we've imported data into Power BI. When doing so, we've loaded all the data from the data source or a large subset of the data from this data source into Power BI Desktop. In some cases, this creates a large file size and causes some performance issues. When you refresh in Power BI Desktop, it literally reloads all the data back into the data model. Depending on the size of the model, this could be a lengthy process that you perform multiple times a day. If file size is a consideration and the source data is very large and/or data is changing frequently and reports must reflect the latest data, you would use direct query. Direct query connects directly to data in the original source repository, for example, SQL Server or Azure Analysis Services, and no data is actually imported into Power BI. When visualizations are created, queries are sent to the underlying data source to retrieve necessary data. Upon refresh, the necessary queries are resent for each visual for updating. When publishing reports to the service, you will see a data set as well as the reports; however, no data is included in the data set. The data resides in the source repository. There's additional detailed information in the word document, "Website Links and Additional Info," in the video description. It'll let you know all of the data sources that you can use direct query on. We're going to get started by using the retail analysis sample desktop file where we created our KPI in the previous module. One of the data sources that you can use direct query on is a Power BI data set. In order to use a Power BI data set for a direct query, that data set needs to be published to the service. So we're going to go ahead and publish this data set to the service. We did this in a previous module, and again in a later module, we'll spend more time examining the service when we set up our dashboards. But in the meantime, the last button on the Home tab of the ribbon is "Publish". Go ahead and click on it to start the process. So we all have in common a workspace named "My Workspace". If you don't have any other workspaces available to you, you can use that one. I am going to use my Power BI video workspace by selecting it on the list. It may take a few moments to publish this as this is a fairly large data set. When it's done publishing, you'll get the success check mark, and you can get to the service by clicking the link that says "Open retail analysis sample.pbix in Power BI". Go ahead and click the link. It opens the report, which is all of the pages that are in our report view in the desktop. Because I was on the KPI page, that's the page that it's on here in the service. We want to navigate to the workspace where we sent this data. So, on the left side of your screen, almost toward the bottom, you're going to hover over the workspaces icon and select it. I'm going to click on "My Power BI video workspace", and you'll notice that it put the report, which is what we were just looking at, as well as the data set, the underlying data in the service. Because the data set is now in the service, we'll be able to use direct query in a new instance of Power BI Desktop. I've switched back over to the desktop, and I'm going to click on "Got it" on the publishing the Power BI screen. We want to access direct query from a new Power BI file, so we're going to go up to the file tab of the ribbon and select "New" on the left-hand side to start a new instance of desktop. When it opens, go ahead and close the splash screen. Before we access our Power BI data set that we published, I just want you to know that the data sources that are supported with direct query, depending on which one you choose, you're going to have to do something different maybe. So, for example, SQL Server is a data source that's supported by direct query. On the Home tab of the ribbon, in the data group, go ahead and click on "SQL Server", and you'll notice that it has a data connectivity mode section; it defaults to "Import". So if you want to use direct query to connect to SQL Server data, you would have to use the option button for direct query. We're going to go ahead and cancel that dialog. When you're bringing in from a Power BI data set, it automatically is in direct query mode, so you won't have to make a choice like you would have for SQL Server. So, on the Home tab of the ribbon, we're going to go ahead and click on "Power BI data sets", and it's only going to show you the data sets that are published to the service. We're going to click on "retail analysis sample" and then the "Create" button in the lower right corner. Before we do anything else, let's save this file, and we're going to name it "direct query". Right now, there doesn't appear to be a difference between using "Import" or "direct query" to bring data into the desktop. On the right side, you still have your fields pane; if you expand the sales table, you'll see all of the fields. But what I want you to notice is on the left-hand side, on the left side, you no longer have data view; you just have your report view, which is default, and you have modeling view. There is no data view because it didn't actually bring in any data from the underlying source. If you go to modeling view, you'll be able to see the fields in the tables and the relationships that have been created in this data. I'm going to go back to report view, and we're going to recreate the KPI report that we did in the previous module. So we're going to expand the sales table in the fields pane, and we're going to grab the "Total Units This Year" field and drag it onto the canvas, and then we're going to expand the time table and we're going to drag "Fiscal Month" into the framework on the canvas as well. Next, we're going to access more options in the upper right-hand corner of the visualization, and we're going to hover over "Sort by", and we're going to click on "Fiscal Month". We're going to go back to more options and click on "Sort Ascending". So remember, you have to sort before you turn it into a KPI because the KPI doesn't allow sorting. I've resized the column chart a little bit, and now in the visualizations pane, I'm going to go to the KPI visualization and convert this into a KPI. So when we did that, it actually sent a question to the underlying data source to retrieve the information for this visualization. Go ahead and save and close your direct query file and also close your retail sales analysis sample desktop file. Navigate to your working directory where you have saved your direct query desktop file and the retail sample analysis file and look at the difference in file size. Retail sample analysis is over 9000 kilobytes; it has a lot of data that we imported into Power BI Desktop. Direct query, however, didn't bring any data in, and so therefore that file is only seven kilobytes. If file size is a consideration and your data source is supported by direct query, it's recommended that you use direct query.

Our next lesson in this module is about variables. We're going to be using the sample Superstore desktop file. If you want to pause the video and launch that file, so what are variables? Let's get some background information. You've already been exposed to DAX functions. We've nested functions, so you can see as a data modeler writing and debugging some DAX calculations can be challenging. If you get an error in your formula or function, you have to debug it. It's common that complex calculation requirements often involve writing compound or complex expressions. As you've already experienced, compound expressions can involve the use of many nested functions and possibly the reuse of expression logic. Using variables in your DAX formulas helps you write complex and efficient calculations, so that's the why you would want to use a variable. What is a variable? You can store the result of an expression as a named variable, which can then be passed as an argument to other measure expressions. Once resultant values have been calculated for a variable expression, those values do not change, even if the variable is referenced in another expression, and we're going to focus on variables and how to use them in our calculations in this lesson. So what can variables do for you? Why are they important? They can improve performance; they can improve the readability of your functions and expressions; they can simplify fixing them if they're broken, also known as debugging; and they can reduce the complexity of a calculation. We're going to start by creating a basic measure to calculate the total sales from the orders table. So, over to the right in the fields pane, I'm going to right-click on "orders" and choose "new measure". We're going to name the measure, so I'm double-clicking the word "measure" in the title bar, and I'm going to name it "Total Sales" and navigate to after the equal sign, and I'm using the CALCULATE function with a nested SUMX function here. So I'm going to start typing CALCULATE, and I'm going to tab it in from the list, and then I'm going to start typing SUMX, and I'm going to highlight SUMX and tab that in as well. So the first argument for SUMX is the table. I'm going to start typing "orders", and when I see the table on the list, I'm going to tab it in. I'm going to type the comma to separate arguments, and the expression is going to be on the orders[sales] field, so I'm

Going to start typing orders again, and I'm going to just down arrow until order sales is highlighted. I'm going to tab that in, and I'm going to press enter. At this point, if there was an error, you would have red highlight up here, and it would be letting you know.

Again with a measure, it won't show until you use it on a visualization, but if you look in your Fields list, you'll see the total sales measure. We created that measure so we can use it in another measure that we're going to create, where we're going to also use a variable. Now we're going to create another measure in the orders table that includes variables. So I'm going to right-click on orders again and select new measure. In the formula bar, I'm going to name this one "2010 total corporate sales". 2010 total corporate sales. And I'm going to position myself after the equal sign. We're going to go ahead and press Shift+Enter so we get another line. This is going to be a multi-line function, so you don't ever want to be in a position where you have to read a long line from left to right, especially when you're troubleshooting.

We're going to use the VAR keyword to start our variable declaration; that's where you name it and tell it what it's going to contain. So I'm going to type VAR. VAR is a keyword; it will not show up on the function list. They have variant functions that show up on the list; we just want VAR plain. So we're going to do Shift+Enter afterwards to come down to the next line. We're going to name this variable "corporate sales". I'm going to type Capital C corporate, no space in between, capital S Sales, and an equal sign. So this is where we use a nested function. What we want is we want all of the corporate sales right now from the orders table. The type of sale is in the customer segment, so we're going to use the filter function. Go ahead and grab that with a nested ALL function. Grab the ALL function, and we're going to refer to the orders table customer segment field. So when I start typing orders and I see orders customer segment on the list, I'm going to highlight it and tab it in, and we're going to do a closing parenthesis. So that orders customer segment is the table name or column name for the ALL function. We're going to do a closing parenthesis to return to the filter function, and we're going to type a comma to get to the filter expression argument. So this is where we're going to tell it to filter only for corporate customer segment. So we're going to reference orders customer segment field again, and we're going to type in equal sign and then double quotes corporate because it's a text field; it has to be in double quotes. And we're going to do a closing parenthesis. Shift+Enter to come down to line four.

So right now we declared a variable called corporate sales where it's going to only bring us from the orders table all of the sales for the customer segment known as corporate. Now we want to declare another variable. In this variable, we'll tell it to only use 2010 dates. So we're going to do our VAR keyword again and Shift+Enter. We're going to name this variable capital I included capital D dates, and we need an equal sign afterwards. We'll go ahead and go down to the next row, so I'm Shift+Entering again, and this is going to be another filter ALL. So I'm going to start using the filter function, and then I'm going to bring in the ALL function. This time we're using our dates table year field. So I see it on the list; I'm going to grab it, date year, and I'm going to do a closing paren to come out of the ALL function, a comma to advance to the filter expression argument for the filter function, and we're going to reference the dates year field again, and this time we're going to type equal 2010 and a closing parenthesis. Shift+Enter.

So so far we've declared two variables and told it what the variables will contain, and now the other part of declaring a variable is telling it what to return. So what do we want our end result to be? We're going to use the keyword RETURN and Shift+Enter. And we're going to use the CALCULATE function here, and we want to calculate the total sales measure that we created earlier. So since it's a measure, it will show up. If you type an open square bracket, you'll see your measures that are in the orders table, and we're going to grab total sales measure. We're going to type a comma, Shift+Enter, and now we're going to call those two variables. So we're saying calculate total sales, but we only want them for the customer segment corporate, and we only want them for 2010. So all we have to do is type the name of the variable. So corporate sales is the first one. Notice the icon in front of it in the list; that's the XY icon; indicates it is a variable, a named variable. I'm going to tab it in, type a comma, and Shift+Enter, and I'm going to start typing included dates, which is our second variable, and I'm going to grab that and get it in there and a closing paren.

Now at this point, the RETURN is what's really happening here; it's going to calculate total sales, but it's going to be filtered for the corporate customer segment and for the year 2010 order dates. So when you do something like this, or any DAX calculation really, you might want to get in the habit of putting comments in that explain what the calculation is doing. If the file is being shared with other people, they'll be able to see your comments, and even you, your future self, like six months down the line, you might look at this and say, "What did I do?" Comments will be helpful then as well. So we're going to do Shift+Enter one more time, and we're going to type two forward slashes. Notice those slashes turn green; that indicates that what comes afterwards is a comment. When this calculation happens in this measure, when we add it to a visual, it won't try to calculate comment lines. So we're going to type, "This calculation is only for 2010 corporate sales." If you wanted to be more detailed, you'd explain the variables here and what they're representing. We're going to go ahead and press Enter, and we shouldn't get any error messages for this. And in your Fields pane, you'll see that 2010 total corporate sales measure. The variables we declared in the 2010 total corporate sales measure are only available in that measure; they're scoped to that measure. If we want to to create a similar thing but for 2011 total corporate sales, we would have to recreate the variables. Instead of doing that, it's more efficient to just copy the function for 2010 corporate sales. So I'm going to go up to the formula bar and just select everything, including the comments, and I'm going to do Ctrl+C to put it on the clipboard. I'm going to right-click on the orders table again in the Fields Pane and select new measure, and I'm going to use Ctrl+V as in Victor to paste it. Now I just have to update it. So I'm going to start with the name of it; I'm going to call it "2011 total corporate sales". The corporate sales variable is fine; the included dates variable needs to be updated to 2011, and the comment should be updated as well, and then I'm going to press Enter. So now I have two measures, one for 2010, one for 2011. I'd also like the same thing for 2012 and 2013, so I'm going to give you the opportunity to create those on your own. When you're done, you should have all four of those total corporate sales measures in your Fields Pane.

And now we're going to use a multi-card visual to see all of the numbers. So you're still in report View, and what you're going to do is you're going to go to your visualizations Pane, and you're going to look for the multi-roll card visualization and click it. And I'm going to just expand the width of the visualization, and then in the fields pane, I'm going to click and hold on 2010 total corporate sales and drag it into the card visualization. Do the same with 2011, 2012, 2013, and now you have a card showing the differences in sales for that corporate customer segment over the four years. Go ahead and save your file.

The last lesson in this module is other optimization techniques. So in the next module, we'll start getting into Power BI reports, and we'll go over these optimization techniques specifically geared toward your visualizations when we get into the next module. But for right now, one of the things you can do to optimize your reports is apply the most restrictive filters to them. Instead of having one visualization trying to show everything, you might want to break them down by filtering. You also want to limit the amount of visuals on any one report page. I know that everybody's into grouping them together, and that's fine, unless it becomes a performance issue for you. And there are custom visuals that you can gain access to, and you want to evaluate how they perform. If they're not performing well, then you probably don't want to use them. When it comes to optimizing the environment, the three things listed on the slide are things that typically the IT department will be involved with. There is more detailed information about capacity settings, Gateway sizing, and network latency in the Word document, website links, and additional information in the video description.

To recap this module, we started by using direct query for enhanced performance. Up until then, we had been importing the data directly into Power BI, which creates a large file size if you have a huge data set. And when we use direct query, it directly connects to the data when it needs to build report visualizations instead of actually storing the data in a data model. And you saw the file size comparison. We use variables and DAX functions to reduce complexity, so the result of the expression is stored in a variable upon declaration; it doesn't have to be recalculated each time it is used as it would without using a variable. So that could be another performance issue that's avoided if you're using variables. We reviewed a few other optimization techniques, and again, there's more detailed information about all of the lessons in the website links and additional info Word document in the video description.

Hi everyone, I'm Trish Connor Kato, and I'd like to welcome you to Microsoft Power BI. We've built some report visualizations along the way in this course, but Module 7 is going to take us on a deeper dive of the full capability of Power BI's reporting feature. You're going to be introduced to the fundamental concepts and principles of designing and building a report that includes selecting the correct visuals, designing a page layout, and applying basic but critical functionality. The important topic of designing for accessibility is also covered in this module. We'll be using the retail sample analysis desktop file we've been working in and a histogram Excel file that is in the video description below. We have multiple lessons in this module, as you can see on the slide, everything from designing and creating a report through accessibility. Take a few moments and review the lesson topics and then switch to your retail analysis sample file.

There are many methods used to design a report. Some people like to draw out a report on a piece of paper and design from that. Others like to look at previously created reports, maybe by other people that you see, and you use those as a basis for your design. In the retail analysis sample file, let's go to the overview page. So maybe you're looking at this, and someone else created this report, and you see a couple of things on here that you would like to design. You can use these as a starting point for your visualization design. Other people like to design from scratch. There's no right or wrong answer; you can design from whatever perspective you want to design from. We'll revisit these already created report pages later in this module for some tips and tricks on how to recreate some of these visualizations. For now, we're going to start a new page by clicking the plus sign to the right of your page tabs, and we're going to create a pie chart visualization. In the visualizations pane, you're going to locate and select the pie chart visualization, and it puts the framework of the visualization on the canvas. I'm going to go ahead and resize the framework so it's about the width of the canvas; that's not a necessary step; it's just something I choose to do now. And in this pie chart, we want to show Regular sales units and markdown sales units. So in the fields paying to the right, you're going to expand the sales table, and you're going to identify the regular sales units field. And again, if you want to see more of the field name, you can expand the fields table by going in the border between it and visualizations and clicking and dragging. So we're looking for the field in the sales table that's called regular sales units, and I'm going to just drag it into the framework of my pie chart. That's one way of getting that field in there. Now we want to look for markdown sales units field, and this time what I'm going to do is I'm going to check the box in front of it in the fields pane; that's another way of getting it into the pie chart. Let's go ahead and save our file, and then we'll get into some formatting of this pie chart that we just created.

The first thing I want to draw your attention to is if you hover your mouse over any of the pie slices, you'll see the value and percentage associated with that slice; that is known as a tooltip. Whatever fields you use in the visualization will automatically show up in the tooltips, but you can add other fields, other pertinent information you'd like to see when you hover over a slice of the pie. If you'll notice in the visualizations pane, you're in what's called right now the Fields well; those two boxes with the yellow underline; if you hover over it, it will say Fields. For a pie chart, you get Legend, details, values, and Tooltips Fields. Depending on the visualization type, that will change in the visualizations pane. What we want to do is over in the fields pane; we want to find two different fields, and we're going to add them to Tooltips. Again, the fields that you're using in the visualization automatically show up as tooltips when you hover, although they don't show in the tooltips field in the fields pane. Find regular sales dollars in the sales table and drag that field to the tooltips box. Also find markdown sales dollars and drag it into the tooltips box, and you can drag it underneath regular sales dollars. Now when you hover over a slice, you'll see the markdown sales units; that's the red slice I'm hovering over; that is the field that we used in the visualization, and you'll also now see regular sales dollars and markdown sales dollars because we added those fields to Tooltips.

Now we want to apply some formatting to our pie chart. Specifically, we'll have a conversation about whether we want a legend on the chart or the detail labels that are currently showing where it says markdown sales units and regular sales units on the pie chart. We also want to change the title of the chart so it's more succinct. We want to give the chart a background color and a shadow effect. In order to do all of those things, we need to get out of the Fields well in the visualizations pane and go to the Format well. The Format well is represented by the paint roller. So you'll notice the options that are available here in the Format well for a pie chart visualization. Different types of visualizations also come with some same and different formatting options. So right now we don't have the legend on on the chart, so where it says Legend, you can toggle off to on on the switch, and now you'll see underneath the title in the left corner of your chart is the legend. When I have a legend on my pie chart, I often find a detail label to be redundant, so I decide I want to just go with the legend. And there's another format option for detail labels; I'm going to turn that off by using the toggle switch. I'm going to expand the title format option by clicking the down arrow, and I want to change what the title text says. So what I'm going to do is I'm going to click in title text, and I'm going to do Ctrl+A to select everything that's in there, and I'm going to just type "Regular sales and markdown sales units," thank you. And as you're typing, it's updating the title on the chart. You have other title formatting options. I decide that I want the chart title to be center aligned, so I'm going to click on the second alignment button, and you'll see that it instantaneously happens on the pie chart. Now I'm going to collapse the title format category, and I'm going to expand the background category and enable it by using the off slider so that it turns the feature on. I'm going to do the color drop-down and select a comparable color for the background of my pie chart visualization. The last thing we're going to do with the pie chart formatting is apply a shadow effect to it for more visual appeal. So I'm going to expand the shadow format category, enable it, and I'm going to do a slightly darker color for the shadow, and you'll notice there is a shadow effect on the bottom and the right side of the visualization. We should probably rename the page from Page 1; that's not a very descriptive report page name. So I'm going to double-click on the Page 1 tab, and I'm going to just type "reg" for regular versus markdown and press Enter. Go ahead and save your file.

When you deselect your report by clicking on a blank area of the canvas and you go back to the Format well by clicking the paint roller, you'll notice that you have different information than we had for the pie chart visualization. For example, if you wanted to change the size of the report page, you could expand page size, go to the drop-down next to type, you can choose something off the list or custom and put in your own size. So depending on whether a visualization is selected, the Format well will change, and when you have multiple visualizations on a report page, it's important that you have the right one selected before you go to the Format well. We're going to create a clustered column chart on a different page now, so go ahead and click the plus sign so we can generate a new report page. And in your visualizations pane, you want to find the clustered column chart and click on the icon. I'm going to expand the size of the chart; you can do this before or after or both; doesn't matter. And we want to use information that is also in the sales table. We want to see this year's sales, last year's sales, the total sales variance, and we want to see it by Chain. So what I'm going to do is in the sales table in the fields pane, I'm going to expand this year's sales if it's not expanded, do the arrow in front of it, and I'm going to check the box in front of value. We want this year's sales value. Then I'm going to go and check the box in front of last year's sales, and it adds that into the clustered column chart framework as well. Go ahead and check the box in front of total sales variance, and that gets added to the chart. Now the Chain resides in a different table, so expand your store table in the fields pane, and you can check the box in front of Chain or you can click and hold and drag Chain to the axis box in the visualizations pane. At this point, we decide we'd like to see the territories as well in our visualization. In the visualizations pane, we have a small multiples fields, which we did not have with the pie chart visualization, and the territory resides in the store table. So in that table, I'm going to grab territory and drag it into the small multiples box, and I'm going to expand the size of my chart. Notice it now has a scroll bar in it. In order to see all of the data, you would have to scroll, and we decide we don't want to have to scroll in this chart, so we're not going to put territory in small multiples. We decide that we're going to add it as a filter. In your small multiples box to the right of Territory, go ahead and click the X to remove it. In Power BI, you can filter a report visualization by a field that you are not using in the visualization. What we're going to do is if your filters pane is collapsed, go ahead and expand it, and in the fields pane, right-click on the territory field in the store

Table: Hover over "Add to filters," and you'll notice there are three different choices there. Visual level filters will only apply to the visualization that's selected; in our case, the clustered column chart. Page level filters will apply to all visualizations on a single report page, and Report level filters apply to all visualizations on all report pages. We're going to select visual level filters, so we only want it to apply to this particular visualization.

In your filters pane, it already has Chain, Last Year's Sales, This Year's Sales, and Total Sales Variance. They're all set to show everything ("All"), and those are the fields that we're using on the visualization. We're not using Territory, but it allows us to filter by it. So in that filter box for Territory, I'm going to just check the box in front of Georgia, and you'll notice now that the visualization is only showing information for Georgia. I'm going to uncheck Georgia, and it's back to the way it was, where everything is showing all of the territories.

When we created our KPI in a previous module, we had to sort it before we turned it into a KPI. We decide that we want to sort our clustered column chart here; we want to sort it by This Year's Sales in ascending order. Sorting can be a two-step process. In the upper right corner of your visualization, you'll see the "more options" ellipsis button. You're going to click on that, hover over "Sort by," and click on "This Year's Sales." Then you're going to go back to the more options button and you're going to click on "Sort ascending." So now the visualization is sorted in ascending order by This Year's Sales.

We decide that we want to add data labels to this chart; that wasn't an option in the format well for the pie chart. So in your visualizations pane, we're going to go ahead and click on the paint roller to get into the format well, and you can enable data labels by clicking on the "off" slider, so they're on now. You can see the value of each column in our visualization; that's what a data label does. Take a few moments and go ahead and format this chart with the background color of your choice and a shadow effect.

We're going to name this report page "TY for this year, LY for last year and variance," and press Enter so it accepts that change. Go ahead and save your file. At this point, we decide that we're never really going to have to filter this particular visualization by Territory, so we're going to remove the Territory filter from the filters pane. Again, Territory is not a field that we're using on this visualization, so it will allow us to remove it from the filters pane. The ones that are on the visualization you cannot remove from the filters pane. If you hover over Territory's top title bar to the right, you'll see the X that will allow you to remove that filter.

We decide instead that we would like to be able to filter this visualization by Chain, as well as the visualization on the Reg versus Markdown report page and the KPI report page. We'd like to use a visual filter known as a slicer. So what I'm going to do is I'm going to make room for the slicer on this page. I'm going to just leave more space on the right side where I'll be able to pop the slicer visualization in. And since we're going to want to use the same slicer on two other report pages (KPI and Reg versus Markdown), go to each of those pages and leave about the same amount of space on the right side of those visualizations. When you're done making those adjustments, come back to your TY LY and variance page. Make sure your visualization is not selected; you can click in a blank space on the canvas to make sure it's deselected. And in your visualizations pane, you're going to find and select the slicer visualization icon; it looks like a table with a funnel on it, and it popped the slicer over on the right side of the screen. If it didn't place it there, you can move it over there.

And now what we're going to do in the Store table in the fields pane, we're going to just drag Chain over to the slicer. And we realize the slicer doesn't have to be that tall, so I'm going to resize it; it doesn't need to take up that much space. And I decide that I'm going to do a little bit of formatting to this slicer. With the slicer selected, I'm going to the format well in the visualizations pane, and I'm going to just enable a background color for the slicer, expand Background, and choose a complementary color. I'm also fond of putting borders around slicers, just so they stand out a little bit more on the page. So I've collapsed the Background format option, and I'm going to enable Border and expand it, and I'm going to leave the border black, but I'm going to make it about 30 pixels in radius so it stands out. And now when I click away from the slicer, you'll see the border around it.

Let's test the slicer on this page before we sync and make it visible on the other two pages. So in the slicer, I'm going to just check the box in front of Fashions Direct, and it filtered the visualization for that chain. I'm going to uncheck that box so I get both chains showing. Now, with your slicer still selected, you're going to go up to the View tab of the ribbon, and the last button on the View tab is "Sync slicers." You really don't have to sync slicers if you want a slicer just on one report page and only functional on one report page; you can do that. But in our case, we want to use the same slicer on multiple pages. When you click on "Sync slicers," you get a "Sync slicers" pane in between your filters and visualizations panes on the right side. It will show you all of the pages in this particular report file, and there are two columns. The column with the circular arrow that represents whether the slicer is going to be synced. So if it's on this page and I sync it with another page, that means that when I filter on this page, the other page will be filtered as well, and vice versa. The other column looks like an "i," and that's whether the slicer will be visible on the other pages. So notice the slicer is already visible; it's already checked; this is the page that we created it on, and we're also going to check the sync button after TYLY and variance, so it's synced and visible on this page. We're going to select both check boxes for the Reg versus Markdown page, and we're going to select both for the KPI page. Usually, when you sync it, a new update has it automatically checking that it's also visible, but if that's not happening, you'll have to click both check boxes.

So what does that do? At this point, we don't need the Sync slicers pane open, so to get rid of some of the clutter, I'm going to close that pane by using the X. And on the TY LY and variance page, I'm going to go ahead and use the slicer to filter for the Lindsay's chain. When I go to the Reg versus Markdown page, you'll notice the slicer is visible, and this page is also filtered for Lindsay's. On this page, uncheck Lindsay's and filter for Fashions Direct, and when you go to your KPI page, you'll notice the numbers have changed as it's only filtered for Fashions Direct. If I go back to the TY LY and variance page, that is also filtered for Fashions Direct. So I'm going to clear the filter by just unchecking Fashions Direct in the slicer, and now if you look at the other two pages, they're showing all of the data, not just by a particular chain.

In this next lesson, we're going to focus on creating a drill-through page with drill-through and Power BI reports. You can create a page in your report that focuses on a specific entity, such as a category, store, or territory. When your report readers use drill-through, they right-click a data point in other report pages and drill through to the focus page to get details that are filtered to that context. You'll see this play out in this lesson. We're going to put our drill-through report on a separate page, so I'm going to go ahead and click the plus sign to get a new page, and I'm going to double-click "Page 1" and name the page "Drill Through." Remember to press Enter after you finish typing in the name so it accepts the name. For this type of report that we're going to build, we're going to use the multi-row card visualization. In your visualizations pane, locate the multi-row card icon and click it.

We're going to use three fields from the Sales table to populate this card. The first field we're going to use is Last Year's Sales, so I'm going to just click and hold and drag it over into the card framework. I'm going to grab the Value field from This Year's Sales and drag it over. Thank you. And the last field is we want Total Sales VAR Percent. Once I drag that into the card, I'm going to widen the card so it only displays one row, and I don't need it to be that tall, so I'm going to make it less tall by using the sizing handles at the bottom of the visualization pane. Regardless of the type of visualization that you select, you have a drill-through section. We're going to use that section now to add a field as a drill-through field, and then you'll see what happens with our visualization. We want to use the Category field from the Item table as a drill-through field, so I'm going to locate it in the fields pane. I'm going to click and hold and drag it to the box that says "Add drill-through fields here" at the bottom of the visualizations pane. The Category field displays like a filter in the drill-through area. Also on your multi-row card, you have a back button; that's a navigation button that you can use to go back to whatever report page you perform the drill-through on. Let's go ahead and save our file. We'll circle back to formatting the multi-row card, but right now we want to proceed so you can see the functionality of using drill-through.

We're going to create a stacked bar chart on a new page. Go ahead and create a new page and name that page "Sales by Category." In your visualizations pane, the stacked bar chart is typically the first icon. Go ahead and locate it and click on it to put the framework on the canvas. From the Item table, we're going to drag Category to the Axis box in the visualizations pane, and we're going to drag from the Sales table This Year's Sales Value to the Values box in the visualizations pane, and we have our stacked bar chart. Now I'm going to make the bar chart just a little bit wider, and we'll come back and format it later. And I'm going to click away from it because we want to add two slicers to this page: one for Chain and one for Buyer. In your visualizations pane, locate your slicer icon and click on it, and I'm going to just move the slicer over to the right side of the canvas. It doesn't need to be as wide as it is, and you can size yours accordingly. Thank you. So I'm going to just pop it over here on the right side, and you'll notice the red dashed lines; those are like your guides when you're trying to align things. So when the line is on the bottom, it means it's aligned with the bottom of the visualization, and I'm going to go to the Store table and drag Chain into the slicer. And we decide that we want this slicer, instead of having everything displayed like it is now with the two check boxes, we want it to be a drop-down. To the right of Chain, you'll see the drop-down arrow; it says "Select the type of slicer," and we're going to choose "Drop down." So now when you do the drop-down, it defaults to "All," and you'll see the two choices. This is a particularly useful tip when you have a lot of entries in the slicer; instead of having to have a long, long slicer, you can just make it into a drop-down. We'll do more formatting on the slicer as well a little bit later. Right now, I'm going to click on a blank area of the canvas, and I'm going to grab the slicer icon again. I'm going to size it down, and I'm going to move it over to the right of the Chain slicer, and this time in the Item table, I'm going to drag Buyer into the new slicer. Make that one a drop-down as well. And for right now, I'm going to just move the Buyer one down a little bit. We're going to go ahead and save our file, and then we'll explore how to use drill-through.

Let's go back to the Drill Through page and take a look. Last Year's Sales: 23 million and change. We can navigate back to Sales by Category if we right-click on any of these data points. So we have Men's Shoes; these are the different categories. Now that we have a drill-through page, we'll see drill-through on the right-click menu. So what I'm going to do—and it even tells you that when you hover over a category it says "Right-click to drill through"—I'm going to hover over Shoes and right-click on it, hover over "Drill through," and it shows the target page. I'm going to click on "Drill through," and now when I look at this data, it's only representing the Shoe category. If you look at the bottom of the visualization pane, you'll see it's as if you filtered it for Shoes, so the numbers reflect that. You can use the back button—the back arrow. If you hold down your Control key and click it, it will take you back to your source page. We're going to filter this report by a buyer, so I'm going to go to the drop-down next to "All" in the Buyer slicer and I'm going to check the box in front of Barrett Galvin. So now this report has updated; it's just showing his sales. I'm going to right-click on the Women's category, hover over "Drill through," and click on "Drill through," and now we'll see Last Year's Sales are blank, and we have This Year's Sales. So Galvin didn't sell anything last year; he's not responsible for, as a buyer, of buying anything that sold last year. He may have just started with the company this year, but I want to draw your attention to the drill-through section. Now when it was filtered by Category, which is the field we use as our drill-through field, it looks like a regular filter, and you can see now that it's filtered for Women's; that's what we right-clicked on. Underneath that, we have another filter, and this one is in italics; that's because it's not a drill-through field. We use the Buyer slicer on the source report, so it appears different in a drill-through section. We're going to use the back button to go back to our source report. We're going to do the Buyer slicer drop-down and uncheck Barrett Galvin so we get the full information back in this visualization.

Now we're going to spend a little bit of time formatting the Drill Through and Sales by Category reports. Let's start with the Drill Through page. Now, even though we cleared the Buyer slicer on the source page, Sales by Category, you'll notice that the data hasn't updated. You have to clear it on your drill-through report page as well. So in the bottom of the visualizations pane, we're going to clear the Women's category. Now you can either uncheck it or you can use the little eraser that says "Clear filter," and then underneath that we have the Buyer filter is still there, even though we cleared the Buyer slicer, and we're going to do the X to just remove that filter. So now we have our full number set back. Make sure your multi-row card visualization is selected, and we're going to go over to the paint roller in the visualizations pane to go back to the format well. We don't need data labels; we're seeing the numbers; it's a multi-row card, right? We don't want to put a title on the card; maybe we do want to give the card a border for some visual interest. So I'm going to toggle Border to on and expand the category, and I'm going to give it a border color like a bluish color, and I'm going to make the border 30-pixel radius. And when I click away from the card, I can see the effect; it's a little bit more visually appealing. I'm going to reselect the card and go back to the format well, and I decide I'm going to give the card a background color, so I'm going to toggle Background to on, expand the category, and I'm going to choose a lighter color, and I'm going to click away from the card. In terms of the arrow that comes when you do a drill-through page, you can actually swap that out for something else of your choice; it's actually a second component; it's kind of sort of on its own outside of the card. So if you click on the arrow, you'll notice that it selects just that; you'll see the sizing handles around that, and you can press Delete on your keyboard. And we're going to use another back arrow for our back button. So what we're going to want to do is go up to the Insert tab of the ribbon, and the last group is the Elements group. You can insert a text box on a visualization page, a variety of buttons. If you look at the buttons, you'll see the back button; that's the one that came as a default when we created this drill-through page, and they have other arrow buttons in here. We can use an image if it's stored on your computer. What we're going to use is shapes. So under Shapes, they have block arrows, and we're going to select the arrow—left block arrow—puts it in pretty big, so I'm going to make it really tiny, and I am going to move it so that it is right above my multi-row card. And with the black arrow still selected, you'll notice you have another pane; you have a Format Shape pane that shows up where the visualizations pane used to be, and in that pane you have an Action option. We're going to enable the Action option and then expand it, and you'll see that the default action is that it serves as a back button, just like the one that came when we did this visualization and made it a drill-through. You have other choices there; we're going to leave it on Back. And now I'm going to just click away from the arrow, hold down my Control key, and click on it, and it should take me back to the Sales by Category report page. Go ahead and save your file.

A few moments in the format well and give your stacked bar chart data labels, add a background color, and a shadow effect if you'd like. We want to apply the same formatting to both of our slicers, so there's a time-saving technique that you can use. I'm going to click at the very top of the Chain slicer, hold down my Control key, and click at the top of the Buyer slicer, so both of them are selected. In the format well, with both slicers already selected, I'm going to expand Selection controls and make sure Select all option is toggled to on. I'm going to collapse Selection controls, and I'm going to expand the Background and choose a background color that's complementary to the rest of the page. So see how it impacts both slicers at the same time. Because I chose a dark background color, I'm going to expand Items and change the item color—the font color for the items—to white so they show up a little bit better. I'm going to click on a blank area of the canvas to deselect both of those slicers, and I just need to resize my Buyer slicer so it's the same size as the Chain one for consistency. Go ahead and save your file.

Just like Excel, Power BI has a conditional formatting feature. Let's navigate to our TY LY and variance page, and the first thing we're going to do is resize the column chart visualization so it's not as tall as it is; we're going to want to put another visualization underneath it, so we're just making space for that. Conditional formatting in Power BI lends itself to the table visualization or the Matrix visualization. In a visualization pane, we're going to make sure that we don't have anything selected on our canvas, and we're going to select the table visualization icon. From the Sales table in the fields pane, we're going to want to add Last Year's Sales to the table, so I'm going to just drag it into the table framework, and we're also going to want to add This Year's Sales Value into the table, so we just have two columns in the table, and that's fine for what…

We're trying to do so. In order to do conditional formatting, you have to access the field that you want to conditionally format from the visualization's pane. So if you notice, we have last year's sales and this year's sales in the values box. We want to conditionally format this year's sales. So I'm going to right-click on this year's sales, hover over conditional formatting, and I'm going to click on background color.

So the background color for the specific field that we right-click on, this year's sale dialog box comes up on the screen. Take a moment and explore the format by drop down. We're going to leave it on color scale, but you do have other options there. And also take a look at the apply to drop down that's defaulting to values only, and we're going to leave it like that. It tells you it's based on this field, and that's also a drop down, so you could search for other fields from in here if you happen to right-click on the wrong field. We have a minimum and maximum area, and before we start filling in any numbers here, we're going to check the diverging check box.

So now we have minimum, Center, and maximum areas to fill out. The scenario we're using here is we want to say that we have a tolerance for what this year's sales should be. We're comfortable with a minimum of 20 million. We would like to have a maximum of 25 million, and we're kind of okay in the center if the center is 23 million five hundred thousand dollars. So we're going to start putting those numbers in boxes. Under minimum, where it says enter a value, you're going to put in your 20 million. For the center enter a value box, it's going to be 23.5 million, and the maximum is going to be 25 million.

Before we click OK, let's take a look at this year's sales value: 22 million 51,952. So it's not at the minimum, certainly not at the maximum; it's a little bit below Center. So when you're diverging, there is a color scale there. If we were directly at Center, it would be this yellowish color, but we're a little bit less than Center. So go ahead and click OK and look at the color that it used to highlight this year's sales. Now keep in mind, if our data changes—as we add more sales for this year to our data set—that color on this year's sales will change accordingly as well. That's an example of a conditional format.

Another useful efficiency technique is using the bookmarks feature in your Power BI reports. Let's go to the sales by category page, and the first thing we have to do is let's go to where the drop down would be in the buyer slicer. And remember, we changed the item color, the font color to white, so we're not able to see the items. I neglected to have you change the background of the items to a darker color. So I'm going to click on the Chain slicer to select it, hold down my control key, and click on the buyer slicer and go to the paint roller to get into the format. Well, I'm going to expand items and do the drop down next to background color, and I'm going to choose a dark color. If I click on a blank area of the canvas and go to the buyer all drop down, I'll be able to see all of the buyers' names clearly now because it's a contrasting background color. We're going to select Evangeline Bright.

So now our chart is filtered for just Evangeline Bright. Let's say that this report page you come to often, and you filter for different buyers. You can save your filtered report as a bookmark. To do that, you're going to go up to the View tab on the ribbon, in the show panes group, the last group, you're going to click on bookmarks, and it opens the panel to the left of your visualization pane. With the report filtered for Evangeline Bright the way it is, we're going to click Add at the top of the bookmarks pane, and it adds a bookmark; it calls it Bookmark 1. To the right of it, we're going to go to the more options ellipsis and we're going to choose Rename, and we'll just call it Evangeline Bright, the buyer's name, and press Enter. Now go ahead and close your bookmarks pane by using the X in the upper right-hand corner. Okay, access the buyer slicer drop down and choose Select All at the top. The next time that you want to see it filtered for Evangeline Bright, you don't actually have to do the filter; you just need to access the bookmark. We're going to go back to Bookmarks on the View tab, and in the bookmarks pane, you're going to click on Evangeline Bright, and it does the same filter for you. So imagine if you set up a bookmark for those buyers that you want to track frequently; you can have multiple bookmarks set up and access them anytime you need to. Go ahead and save your file, and you can close the bookmarks pane and do your buyer slicer drop down again and go back to Select All.

Our next lesson is about accessibility features in Power BI reports. There are two main categories of features: built-in accessibility features that require no configuration, and built-in accessibility features requiring configuration. The features that don't require configuration are keyboard navigation—a lot of users want to navigate using their keyboard through the reports—the screen reader compatibility. If you have high contrast colors set in Windows, those high contrast colors will come over into Power BI reports and be applied to your reports. You have Focus mode, so you can fill up the whole canvas and just focus on the visualization that you're looking at. And another one that requires no configuration is the ability to show a data table of the underlying data for that particular visualization. You'll get to see some of these as we go through this lesson.

We also have features that do require configuration. In this lesson, we'll go through alt text, which is text that a screen reader will read out to the user, making sure that the tab order is appropriate, especially for those users that want to navigate a report by using the keyboard. There are titles and labels that can be configured as well as markers, and you'll get to see some of the report themes. The first thing we're going to do is configure alt text for our bar chart on the sales by category page. So I've just selected the chart, and I'm going to go to the format well in the visualizations pane and expand General. If you scroll down, you'll see alt text at the bottom of the general category. So if you don't put in alt text, any screen reader will give a generic description of whatever object the end user selects, so it'll just give it a generic description. You want to give a more detailed description. So in that alt text box, you're going to say—you're going to actually type—This is a bar graph representing this year's sales broken down by category. So when the screen reader responds to this selected visualization, that is the text it will read.

Another accessibility feature that needs to be configured via a shortcut key combination is the ability to add a data table to a visualization. A data table shows the underlying data in a table format that's causing the visualization to be drawn. So with that same bar chart selected, the shortcut key combination for adding a data table is Alt+Shift+F11, and when you do it, you'll notice on the bottom half of the screen it has a table, a data table showing a listing of the categories and each category's sales for this year. That can be useful when someone needs to review a report. You'll notice at the top it has a Back to Report button, and when you click that button, it just gets rid of the data table and takes you back to the regular report page.

Another useful feature available for every report visualization is the ability to go into Focus mode. In the upper right-hand corner of the bar report, you'll see the middle button is Focus mode. I'm going to click on it, and this is similar to the type of screen you get when you put in a data table; it just doesn't have the data table. It makes sure that the visualization fills up the screen so you can direct your focus to that. We're going to click the Back to Report button again to get back to the report.

An equally important feature to consider is the tab order for those users that are going to be using the keyboard to navigate through the report. You want to make sure that the tab order is correct. Go ahead and press the Tab key on your keyboard, and it should select the bar graph. When you press Tab again, it should go to the Chain slicer, and when you press it again, it should go to the buyer slicer. Let's say for some reason you don't want it to land on the Chain slicer—maybe you don't want end users to be able to filter by Chain on this report—or if the tab order is out of order, you need to fix it. Both are found in the same place: on the View tab of the ribbon, in the show panes group, you're going to click on Selection, and it opens the selection pane. At the top, you can look at the layer order or tab order; click on Tab order. So we're seeing the tab order here; it's saying the first thing it's going to go to is the slicer that we're currently on, and then the second thing is the second slicer, and we want to change the order here. So to ensure that it goes to the bar graph first, I'm clicking on the number three, and I'm going to use the up arrow to move it to the first position. I'm going to click on the number two and say that should be moved to the last position. Now we'll leave that one second, and for the third one, we actually really don't want this one to be in the tab order; we'll leave it there for right now.

An equally important feature to consider is the tab order on a report page. I'm still on the sales by category page, and I'm going to press the Tab key on my keyboard, and you'll notice that it is selecting the bar chart, This Year Sale by Category bar chart. When I press Tab again, it goes over to the buyer slicer, and again it goes to the Chain slicer. So the tab order is not correct; it should flow. And for people that are accessing your report using their keyboard to navigate it, tab order becomes particularly important. So I'm going to show you where we would go to make sure the tab order is correct. It's going to be on the View tab of the ribbon, in that last group again; you're going to click Selection, and it opens the selection pane. At the top of the pane, it defaults to layer order, and to the right you're going to click on Tab order. So we want to make sure This Year's Sales by Category is in position number one, which it is. I'm going to click on number two slicer, and that's the buyer slicer, and I want that to be in the last position. So I'm going to go above the names of the objects, and I'm going to click the down arrow button to move that one down. Now when I test—test my tab order—I'm going to just click anywhere on the canvas of my report, press my Tab key, and it selects the bar chart; press it again, it selects Chain, and again it selects Buyer. I'm going to go ahead and close the selection pane and save my file.

The last accessibility feature we're going to cover in this module is the use of a theme, one in particular. So on the View tab, you have a gallery of themes, and we're going to access it by using the drop-down arrow to the right under Power BI. There is a theme, and when you hover over them, you'll see the screen tip showing you the names. On my screen, it's in the second row and it's second column under Power BI. There is a theme called Colorblind Safe. I'm going to go ahead and click on that theme and apply it. When you choose a theme, it supersedes any formatting that you've done on the report pages. So if you go to your drill-through page, you'll see that that has the same colorblind-friendly theme; other report pages has it as well. There are also themes available to you in the online service, and you'll see that in the next module.

Our next lesson is how to create a histogram visualization. The histogram visualization does not default to being in the visualization's pane; it's a visualization that you're going to have to add. So in order to do that, let's go over to our visualizations pane, and after the last visualization, you'll see the vertical ellipsis icon, and it says Get More Visuals. We're going to click on that, and then click on Get More Visuals. When you do this, it brings you into Power BI visuals, and it defaults to the App Source tab at the top of the dialog box. You also have a My Organization tab, so your Power BI admin can add visuals for the entire organization, and when that happens, you can access them from the My Organization tab. We're going to go back to the App Source tab. You'll notice they have categories of visualizations, and we can click in the search box and type histogram, and when you press Enter or click the search button, the magnifying glass, it will show you the histogram chart. There's a couple of different kinds in here from the AppSource store. We want the default histogram chart, so we're going to click the Add button on the right. Depending on your licensing, you may not have the ability to get more visuals; it will let you know that it imported this custom visual successfully, and I'm going to click OK on that dialog. So the histogram icon shows up underneath all of the other visualizations. If we were to open a new Power BI desktop file, the histogram visualization icon will not be there. If you wanted to retain it over different files, you need to pin it. So I'm going to right-click on that histogram icon and choose Pin to visualizations pane, and it will ultimately move it up so it's amongst all the other visualizations in that pane.

Now that we've pinned the histogram visualization icon to our visualizations pane, we're going to start a new Power BI desktop file and bring in information from an Excel file. I'm going to go to the File tab on the ribbon and choose New, so it will launch a new instance of Power BI desktop, and because you pinned the histogram visualization, it shows up in the new file as well. On your splash screen, we're going to click on Get Data, and when the dialog box opens on the right side, we're going to double-click Excel workbook, and we're going to use the Histogram Excel file that you grabbed from the video description earlier. I'm going to just double-click it and let it connect, and this file only has one sheet in it called Employee Salaries. I'm going to click the check mark, and you'll see it just has generic employee data and their monthly salaries in dollars. We're going to click the Load button at the bottom. This data is ideal for a histogram, and you'll see why in just a moment.

Okay, let's take a look at the data in Data View. So it just shows the generic employee identifiers and their monthly salary in dollars. The reason why this is good data for a histogram—because a histogram is a representation of data points into ranges—data points are grouped into ranges or bins is what they're called in a histogram, making the data more understandable. So what we're going to do is go back to Report View, and we have this blank page here. Let's go ahead and rename Page 1, so it's called Histogram. And the first thing we're going to do is create a table visualization using the Employee Salaries data. So in your visualizations pane, find your table icon and click on it. Expand your Employee Salaries table in the field pane, and we want to do the employee—drag the Employee field into the table—and then drag Monthly Salary in Dollars into the table. So just in a table format, we're able to see this information, and what's going to happen is when we put this information into a histogram, it's going to do groupings of employees known as bins based on their monthly salary. So for example, it may create a bin for the count of employees that are in the salary range of, say, 2,000 to 2,500; another bin would be 2,501 to 3,000, so on and so forth, and you'll see this play out right now. Go ahead and press Delete to delete the table visualization, and in your visualizations pane, you're going to click on your newly added histogram icon, and you can go ahead and expand the width and height if you'd like of the histogram framework. And if we look in the visualizations pane, you'll notice that we have a Values field and a Frequency field for the histogram. We're going to use the Monthly Salary field from Employee Salaries table as the Values field, and we're going to use the Employee as the Frequency field. And when you drag Employee into the Frequency field, it says First Employee. We want a count of employees, so we're going to do the drop-down arrow next to First Employee, and we're going to click on Count. So what you're looking at—I'm going to just make my histogram bigger—so the frequency is over on the left, and we're going to format that so it doesn't have any decimal points in a few moments. But what's happening is it created—these are your bins—so it did groupings of the salaries. So you have 2.0—2,000 through—that's the beginning of the first bin; the second bin begins with 2.5—one thousand—the third one is 3.0—zero thousand—so on and so forth. So when you hover over a bin, which is a column in the histogram, you can see the frequency. So that's saying there are 25 employees that fit into that first bin, and it gives you the range of the bin. That is what a histogram does. Now we want to format that axis on the left, which is known as the x-axis; the one on the bottom is known as the y-axis—to get rid of the decimal places. So I'm going to just go to the format well, and I'm going to expand x-axis, and where it says Decimal Places is 2, I'm going to change the 2 to a 0 and press Enter. And I did that on the wrong axis, so I'm going to change that back to two decimal places. I do this all the time, and I'm going to collapse the x-axis and expand the y-axis. I always get them backwards, and change the 2 for decimal places to 0, and now you can see the change. For the frequency, doesn't need decimal places over there; it's a count. Go ahead, and we're going to close this file. If you want to keep it, you can save it and just call it Histogram. So I'm going to go ahead and do Save, and it'll prompt me and to give it a name, and I'm going to call it Histogram, and then I am going to close it. And you still have your Retail Analysis sample file open.

I mentioned earlier that another way to get great report ideas is by viewing reports that have been created by other people. We have lots of them in this particular file. Let's go to the Overview page, and if you see a report that someone else designed that you liked, you can select the report. So I'm going to select—let's see—we'll select the Total Sales Variance Percent report in the lower right-hand corner. And when I look in the visualizations pane, I'm going to go ahead and collapse the fields pane for now. When I look in the visualizations pane, I can see which visual was selected; in this case, a scatter chart. So if I wanted to recreate this, I know that I can by using a scatter chart visualization. I also have all of the fields that were used and where they were used. So we have District and Store Number in the details section; we have District in the legend, so on and so forth. So I can use that information to recreate this type of chart, and I can even use different fields when I recreate it. The easiest way of doing this is copying the visualization. When you copy a visualization, you can only copy it within Power BI Desktop, so from one report page to another; you can't copy it outside of the application. What we're going to do is we're going to right-click in a blank area of that visualization in the lower left, and we're going to hover over Copy and choose Copy visual. Let's do the plus sign to create a new page and click anywhere on the canvas, and you're going to do Ctrl+V as in Victor to paste. So now we have this visual on its own page, and we can go over to the…

Visualizations pane and swap out Fields if we want to. We're going to delete the page that we just pasted the visual on, so I'm right-clicking on page one. I'm choosing delete page. It will always confirm if you want a deletion; go ahead and confirm the deletion. Save your file.

And this extensive module, you learned how to design, enhance, configure, and format report visualizations; how to create and configure sync slicers; how to create a drill-through Page; apply conditional formatting on a field in a visualization; create and use bookmarks for efficiency; how to create a histogram; and we reviewed some accessibility options in the reporting module. Now that we've created and assembled our report Pages, we're ready to move on to module 8, which is creating dashboards.

Once you put your data together on a dashboard, it gives you the opportunity to give a compelling story about your data. You'll also learn about the features and functionality of dashboards and how to enhance them. We'll be using the sample Superstore OD data set that we published to the Power BI service in module 2, as well as the Microsoft Forms and Power Automate apps. In this module, you can access those apps by logging into your Microsoft account, going to the waffle in the upper left-hand corner, and you should see all the apps that you have access to.

We'll start this module by creating a dashboard by pinning visualizations to it in the Power BI service. We'll move on to real-time dashboards where the visualizations update in real time because we'll be accessing a real-time data set, and we'll also get to use a form and the Power Automate feature during that lesson. We'll go into enhancing a dashboard, which could mean adding a video to it, a text box, a theme. We're going to configure a dashboard tile alert, so if changes happen to that dashboard tile, you can be alerted, and we'll use a feature named Q&A for analysis to finish off this module. Dashboards are not created in the desktop; they're created in the service. As mentioned, they can contain report visualizations, videos, text boxes, audio files, and web content, including other dashboards or reports. You can share dashboards, have conversations about them, both in the service or in Teams. You can even subscribe yourself and others to email alerts regarding dashboards. You'll learn how to do all of those things during this module.

We'll start by navigating to the Power BI workspace where we publish the sample Superstore OD report and data set. We're going to get started by accessing the sample Superstore OD report that we published to the service in module two. First thing I want to point out here is the difference in icons: when we publish from the desktop to the service, it published both the report, which has a blue icon and it looks like a column chart on it, and it also published the data set, which has an orange icon and it looks like a database icon on it. We want to access the report, so Sample Superstore OD is a link, and we're going to click it, and it will navigate us to the report. So this was a simple report that we put together earlier, and we're going to edit it. We want to add three visualizations to it, and we can really get rid of the visualization that's on it. So once you have published to the service, if there are any changes to your reports or even if you want to create new ones, you can do it in the service, and the way that you do that is by coming up here to the menu and clicking the more options button, choose edit. This view should look very familiar to you; it's almost exactly the same as report View in Power BI Desktop. So you have your fields and visualizations and filters pane on the right.

Let's start by clicking on a blank area of the canvas, and we're going to want to add another visualization here, and so we're going to use the table visualization, find it in your visualizations pane, and we're going to expand the Orders table in the fields pane, and we want to add Customer Segment field to our table. The other field that we want to add to the table is Sales. So in our table, we're seeing Customer Segment by Sales, and we can go ahead and resize that table; it doesn't need to be as big as it is to show that little amount of data. Yeah, before we add our other two visualizations, let's go ahead and select the bar chart that was already on this page and press Delete on your keyboard to get rid of it. I'm going to then move the table up to where the bar chart was, so it's in like the upper left-hand corner of the page, and I'm going to click on the blank area of the canvas again before I add my next visualization. Let's grab the Stacked bar chart visualization, and we're going to use the same two fields in it that we used in the table, so go ahead and check Customer Segment in the Fields Pane and check Sales. We're going to click on a blank area of the canvas, and the last visualization we're going to add is the card visualization, and we want to just have that show the total Sales. I'm going to move the card visualization so it's to the right of the table, and you can see the same red guidelines that are there as when you're in Power BI Desktop. These three visualizations are defaulting to interact with each other by filtering, so in the table, if I click on Consumer, you'll notice that the card updates to just show the sales for the Consumer segment and that the bar chart also updates. I'm going to just click on a blank area of the canvas, and now we want to save this report, and you can do that from the file drop down and just choose Save. We won't worry about any formatting on this report right now; the greater picture is to get it onto a dashboard.

Now we're ready to create a dashboard by pinning this report to it. On the task pane going across the top of your screen, you'll notice there's an icon that says Pin to a dashboard. We're going to go ahead and click that icon, and it opens the Pin to the dashboard box. If you have existing dashboards in here, it will list them in alphabetical order. We're going to create a new dashboard, and we're going to name it Sample Superstore, and then we're going to click the Pin live button. And it tells you Pin live page enables changes to reports to appear in the dashboard tile when the page is refreshed, so we're going to go ahead and click on Pin live, and it tells us it pinned it to a dashboard, and if you catch that pop-up quick enough, you can go to the dashboard from that pop-up. If you don't catch it quick enough, you can get to the dashboard by going back to your workspace. There's a couple of ways you can get back from your report to the workspace: you can come right up here at the top in the title bar and click on your workspace, or on the left side, the next to the last button will take you to your workspace; either way is fine. When you get back to your workspace, you'll notice that now for Sample Superstore, we have we still have for OD to report in the data set, but now we also have a Sample Superstore dashboard, and just take note of the different icon; so this one is like a greenish icon, it looks like it has a gauge inside of it. To get to the dashboard, we're going to just click on the link Sample Superstore, and now you're seeing your dashboard. After we create our second dashboard, you'll learn how to enhance dashboards. In the meantime, let's go back to our workspace where the dashboard resides, and I'd like you to go back to the Sample Superstore OD report because we were in edit mode when we pinned it to the dashboard. We have that huge set of options going across the top. What if you want to not edit a report and just add it to a dashboard? I figured I'd show you how that process would work, so that's when you would just use more options up here, and you would have the Pin to a dashboard Choice. We've already pinned this report to a dashboard, so we don't have to go through that step; just wanted to show you how to find it when you're not in edit mode in report View.

In this next lesson, we're going to create a dashboard based on streaming real-time data. Your dashboards will update in real time when you use this method, and the data can be from various sources. We create these real-time data sets in the service as you'll see play out in this lesson. When you're using real-time data, there are three primary types of real-time data sets that you can use in the service: there are push data sets, streaming data sets, and PubNub data sets. Let's take a look at the differences between the three of them in terms of the capability of what they can do. We're going to end up using a push data set for our next example, and also just a reminder, this PowerPoint presentation is in the video description, so you'll be able to refer to it in the future. So the push data set—all of them update in real time as the data is pushed in—you'll notice that the push method allows data to be stored permanently in Power BI for historic analysis. When it's streaming, the data is only temporarily stored for an hour to render visuals, and PubNub doesn't store any data at all. Because the data is stored permanently for the push method, you can use your Power BI reports atop the data. So we'll be using the real-time push data set because it creates a database which maintains history. We can use the Power BI visualizations on that data; it's still be real time in your dashboard, so no need to refresh. There's a difference here: you cannot Pin live Pages like we did in our previous example; we would have to pin each individual visual to the dashboard in order for it to be effective and refresh in real time. It's counter-intuitive, pinning live and real-time refresh, so we won't be able to use the Pin live feature that we just saw, but you'll see how this plays out.

We're going to start this lesson by creating a form in Microsoft Forms that will be used to push the data into the data set and create the database for us. So I've signed into my Microsoft account, and I'm in Microsoft Forms. If you need to get yourself into Microsoft Forms, you can use the Microsoft waffle in the upper left-hand corner of your screen, and that will give you access to all of your apps. When you click on the waffle, you'll be able to see Forms in the list. If you don't see it in the list, you want to click on All apps, and then you should be able to find it in your list of apps. Go ahead and launch Microsoft Forms, and your screen will look similar to mine. We're going to create a short three-question form that will be used as the vehicle to push data into the data set we're going to create. In the upper left corner, go ahead and click directly on New form. It may ask you to pick an account; sometimes it even asks you to sign in. Where it says Untitled form, we're going to click, and we're going to just call this Streaming Survey. We don't need to enter a description; it is optional. We're going to go ahead and click the Add new button to add our first question, and the type of response is going to be a choice, so we're going to click on Choice. The question is, "Which BI technology are you most interested in learning about?" So I'm going to just type that in next to the number one: "Which BI technology are you most interested in learning about?" and I'll give it a question mark at the end. We're going to click where it says Option one, and the first option is Power Apps; Option two is Power Automate; and we're going to add another option using the Add option button, and that one is going to be Power BI. To the right of the Add option button, we're going to add the other option, so it gives an opportunity to select Other and put in a choice that's not on our list. We only want them to choose one answer, so we're not going to toggle Multiple answers on, and we're not going to make this question required. Instead, we're going to add our second question, so click your Add new button, and the second question will also be a choice, so click on Choice. This question is, "What is your current experience level on the technology you selected?" "What is your current experience level on the technology you selected?" We're going to click on Option one, but if you notice right above Option one, it gives us some suggested options, and in that list of suggested options, we're going to click on Beginner. For Option two, we're going to select the preset Intermediate, and we're going to add an option again, so we have three options, and this one we want to be Expert, and there's a preset for that there as well. Again, we only want one answer; it's not a required question. We're going to go ahead and Add new to get our third question, and I got this pop-up every time something is updating; they let you know. I'm going to just get rid of it by doing Got it; it has nothing to do with what we're doing right now, so I'm going to go back to Add new, and this one is going to be a text type response. The question here is, "Where are you from?" and we want to be helpful and give them information about the format we want the answer to be in, so after the question in parentheses, I'm going to type, "Provide answer in City, ST for State format," and I'm going to give an example: for example, I'll put in San Francisco, CA, and close my parentheses. And so those are the three questions that we want on this form. The responses to these questions will be pushed into our database in the cloud. If you'll notice, since this is online, the form has already been saved. I can click back on Forms in the upper left corner, and I'll see my Streaming Survey form.

We're going to navigate back to our workspace so that we can start creating our streaming data set. When we switch back over to the service, we're still seeing the Sample Superstore OD report; in my case, it's in my Power BI video workspace, and I decide I want to put our streaming data in a new workspace. On the left side of your screen, the Workspaces icon looks like a stack of papers, and that's how you can start the process of creating a new workspace. I'm going to go ahead and click that icon, and at the bottom, I'm going to choose Create a workspace. So the panel opens up on the right to create the workspace. We're going to name the workspace Streaming Data Set, so you can just type Streaming Data Set in there, and for the description, "Contains all components of our streaming data." If you wanted to learn more about workspace settings, you could click this link; it would open a new browser tab. We're going to go ahead and click Save at the bottom, and you'll notice you are in your new Streaming Data Set. Now we just have to create the data set, so we have the workspace that's empty at this point. We are going to create the data set by using the New button on the left side of the screen, and on that list of things, you're going to select Streaming data set, and the panel on the right opens up. We talked about the three methods of getting streaming data, real-time streaming data into Power BI. API is the method we're going to use; that's for the push data set; streaming would be Azure Stream; and then we have PubNub. We're going to leave it on API and click on Next. We're going to name the data set Streaming Data Set, just to be consistent; they'll have different icons, so you'll be able to tell, and it tells you whether it's a report versus a data set, so on and so forth. For the values from the Stream, we're going to leave all of them defaulting to Text, but go ahead and do the drop down next to Texting; you can see it could be Text, Number, or Date/Time. Our first value, we're using three values from the survey form that we created, so our first value is going to be Technology that was referenced in the first question, so we're just going to call this value Technology, and again we're going to leave it on Text. It's putting it in; it's showing you some of the behind-the-way coding; it's a range, so we're naming it Technology, and it's going to pull whatever value is put in the response for that question from the form. Ultimately, that data is going to be pushed into this data set. Our next value that we're going to use here is going to be Experience, and the third value is going to be Location, and you can just see that it gives you another space for a new value. We can ignore that; it expanded the range: Technology, Experience, Location; and underneath that, you'll see it's Historic data analysis; by default, it is off. We want to toggle that on, so it creates the database, retains all of the data from all of the responses from that Streaming Survey form that we created. Go ahead and click the Create button. It lets you know that your streaming data set has been created; it gives you the schema for it; right, you're looking at the raw data here, and it gives you a push URL. We're going to just go ahead and click Done in the lower right-hand corner.

Even though we don't have any data in our streaming data set yet, we're going to go ahead and create our reports so that when we do fill out our form and push data into the data set, the report visualizations will automatically populate. To do that, I'm going to use the more options button to the right of the data set, and I'm going to click on Create report, and it opens up the reporting interface that you're used to. We saw this when we edited in the previous lesson, and we saw this when we were working in Power BI Desktop. Notice in your Fields pane, it just says Real-time data, and we're going to expand that table. So we're going to create a few visualizations here, and they'll populate when we push data in to the data set. So the first visualization, we're going to grab the table visualization icon, and we're going to just add Technology to the table as a field, and then we'll add—let's see—we'll add Technology. We'll add after Technology; let's go ahead and do Experience and Location in the table, and then we're going to click on a blank area of the canvas, and we're going to use a map visualization, and your visualizations pane, the map visualization looks like a globe. Go ahead and select that, and we're going to use Location field from the fields pane as the location. We're going to use Experience as the legend for the map visualization, and we're going to use Technology—actually, let's flip that; let's use the X to get rid of Experience in the legend, and let's put Technology in the legend, and then for Experience, we're going to put a count of Experiences, and that goes in the Size box for the visualization. So I'm going to make that map visualization bigger. Again, it's not showing any of our data because we don't have any data in our data set, and maybe what I'll do is I'll move the map to the right side of the table and then size it from there. I'm going to click on a blank area of the canvas again, and this time we're going to add a stacked column chart, so I guess that's the second icon in the visualizations pane, and we're going to use—for the axis, we'll grab Technology, and we'll use Experience for the legend, and again we're not seeing anything in these visualizations because we don't have any data yet. We're going to add one more visualization, and we're going to use the card visualization for that, so make sure you're on a blank area of your canvas, click on the card, and for the field, we're going to use Technology, and notice how when you put it into the fields box, it says First Technology. We're going to do the drop-down arrow next to First Technology and choose Count distinct, so how many responses are there for that technology. Now we need to save this report, so you're going to go up to File and you're going to choose Save, and it's going to want you to name it; we're going to just call it Streaming and click Save. So I mentioned earlier, when you're using streaming data, you can't use the Pin live experience for dashboards because it's counter-intuitive; it's real-time streaming data, which means your dashboards will automatically update as new data is pushed into the data set, so what we need to do to get these

Onto a dashboard is pen them each individually. If I hover over my empty technology experience location table, you'll see the controls for that. The first control is pin visual; go ahead and click it. Notice it shows a little snapshot of the data. In our case, we don't have any data yet; it defaults to a new dashboard because we are in a new workspace that doesn't have any dashboards. We're going to call this dashboard "Streaming Dashboard". Notice the pen button does not say "pen live"; it knows that it's streaming, so it's not going to offer you that option. We're going to go ahead and click pen. We don't have to go to the dashboard yet; you can just close that pop-up if you'd like, or it'll disappear in a moment. Then I'm going to hover over the map and find its control buttons, which are showing up underneath. I'm going to grab the pen visual, and now it's on an existing dashboard, and it's the one that we have in this workspace, so we're going to just click pin. I'm going to hover over the technology and experience chart and use the PIN, and I'm going to do the same for the card visualization and use the pen. So now our setup is done in terms of: we have our dashboard; we've already pinned our visualizations to it; we created our data set, but it is not populated.

Before we begin the process of populating our data set, let's switch over to our workspace, so the streaming data set workspace, and let's open up the dashboard that we created. We want to make sure everything is organized on the dashboard accordingly. So you'll notice when we were looking at the report, the card for the count of Technology was over in this area; we want to move it so that we don't have to scroll to get to it on the dashboard. So to move a visualization on your dashboard, you want to look for its more options button, and you can click and hold that and drag it to a new location. Notice that as you drag it, it might cause other dashboard tiles to move; that's because they're taking up the space. So you'll notice there's a gray box that pops up and says, "Hey, that's an empty space; it might be great for this tile." So once I've done that, now what we need to do is start the process to populate our data set. We're going to use Power Automate to create a flow. Flow is kind of short for workflow. The flow that we will create will tell the system to grab all the responses from our survey form that we created and push them into the data set.

So we're going to go access Power Automate. You can use, if you want to stay in Power BI, that's fine, but you can go to the Microsoft waffle and you'll see all of the apps. If you want to open Power Automate in a new window, you can use the vertical ellipsis and say "open a new tab". So go ahead and get yourself into Power Automate; it should be the same login you're using for Power BI. When you access Power Automate, you have the option to start a flow from a template, or you can start from a blank. We're actually going to start from a blank. So on the left side, you have the menu, and on that menu, you're going to go ahead and click on "create". So this is where it gives you choices: you can start from blank, you can start from a template, and you can start by using a connector, which is typically another app. We're going to be using connectors when we start from a blank one. So the type of blank flow that we want is an automated Cloud flow. We have Power BI in the service, which is online; we're using a form that we created in Microsoft's Forms, which is also online, so we're going to do an automated Cloud flow. The first thing we have to do is give it a name; it says "add a name," or we'll generate one. We're going to name this "Streaming Data Flow".

Now flows respond to triggers; something happens that causes the flow to respond. The trigger we want is a Microsoft Forms trigger. If it's not showing on your list, "when a new response is submitted," that's the actual trigger we're going to use. You can click in the search box and type "forms," and it will show all the different types of form triggers. So we're going to go with "when a new response is submitted," and we're going to click on "create". So now for this particular trigger, you have to tell it which form. Do the drop down next to "pick a form," and your streaming survey should show on that list. So that's the first step to trigger this flow. That we're creating will respond when a new response is submitted to our streaming survey. We need to add another step, so go ahead and click the "new Step" button, and this is another Microsoft Forms step. So in the search box, go ahead and type "forms" just to narrow the list. I really can't promise, and you'll see that you get some actions. So "when a new response is submitted" is a trigger; now what do you want to happen when that trigger is activated? We would like to get the response details from that form. So again, it needs the form ID, and you can do your drop down and select "streaming survey," and click in the response ID field. You'll get this pop-up on the right that says "add Dynamic content from the apps and connectors used in this flow." There's only one choice there, and it's response ID; go ahead and click on it. So what it's saying is, when a new response is submitted on the streaming survey form, get the response details by response ID. Each respondent is assigned a response ID, so each person that takes the survey is answering three questions; there are three responses will have the same ID. We have to add one more step, so go ahead and click your "new Step" button again. This time we're using the Power BI connector. So in the search box, type "Power BI," and you'll see a list of all the actions for Power BI. The action that we want is "ADD rows to a data set". For that one, we have to tell it which workspace. So do your workspace drop down, and you're going to choose your "streaming data set workspace". We named the data set itself the same thing, so access that drop down and make the choice, and then do the drop down for the table. Notice "workspace," "data set," and "table" all have a red asterisk in front, which means they are required. The name of that table, we saw it when we went and looked at the reports, is "Real Time data". Once we select the table, we get three Fields: technology, experience, and location, and we have to tell it which question maps to each field. So when you click in "technology," on the right side you'll have that Dynamic content box, and it'll show you the questions; it'll also show you some other things, but it'll show you the questions that we created in our form. So the first one is "which BI technology are you most interested in learning," and that's the one we're going to click on. For "experience," click in the box, and "what is your current experience level on the technology" is the question. For "location," you're going to grab "where are you from". And now we've completed our flow. A couple of things: if we had problems with anything we did in our flow, the flow Checker would be red. Go ahead and click on the flow Checker; you see we have zero errors and zero warnings, which is good; we don't have any problems. We need to save our flow, so you can either save it up on the top or you can save it on the bottom, and it lets you know when it's saved. So now you have that green band that says "your flow is ready to go". We recommend you test it. What I'd like you to do now is use the Microsoft waffle and open...What I'd like you to do is reopen Forms in a new window or on a new tab; go ahead and do that.

So I've arranged my screen so I have Microsoft Forms on one side and my dashboard, my streaming dashboard, on the other side. What I'm going to do is I'm going to be a respondent for my own survey, and I'd like you to do the same thing. What you're going to do is click on your survey; if it's not there, you can go to "all my forms" to find it. So I'm going to click on the "streaming survey," and in order to respond to it, I'm going to click the "preview" button up here on the bar. So now what I'd like you to do, I would like you to submit five separate responses to your own survey. After you finish the first one, it'll give you the opportunity to take the survey again. Make random answers when you're going through this process, and go ahead and get started on doing that. Now I'm going to review the streaming dashboard; you'll notice that it populated with my choices from my survey responses. So the table populated, the map populated, and you can see the locations on the map; the count of Technology card populated, but the technology and experience report did not. So let's take a look at the report itself to see what's going on with that. I'm going to just return to my streaming data set workspace, and I'm going to select a streaming report. Once it loads, I'm going to go up to "more options" and edit it. So it looks like report view in the desktop; I'm going to select that empty visualization. I can see that it is a stacked column chart, and the reason it's not populated is because we only filled in the axis and the legend; we need a value for that chart to populate. So in your Fields pane, expand "real-time data" and drag "location" to the values field. Because it's a non-numeric field, it does a count of locations. So now you'll see that the chart has populated. The thing is, at this point, we have to pin the updated visual to the dashboard. So go ahead and select the pen visual, push pin above it, and we're going to pin it to our streaming dashboard. Go ahead and click pen, and then I'm going to navigate to the dashboard from the pop-up; it's going to ask me if I want to save changes to the report, and I'm going to choose save. So now on the dashboard, I have both the empty one and the populated one. I'm going to remove the empty one by going to its more options icon and choosing "delete tile". I'm going to scroll to the right so I can see the more options for the populated chart, and I'm going to click and hold on that and just drag it over to where the other one was, on the left of the count of Technology card.

So now you've created your push data set. Let me go over the process we went through for this: we started by creating our form, and then we came over here and we created our push data set. We created a workspace, a premium workspace to hold it, and we also designed our reports; they were empty until the data got pushed in when we responded to our survey. After we designed our reports, we created our automated flow so that it's triggered when a response is submitted on the form, and it grabs the data and dumps it into our data set. One of the things we can do to enhance our dashboard is to apply a theme to it. This is very similar to applying a theme to a report in the service, and we can access the themes by going to the edit icon up at the top of the screen, and we're going to select "dashboard theme". The panel opens on the right. Now there are not as many themes in the service as they are in the desktop. So when you do the drop down next to "light," which is the default theme, you have "dark," "colorblind friendly," and "Custom". I'm going to select "dark," and you know that could be hard to read; the part of the dashboard that I see behind it could be good for some people. I'm going to go back to the drop down and select "colorblind friendly," which really looks a lot like white in here, but apparently, just like in the service, they have a colorblind friendly theme, and then I'm going to go back one more time and choose "custom". So you can use a background image for your dashboard; you can change the color of the background. So I'm going to do that by accessing the color drop down, and I'm going to choose...you can make the color choice that you like, but I'm going to choose like this light teal color. You can have a background color for the dashboard tiles, so the things that are on the dashboard, if you like. Why don't you take a few moments and go through those settings and figure out the way you want it to look. Foreign making your choices, click the save button in the lower right-hand corner.

Another enhancement feature is to add a video or audio or even text boxes to your dashboard. We also use the edit button to access those features, and you're going to choose "add a tile". In the "add a tile" panel, you'll see that you can add web content. If you click on "web content" and choose "next," you can see that you can have a title and subtitle, and you would have to embed the code here. I'm going to use the back button; you can add an image, a text box, or a video here. I'm going to go ahead and click on "video," and then I'm going to click "next". So it adds the "add video tile" box here, and you have to have the video URL, and right now it will only accept videos that are on YouTube or Vimeo. Open up a browser window or a new tab and navigate to YouTube. At the top of the screen, click in the search box and type "how to use web apps," and at the end type "learn it," all one word, "learn it," press Enter. So if you scroll down just a little bit, you'll see "Office 365 how to use web apps"; it's by LearnIt Training, and you're going to click on that so that the video page comes up. Now I just stopped the video from playing because what I want to do is go all the way to the top of the screen and see the address bar where the URL is. So I'm going to select...I'm going to click in the address bar at the top of the screen, and I'm going to do Ctrl C to copy that URL, and I've switched back over to my supplier quality analysis sample dashboard, so we can fill in the details in the "add video tile" pane on the right. We're going to check the box that says "display title and subtitle"; the title is going to be "how to use web apps"; we don't need...well, we'll put "LearnIt" in the subtitle just so you can see how that looks, and in the content section for the video URL, you're going to do Ctrl V as in Victor to paste, and now you're going to click the "apply" button. You saw the pop-up that said the content was added to your dashboard. So everything you add to your dashboard at this point is going to go to the bottom, so I'm scrolling down, and you have the video there on your dashboard. Now this particular video, it's a very short video, um, that's why I chose it; it's also from LearnIt, another reason why I chose it, but it has nothing to do with the supplier quality analysis data, but just to give you the information on how to add videos to your dashboard to enhance them is what the important takeaway is here.

Another feature you can use on your dashboard is setting a dashboard tile alert. A tile alert will notify you when data on a dashboard changes above or below limits that you set. Right now, tile alerts can only be set on card, KPI, engage visualizations, and Power BI. I'm going to scroll back to the top of my dashboard, and I have a "total defect quantity" card. If I navigate to its more options button and click it, you'll see that I have "manage alerts" there because it's a card visualization; it allows me to feature...I'm going to go ahead and click "manage alerts," and the "manage alerts" box pops up on the right side. It lets you know for which card it's for because, you know, sometimes when you're working fast, you might select the wrong thing, so it gives you a reminder at the top. We're going to click on "ADD alert rule"; it defaults to being active; you can have alerts set up and inactivate them that if you don't want to use them for a while and then just reactivate them when you want to use them again. We're going to leave the default title; it again repeats, and then here it has the name of the field, the actual name of the field, and the condition defaults to "above"; you have a drop down where you can choose "below," and the threshold we're going to put in 35. On the card right now we're at 33 million, so this would be at 35. You get to select your notification frequency; it defaults to every 24 hours. If you want to get alerts every hour, you can change that. They will only send if your data changes, so you'll get alerts in your notification center in the service, and you can opt to have an email sent to you as well. I'm going to uncheck "send me an email" to box; it's recommending that you can also use Power Automate, where we set up our flow to trigger additional actions. We're going to do "save and close" at the bottom. So you can only do alerts on cards, gauges, and KPI visuals at this point. If you opt not to get an email, your notifications for your alerts can be found to the left of your login information up here at the very top right-hand corner. Depending on your resolution, it might already be expanded, but I'm going to click on that settings button, and then I can click on "notifications". So if you have notifications, you can get them from the notification center. So when that total defect quantity gets to 40 or above, you'll get the notification there. Again, you can opt to get it via email as well.

There's an artificial intelligence visualization that you can use both in the desktop and the service, and I'm going to introduce it to you now; it is called Q&A for question and answer. You've noticed since we've been in the dashboard, in the upper left-hand corner it says "ask a question about your data"; that's where Q&A resides in the service. You could also apply it as a visual on a report; it functions a little bit differently in the desktop than it does in a service, but the end result will be the same; it's going to perform analysis on your data. If you click in that on that line that says "ask a question about your data," it'll say it's preparing your Q&A, and when you get in here, you'll see a series of tiles, questions that Power BI determined to ask about your data. If you have a link on the right that says "show all suggestions," click it, and it will display more tiles if there are any. So you can review these tiles and see what they're saying. The thing is, is there may be questions that it comes up with that you look at these and you say, "you know, I should have thought of that; that is a great question." You can click on any of these tiles and see the result. I'm going to click on the tile that says "show defect quantity supplies and total defect quantities," and when I click on that, it creates a visualization for me. So it's showing me the information I requested. Now if I want this information to be on my dashboard, in the upper right-hand corner, I can click on "pen visual," and it's on our existing dashboard, so we're going to do pin, and in the upper left-hand corner of the screen, you'll see an arrow and it says "Exit Q&A"; you can go ahead and click that, and again, everything you add to your dashboard will come up at the bottom, so you can scroll down and see that new tile that was added. If you want to spend a few moments going back to Q&A, looking at different tiles and pinning them or not, feel free to do so.

There's a data analytics feature that's built into the service called "Quick Insights," where Power BI will analyze all of your data and offer you a wide variety of visualizations. In order to get access to Quick Insights, we need to go back to our workspace. So you can use that next to the last button on the left-hand side to get back to your current workspace. Quick Insights are performed on data sets, so we're going to go to the more options button for the supplier quality analysis sample data.

Set and you can click on “Get quick insights” or “View insights” if you’ve accessed this before. If you’re getting quick insights, it’ll have a pop-up box in the upper right-hand corner letting you know that it’s getting them and when they’re ready. When they’re ready, you can go back to your data set and click on “View insights.”

And it tells you, “These are quick insights for supplier quality analysis. A subset of your data was analyzed and the following insights were found.” There is a “Learn more” link. There is help every step along the way in Power BI, which is a good thing. So it gives you these insights: Impact has noticeably more total downtime minutes, max, and total defect reports. So it gives you visualizations as well as the written insight. And just like Q&A, you may come upon things that you decide, “You know what, I really should have that visualization on my dashboard.” So the one I’m looking at, total defect reports by material type, that might be good to put on the dashboard. It has the pin in the upper right-hand corner, and I’m going to go ahead and pin it to my dashboard. You can scroll through these other insights, and if you see anything else that you want to add to your dashboard, go ahead and pin it. I found another one, the count of plant by category, that I’m going to add to my dashboard. And then I’m just going to go back to my Power BI video workspace and access my dashboard again. And remember, scroll down and toward the bottom you will see the insights that you just pinned to your dashboard. I love quick insights in Q&A. Oftentimes I see things that I hadn’t thought about, so they’re very helpful features.

There are just a few other dashboard features I’d like to show you, one of which is setting a featured dashboard. When you set a dashboard, you can only have one at this time as featured. Whenever you go into the service, it will open with that dashboard already displayed. If you have multiple dashboards that you’re working on, you can set the one that you’re currently working on as featured and then set the others as favorites, which gives you easier access to them. I’m going to show you how to set this dashboard as a featured dashboard. You’re going to do it by using your more options button and choose “Set as featured.” It wants you to confirm that you do want to set this as your featured dashboard, so I’m going to confirm it, and now it lets you know that it’s featured. So if you go out of the service and back into it, this dashboard would show. Now, for the others that you work on frequently, you can make them favorites. Let’s go back to our workspace and hover over your Retail Analysis Sample dashboard and use the star to add to favorites. Do the same with your Sample Superstore dashboard. It’s easy to navigate to favorite dashboards because on your left-hand side in your navigation pane you have the favorites icon, and when you click there, you’ll see any dashboards that you have set as favorites. If you end up setting a lot of dashboards as favorites, instead of scrolling through what could be a potentially long list, you can use the search at the upper left-hand corner.

Let’s go back to our workspace and let’s go back to our Supplier Quality Analysis dashboard. We’re not going to actually go to the dashboard; we just want to be able to see it. On the screen to the right of it, you have your more options; go ahead and click that and choose “View usage metrics report.” This is a good report to have. I mean, you can come in here, and you can see all of the ways that this dashboard may have been distributed, um, the platforms, how many views per day, unique viewers per day, shares per day. It gives you a synopsis over on the right side for your total views, viewers, and shares. It does a ranking in here comparing this dashboard against other dashboards in your organization, and it will also show you the total dashboards in your organization and the views by user. So as people, once you share and people start viewing your dashboard, you’ll be able to look at the dashboard usage metrics. You’re able to print your metrics from the file menu; you can also export, so you can analyze the metrics in Excel. You can even pin your metrics to a dashboard.

The last thing I’m going to cover in this module is how to configure your dashboard for mobile view. Nowadays, a lot of people are accessing information from their phones or tablets, so it’s important that they be able to access your information in that way. We’re going to go back to our workspace, and we’re going to open up the Supplier Quality Analysis Sample dashboard again. And by the way, before we do this, if you go back to more options, you can see that you can disable this as the featured dashboard if you’re pretty much done working on it and you want another one to open up when you go into the service. This is how you disable it. Again, you’re only allowed to have one featured one, but to get to the mobile layout, we’re going to go to the edit drop-down and we’re going to click on “Mobile layout,” and it changes the views, so it kind of looks like a cell phone there. And what you can do is, from here, it has some of the tiles from the dashboard; it just picks ones that it thinks will fit in this mobile layout. If you wanted to unpin all of those tiles, you could, from here, and if you do that, then you can reset them. What we’re going to do is we want to keep the top two tiles because those are cards. Cards are good to put in a mobile layout; they don’t take up too much space. So for the map, I’m gonna hover over it and hide the tile, so it unpins it, and for this column chart, I’m going to do the same. And you can see the unpinned tiles here. Now there’s more on here; we really just want to keep the cards, so it pretty much grabbed everything, but you could only view a certain amount in the mobile layout. If you want it to view more, that’s fine, but we want to get rid of everything that’s not a card.

So take some time and do that, and when you’re satisfied with your mobile view, it’s already set. You can get back to the other view by clicking on this mobile layout button and going back to “Web layout.” We’ll do more work on workspaces in a later module. This module was action-packed. We started by creating a dashboard and pinning visuals to it. We went in depth with real-time dashboards, so we started by creating a form; we set up a push data set, created some visuals; we used Power Automate to generate a flow that would take the data from responses on the form and push it into the data set, and we saw our visualizations populate. We moved on to enhancing a dashboard by adding a custom theme and video to it. We configured a dashboard tile alert—again, that’s only for gauge card and KPI visuals at this time. You got introduced to Q&A and quick insights for analysis, and we added some of the results to the dashboard. You learned how to set a featured dashboard and how to unset it because you’re only allowed one, and you learned that you can add multiple dashboards as favorites, so they’re easy to navigate to. You also learned how to configure a dashboard for mobile view.

Hi everyone, I’m Trish Connor Kato, and I’d like to welcome you to Microsoft Power BI Module 9. We’ll guide you through how to create paginated reports in Power BI. These types of reports are designed to be printed and/or published. They can also be exported to PDF, PowerPoint, and they are formatted to fit well on a page. They will display all of the data in a table, even if it spans multiple pages, and when you export them or print them, you will see that that table will span multiple pages. With regular report visualizations in Power BI, if you use the table visualization on a regular, non-paginated report and that table spans multiple pages, when you print it or export it, it’s only going to print or export what it sees on the screen, meaning you won’t get the whole table printed or exported. Paginated reports are only available in premium workspaces, and in this module, you’ll learn how to make a workspace a premium workspace. And we’ll be accessing a sample data set from within the Power BI service for this module.

In order to create paginated reports in Power BI service, you need Power BI Report Builder. It’s a separate application. When we get into the service, I’ll show you how you can gain access to it and download it. It’s the only authoring tool for paginated reports. This is the only game in town where you can create them. You use the Report Builder to develop reports, preview them, and then you end up publishing the reports to the service. You can add those reports to a dashboard in your service. Report Builder is a developer tool; it’s not meant as a report consumption tool. So once you publish your paginated report to the service, you can put it on a dashboard and distribute that so users have access to it. So when we get into the service, I’ll show you how you can download Report Builder, or I’ve already put a link in the website links for additional information document in the video description.

So in this module, we’re going to use the Power BI Report Builder to create paginated reports. We’re going to design a multi-page report layout. We’re going to define a data source and a data set within Report Builder. We’ll publish our report to the service, and then we’ll use the export feature so you can see how it exports as a PDF. So I’m back in the service, and I’m in my Power BI video workspace, and the first thing we want to do is bring in some sample data from Microsoft. If you look in the lower left corner, you’ll see that arrow that seems to be pointing right, and that arrow, if you hover over it, it says “Get data.” We’re going to click on that arrow. If I scroll to the bottom of this screen, under “More ways to create your own content,” I am going to click on “Samples,” and these are some sample data sources that they have within Microsoft. We’re gonna look for the Supplier Quality Analysis Sample tile and click on that, and it brings up a subsequent screen. You can learn more about it; it tells you about this data that’s in here, and we’re going to click on “Connect.” So it says in your upper right-hand corner, it’s importing data; it could take a little while. In my case, it didn’t take very long at all, and when I look in my Power BI video workspace, I have both the Supplier Quality Analysis report and the Supplier Quality Analysis Sample data set. Let’s click on the link to go to the report. So this one comes with a lot of visuals already in it, as you’ll see on the left side. We have three pages of visualizations, and I’m going to navigate to the top/bottom analysis page just to point out there’s a table on this page, and it has a scroll bar in it, so you know all of this table data would not fit on one page if you were to print it. So let’s just see that this is a regular Power BI report; we haven’t learned how to create the paginated report yet, but we want to see what this will look like if we were to print it. To show it in print preview, I’m going to do the drop-down for “File” and choose “Print this page,” and it will open up the print preview window for me. So when I scroll down print preview, you see that it’s not showing everything in that table. This is print preview; it’s showing the scroll bar, but you can’t scroll in print preview for that. So this is the difference between a basic Power BI report visualization and a paginated report, and I’m going to just cancel the print preview.

Now I’m ready to turn my workspace into a premium workspace so that when I build the paginated report, I can publish it to a premium workspace. So what I’m going to do on the left side is I’m going to go to my workspaces icon. I’m going to hover over my Power BI video—I don’t know if you’re using my workspace or another one, but whichever one you’re using where you put the sample data is the one that you want to make a premium workspace. I’m going to go to the more button on the right side of my workspace and access “Workspace settings.” On the top, I’m going to click on the “Premium” tab, and I’m going to choose “Premium per user.” So you have two premiums there: you have Premium per user, Premium per capacity. In the first module, we talked about the different licensing and subscription types. I have Premium per user, so I’m going to go ahead and click “Save,” and it’s letting you know that you’re changing workspace access, so only people who have Premium per user licenses will be able to access this workspace. Anybody that has any type of Premium access licensing will be able to access this workspace, but the reason why we’re doing this is because you can only publish paginated reports to a premium workspace, so I’m going to click on “Continue.” And when I go back to my workspaces, you’ll see that it has the little diamond icon next to Power BI video, and that indicates that it is now a premium workspace. I’m going to navigate back to my workspace, my Power BI video premium workspace, and I’m going to just use the link in the upper right-hand corner to get back to it. In order to access Report Builder, we’re going to click on “New,” and when you click on “Paginated report,” if you do not have Report Builder, a splash screen will open with a download button. Feel free to pause this video so you can download it, and we’ll continue with the module. Since I already have it, when I click on “Paginated report,” it’s going to launch Power BI Report Builder. Okay, on the splash screen for “New report,” they have four—they actually have three—wizards there: “Table or Matrix wizard,” “Chart wizard,” “Map wizard,” or you can do a report from scratch. We’re going to use the “Table or Matrix wizard,” so I’m going to just click on that, and the first screen that comes up is to choose a data set. We’re going to connect to the Supplier Quality Analysis data set that’s in our premium workspace, so we’re going to just do the “Next” button in the lower right-hand corner. And it says “Choose a connection to a data source.” We don’t have any connections listed here, so we’re going to click on the “New” button, and we’re going to name our data source here where it says “Data source 1”; we’re going to just name it “Supplier Quality Analysis,” and underneath that, where it says “Select connection type,” it defaults to Microsoft SQL Server. We’re going to do the drop-down, and we’re going to choose “Power BI data set,” and the connection string area to the right, you’re going to click on “Build.” So it’s going to bring up this screen: “Select the data set from the Power BI service.” Right, it’s looking at—it has a listing of all your workspaces on the left. I’m going to go to my Power BI video workspace, and I’m going to click on “Supplier Quality Analysis Sample” and then choose the “Select” button in the lower right-hand corner. So now you’ll notice that the connection string is dimmed out, but it’s filled with data, and we’re going to test the connection before we continue by clicking the “Test connection” button. It’s always a good idea to test your connection. It lets me know that the connection was created successfully, so I’m going to click “OK,” and then I’m going to click “OK” again, and now it shows “Supplier Quality Analysis” as my data source connection, and I’m going to do the “Next” button in the lower right-hand corner.

In the new “Table or Matrix” dialog box, it says “Design a query. Build a query to specify the data you want from the data source.” We’re going to do just that. If we had this in Power BI Desktop, these would be all of the things that you would see in the Fields pane under Model on the left-hand side. In that list, we’re going to expand “Vendor” by clicking the plus sign in front of it, and then we have another “Vendor” that shows up that also can be expanded. Expand that one as well, and then you’ll actually see the field name “Vendor”; it has like one little dot in front of it. We’re going to click and hold on that field and drag it right into this space over here and drop it. So now it just shows “Vendor” there. On the left side under “Model,” we’re going to expand “Measures,” and then expand “Metrics,” and there are two that we’re going to want to choose from here, and we’re going to drag it into the query window. The first is “Total defect quantity,” so we’re going to grab that and drag it to the right of “Vendor.” Notice it puts a guideline there, so it won’t let you overlay it. And the other one we want—the other measure that we want—is “Total downtime minutes.” I’m going to drag that into the query window as well. These are the three fields that we’re going to want. So in the middle of your query window, you can click on “Click to execute the query,” and it will show you the underlying data. We’re going to go ahead and click “Next.” This screen allows us to arrange the fields to group the data in rows, columns, or both and choose values to display. In the “Available Fields” list on the left, you’re going to double-click on “Vendor,” and it goes into “Row groups.” If you wanted it in “Column groups,” you could just drag it from “Available fields” to “Column groups,” but it defaults to rows, and we’re going to use our two measures as values. It knows their values, so if we double-click them, it will put both of them in the “Values” box, and the default value calculation is the sum function. If you do the drop-down next to either of those measures, you’ll see the other functions that are available. We’re going to leave it on sum, and we’re going to choose “Next” at the bottom. So this one is just a preview of the report, so it’s going to have the vendor, the total defect, the total defect quantity, and total downtime in minutes for each vendor, and now we’re ready to go ahead and click “Finish.”

Once we clicked “Finish,” it puts us in what’s known as design view in Power BI Report Builder. We have a little bit of a quick access toolbar all the way at the top in the upper left. Let’s go ahead and click “Save,” and you’re going to save this report as “Supplier Quality Analysis.” Notice the type of report is Report Definition Language (RDL). Go ahead and save. So it updates the title bar; we know that we saved the work that we’ve done so far. The first thing we want to do is we want to sort our report in descending order by the sum of the defect quantity. So in that design grid, I’m going to right-click in the second—you can see where it says “Sum total defect,” the one that’s not bolded. I’m going to right-click on that, and on the shortcut menu, I’m going to hover over “Row group” and click on “Group properties.” On the left side of the “Group properties” dialog, I’m going to click on “Sorting,” and I—where it says “Sort by Vendor,” I’m going to do the drop-down, and I’m going to choose “Total defect quantity,” and to the right of that, in the “Order,” I’m going to do the drop-down and select “Z to A” for descending. So we could have clicked on any of these fields and done this; I tend to stick to the field that I’m trying to effect change on when I work in here. And at the bottom, I’m going to click “OK.” So when we ultimately run the report, it will be sorted in descending order by total defect quantity. Go ahead and save again; you want to save as frequently as you can when you’re working with the Report Builder. We can always run the report to see what it will look like at whatever stage of development it’s in. If you look up at your ribbon, the Home tab of the ribbon, the first button is “Run.” Go ahead and click that button, and you’ll see that it loads the report, and so we’ll see what it looks like at this time. A couple of things I notice is we might not want our headings to wrap within the cell like that, so we might want to make those columns a little bit wider. You’ll see that it is sorted in descending order at this point by total defect quantity, which is the sort that we instituted, and you can use Control+End to get to…

The bottom and you will see the Total Line at the bottom of the report. To get back to design view, you only have the Run tab up here now and, in addition to the file tab, but the Run tab; the first button is design, and it will take you back to design view.

Now we're going to address our column width. So if you click in the little table area there, what you're going to want to do is you want to click above where it says Total Defect Quantity. When you hover your mouse in between the dividing line between Total Defect Quantity and Total Downtime Minutes, your mouse will change to a double-headed arrow. I'm going to click and hold, and I'm going to just drag it so I see the full title, Total Defect Quantity, and it's not going to wrap in the cell like it did on when we ran the report. I'm going to also widen the column for Total Downtime Minutes so we have the same effect where it's not doing a word wrap in there, and I'm going to click away from it.

The next thing we're going to do is we're going to replace this report title, where it says "Click to add title," right? We're going to replace that with a text box with our report title in it, and we're going to cause that text box to repeat on every page of the report. So what I'm going to do is I clicked where it said "Click to add title," but I'm going to click on the border of that so you get the sizing handles around those little circles and squares; those are sizing handles, and I'm going to press Delete on my keyboard because I want to insert a text box in that area. And we do that from the Insert tab on the ribbon, in the Report Items group. You're going to go ahead and click on Text Box. And as you move your mouse over your report design, you'll see it looks like a plus sign with a box attached to it. I'm going to click as far as I can get into the upper left corner of the report design canvas. I'm going to click and hold, and I'm going to just draw a text box about that size.

Foreign is already in the text box, so we just need to type the title of this report, and that's going to be Total Defects and Downtime Minutes per Vendor. So I'm going to just type it in that text box: Total Defects and Downtime Minutes per Vendor. And I'm going to select the text, and on the Home tab you have a font group. I'm going to use that big "A" to increase the font size. So I clicked it once, I'm going to click it again and again; so I click that uppercase "A" to increase font three times for our title.

Once my title is formatted the way I want it to be, I'm going to select the text box again; it's selected when you have the sizing handles around it. And you'll notice on the right side of your screen you have a properties window. If your properties window is not showing, you can go to the View tab on the ribbon and check the box in front of Properties, and then it will show over on the right. So we're looking at the properties for that text box; the properties are in groups, and we want to find underneath Other. You're going to click where it says "Repeat with," click to the right of over there. And when you access the drop-down to the right of "Repeat with," you only have two choices: None and "Table1." We're going to choose Table1, which is basically that table of data on design view. So we're telling it to repeat wherever that table is; so if the table spans multiple pages, the report title will repeat on every page. Go ahead and save your Report Builder file.

We'd also like the column headings for the table—Vendor, Total Defect Quantity, and Total Downtime Minutes—to repeat across pages as well. That's a little bit more in depth to get that to happen than the text box that we just did for the report title. So to make those column headings from the table repeat, on the bottom of your screen you'll see Row and Column Groups. If you don't see those boxes, on the View tab you want to check Grouping, and they will appear at the bottom of your design view. To the right of the Column Groups heading, there's a down arrow; you're going to click that and select Advanced Mode, and it just makes everything in those two Row and Column Groups—you have a lot of things that say Static. Now under Row Groups, you're going to click on the first Static, and now you'll see on the right the property screen is different; you have an Other section for properties, and you're going to toggle Fixed Data from False to True, and there's a drop-down to the right of False that you can use to select True. You're going to toggle Keep Together from False to True, and you're going to toggle Repeat on New Page from False to True. I made an error in that selection, so toggle Keep Together back to False, and what I meant to say is you're going to toggle Keep with Group from None to After. Those are the settings we need to get those column headings to repeat on every page. We need to say that it's fixed data, that we want to keep them with the group that's coming after it, and we want to repeat the heading on new pages. We're going to return to the drop-down to the right of Column Group and turn off Advanced Mode, and then go ahead and save your report again.

Now if you look at the Report Data pane on the left side in design view, you have a category called Built-in Fields. Go ahead and expand it; you'll notice the first one is Execution Time: When is the report executed? When is it run? And that's already in the footer as a placeholder. Let's go to the Home tab of the ribbon and run the report—the first button—and we'll see that we have our title at the top; we have our column headings—again, it's sorted in descending order by Total Defect Quantity. If we scroll down, you'll get a sense of how much data is here, so you know that this data is going to appear over multiple pages. We're going to use the first button on the Run tab to go back to design view and run it again. This time when it loads the data, do Control+End, and it takes you all the way to the bottom, and you'll see the footer; that's the Execution Time built-in field that was already placed in the footer section. We can go back to design view. Now you have other built-in fields that you can use, you know, if you want the page number and the overall total pages, the name of the page, so on and so forth, the report name; that's kind of like placeholder stuff that you can use on your report. I'm going to just save the report again.

And now I'm interested in publishing it to the workspace. So on the Home tab, the last button is in the Share group, and it's Publish. Go ahead and click on your Publish button. Remember, you can only publish to a premium workspace. So when I'm on my workspace, notice the Publish button is dimmed out; that's because that's not a premium workspace. I'm going to click on my Power BI video workspace, and then when I click in the file name box, I'm going to type Supplier Quality Analysis, and notice my Publish button is active here, so I'm going to click it. It lets me know that it's been successful, and right from here, just like when you're publishing from the desktop, I can open Supplier Quality Analysis in Power BI. I'm going to go ahead and click that link, and it's loading the report in the service. Notice it's in my Power BI video workspace, and it generated my report.

I want to start by going to the workspace, so I'm going to go up here and use the link to get to my Power BI video workspace. And you'll notice, if you scroll down if necessary, whatever workspace you're in, you have two reports with the same name: Supplier Quality Analysis. Notice the icons are different though; the one that looks like a chart, like a column chart, that's a regular Power BI report icon; the one that looks like a page that kind of has its upper right-hand corner folded, that is the paginated report. I'm going to use the link to get to that paginated report so we can go over it. It may take a moment to load; it shouldn't take too long though. And when you're looking at a paginated report, you have a couple of different views here; the second button on that toolbar at the top is View; it's in default view right now, and there's a page navigation area right here where you can click to go to the next page, which it will load. Notice that it's repeating the report title as well as the column headings on the pages, so you can navigate through some more pages, and you'll see that in default view—this is the view that if users are consuming this report in the service, this is the view that they'll be using. If I go to the File drop-down and choose Print Report, and a lower left-hand corner, there's a Preview button at the top; it tells you "We'll create a printer-friendly PDF version of your report." I'm going to click on Preview, and it may take a moment to load again, but when I previewed a report, and there's my page navigation at the top, and I navigate to the next page, it's going to load every time for a page; you'll see that it's not repeating the report title; it's just going to repeat the column headings. The report title is a text box object, so that's really—if you want to repeat it, it's only going to repeat when someone is consuming the report and not actually printing it or exporting it to a PDF. You don't really need that heading to print on every page, but it will show up. If I go back to default view, then you'll see it on every page, so an end-user can consume it online like this and get that heading on every page, but when it's printed or exported, it's only going to repeat the column headings. So for paginated reports, end-users consume it in the service, or you print it and distribute it, or you can export it to any of these formats: Excel, PDF, accessible PDF; you can export it to PowerPoint, Word, so on and so forth, and distribute it that way.

Let's go back to the workspace that has the paginated report in it, and let's say you look at your report in the workspace and you decide that you want to make changes to it. If you hover over the paginated report and go to the More options button, you'll see that the top option is to Edit in Power BI Report Builder. Let's click that, and it will relaunch Report Builder for you, and it brings up your report. So if you wanted to make any changes in here, let's add something else to the footer area. On the left side, and I can maximize this screen so you're not seeing my workspace behind it, on the left side I'm going to expand those Built-in Fields again, and this time I'm going to drag Overall Page Number into the footer section on the left side, and I'm going to save this report. Click in the pane—oh, it's considering it already saved. I'm going to collapse Built-in Fields here, and I'm going to just click the Publish button again, and it's bringing up—because I've already published from here—it's bringing up Supplier Quality Analysis, and we're going to use the same one; we're going to basically overwrite it by publishing it. It lets you know that that item already exists; you want to replace it; we're going to click on Yes, and then you can open it again in Power BI. I'm going to just drag my scroll bar to the bottom, and I'll see the page number there, and then the date and time that was already in there for the execution date and time.

So in this module, you learned what paginated reports are, and that they're meant to be consumed in the service or by exporting to a PDF, printing and distributing; there's no option to put them on a dashboard; they're not meant to be consumed that way. You created the multi-page paginated report in Power BI Report Builder by creating and configuring a data source and a data set. We used the Table wizard to create our report; we published the report and reviewed the print and export options. You also learned that you can only create a paginated report in a premium workspace, so you learned how to make a workspace a premium one in Module 10. You'll learn how to perform advanced analytics on your reports; you'll be introduced to report features that perform analytical insights into the data; you'll learn how to perform advanced analytics using an AI visual on the report for deeper and meaningful data insights. We'll be working in Power BI Desktop after a brief task in the service, and we'll be using the Sales and Marketing sample desktop file, and we'll be able to grab that from the service. We'll start with our advanced analytics lesson, which contains features like grouping, binning, drill down and up, and analyze. We'll move on to using the Key Influencers AI visual; then we'll get into creating an animated scatter chart, which is really cool; we'll use a visual to forecast values and end up creating a custom advanced analytics visual.

Now would be a good time to get back into Power BI service. So earlier we accessed Microsoft Supplier Quality Analysis sample data, and we brought it into the service, and it gave us the data set and the report; we actually ended up putting the report onto a dashboard. So what we're going to do now is we're going to go back to that Get Data arrow in the lower left-hand corner; we're going to scroll down, and in the lower left, click on Samples. And this time we actually want to pull the desktop file, not the report and data set, into the service; we actually want to just download the desktop file. So we're going to click on Sales and Marketing sample tile, and instead of clicking the Connect button, which will give us the data set and any reports, we're going to click on Learn more, right underneath the Connect button. It's going to open another browser tab; it gives you all the in-depth information about the data that's in this sample, and underneath Get the sample, you can see you can download the dashboard, report, and data set; if there is a dashboard already in with the sample data in our previous example, it gave us the report and data set, and we got that into the Power BI service into our workspace. This time we want to grab the .pbix file, which is the desktop file. You'll notice you can also download the Excel workbook if you wanted to. So it walks you through; you can get the sample from the service; if you keep scrolling down, you can get the .pbix file; there's a link there to the .pbix file. So I'm going to click on that link, and it's going to start downloading the file for me. Actually, I have it downloading twice because I clicked the link twice, but it's okay; I can stop one of them. And once it's done downloading, I'm going to go to my Downloads folder on my computer and copy that file into the working directory that I've been using for this class, and then I'm going to launch it, and it's going to open up Power BI Desktop, and it will have that data already in it.

The first page in the desktop file is an info page, so this data is provided by a company known as Obvious, which works in conjunction with Microsoft, and we don't need that page, so I'm going to just hover over the info page tab and do the little X in its upper right-hand corner. It will always prompt you if you're going to delete a page, so I'm going to go ahead and choose Delete on the prompt, and it's going to start rendering the images on each of these pages. So we have a Market Share page, Year-to-date Category page, a Sentiment page, and a Growth Opportunities page. If it needs us to use Microsoft—are open—on a specific type of visual to render it, it would notify you, as you saw on the screenshot in the slideshow. The first thing we're going to talk about here is the concept of grouping in Power BI. You can group fields together, and you'll see how this works. I'm on the Growth Opportunity sheet tab, and in the lower left-hand corner there's a column chart. If I click on that chart and make it active, I can see that they're using the Segment field for the axis and Total Units for values, so we're seeing all the segments. If we want to take a look at the data first, let's go to Data view on the left-hand side, and we want to click on the Sales Fact table, and you can see some of the fields that are in that table; it's a lot of measures in that table, right? We also have a Sentiment table, which I'm going to click on in the Fields pane so we can see what's in the Sentiment table. If we scroll down in the Fields pane, we'll have the Date table; we have a Geographical table; let's expand the Manufacturer table or click on it, and you'll see the data in there, and we have one remaining table at the bottom, which is Product, and the Product table is where the Segment is coming from. We can go back to Report View, and that report is still selected on the Growth Opportunities page, so we know that Segment is coming from the Product table. Also in the Fields list, the tables that have the yellow check mark are the tables that are being used in the visual, so Segment is coming from the Product table, and Total Units is coming from the Sales Fact table. Grouping is something that's typically done on a visualization, and for this column chart, we decide that we want to group the Youth and Regular segments together. So in the column chart, I'm going to click on Youth; I'm going to hold down my Control key and click on Regular, so both of those segments are selected. I'm going to right-click on Youth or Regular, and I'm going to choose Group data.

So it flashed on the screen really quickly that it was working on it, and now it's done the grouping. So if you look at your Legend, right, it says Segment Groups, and that's because in your Fields pane it created a group with those two segments in it, and it named it Segments Group, and it added it to the legend in the visualizations pane. So Regular and Youth are the darker color columns; they're grouped together, and all the other segments have the blue columns. So now when you're looking at that column chart, you're actually analyzing the data in a different way because of the groupings. Go ahead and save your Sales and Marketing sample file. Grouping is typically performed on data fields. Our next topic is about binning, which is performed on numeric fields. We're going to start this by creating a new chart on a new page. Let's go ahead and click the plus sign to the right of Growth Opportunities, and we want to select the clustered column chart visualization in the visualizations pane, and I'm going to go ahead and expand the size of that chart; we're not going for exact here, and I'm going to name that new page Binning, just so you have a reference later for what we did on what page in here. If you want to, you can go to the Growth Opportunities page and put "/Grouping" after the name, so you'll be able to find your way back to the page where we did the grouping on. So we're going to build this clustered column chart; um, we are going to—in the field pane—we're going to expand the Geo table, and we're going to drag Region—make sure your chart is selected here—we're going to drag Region to Axis in the visualization pane; we're going to expand the Sales Fact table, and we're going to grab the Sales Dollars and put that in the Values field in the visualization pane. So now we want to create bins for years. Binning is very similar to grouping, except you don't do it on the visualization; you do it from the Fields pane, and we're going to expand—make sure your Date table is expanded in the Fields pane—and we're going to right-click on Year in the Date table and choose New group. So because it's a numeric field, this screen looks different; it has Group Type as a Bin, and the Bin Type is Size of Bins; you only have two choices there for Bin Type: Size of Bins and Number of Bins. We're going to leave it on Size, so this is kind of like—bending is kind of like—grouping certain amount of Year fields together in one group, and we decide we want our bins to be

A size of five, which represents about five years. Go ahead and click OK after you change the bin size to five. And just like a group, it creates a new field in the fields pane. So in your date table, you have your year bins. I actually have two of them in there; I'm going to get rid of one for some reason, but you have your year bins that you just created. And what we're going to do is we're going to drag year bins to the legend for this visualization. And so now you'll see the legend, right? The bins have coloration: 1995, then the next one is 2000, the next one is 2005, and in 2010. Each bin is consisting of five years. So when I hover over any column on the chart, right, it's telling me—if I hover over the green color, it's telling me it's in the 2010 bin; the orange color is in the 2005 bin, so on and so forth. So binning is like grouping, but grouping different numeric data together; in our case, we're grouping by five years. You will have noticed that both the groups and the bins go into the legend of a visualization. Go ahead and save your file.

Our next topic is drill down and up on a visualization. In order for you to be able to drill down and up on a visualization, the visualization must contain a hierarchy. A hierarchy is a container of sorts for related fields. So the first thing we're going to do is we're going to make a duplicate of our binning page. So I'm going to right-click on the binning page tab, and I'm going to choose duplicate page. I'm going to rename the duplicate of binning to drill down/up, which is the name of the feature. And the reason why we did this is because we're going to create another column chart that's very similar to this one. So instead of starting from scratch, we're going to just modify this one. So I'm going to select the column chart, and I can see that region is in the axis, the year bins is in Legend, and sales dollars is in values. Well, we're going to create a hierarchy first of the region field, and we want it to include the region and the state. So in your Fields pane, in your Geo table, what you're going to do is right-click on region and choose create hierarchy from the shortcut menu. So now underneath the region field, you have region hierarchy in your Fields Pane, and you can expand that, and you'll see that it only contains region because that's the field that we base the hierarchy on. We also want to include the State field in that hierarchy, so we're going to right-click in the fields pane on the State field and choose add to hierarchy, and then select region hierarchy. So now we have a hierarchical field, which will allow us to use the drill down and up feature in your visualizations pane. Do the X to the right of region in the access box to get rid of it, and we're going to drag the region hierarchy field to the access box instead. Now a couple of things happened first, though, before I point them out. Go ahead and save your file.

Now you have additional buttons on your visualization; you'll see three of them in the upper left corner of the visualization, and you'll see another one that just looks like a down arrow over to the right. So when you're using the drill down/up feature, you have to enable it on the visualization, and you enable it by using the down arrow button that's on the right side—the upper right side—of the visualization. When you hover over that button, the screen tip will tell you; it says, "Click to turn on drill down." So we're going to just click that button, and it enabled the feature. You can tell the feature is enabled because that button now has like a black circle around it. Let's talk about the three buttons that are on the left side, above your visualization, and the upper left-hand corner of the visualization. You have three other buttons, and those three buttons control how you drill down and up on your visualization. So these three buttons right here do different things. The first one, if you hover over it, it looks like an up arrow, and it's currently dimmed out; it's the drill up button. We're at the top level in our visualization; we're showing the regions right here: East, Central, and West, and we have our year bin, so we have that going on as well. The next button, it has the double arrow, and that one is your drill down button; the double down arrows is your drill down button. So we're going to go ahead and click that button. Since state is the next level in our hierarchy, we are at the lowest level of the data because we only have two things in our hierarchy: region and state. So when I hover over that dimmed-out double down arrow button, it says, "You're at the lowest level of your data." If I want to get back to region, I would use the up arrow, which is now enabled, which says drill up. So I'm going to use that, and now I'm back at the highest level, which is region. So by going through the different levels, you're able to analyze your data in different ways. Right now we're analyzing it by region, but we have a third button up there; we actually have a third button on the upper left-hand corner that's part of this feature set, and that third button, if you hover over it, it looks like an upside-down pitchfork to me, but if you hover over it, it says, "Expand all down one level in the hierarchy." When I click that button, now I'm seeing the region and the state, so it's combining things; it's another way of analyzing your data. I'm gonna go back to the up arrow button, the drill up button, and I'm back to my highest level, which is region. So the drill down and up feature requires a hierarchical field that you can utilize in the visualization, and just by having a hierarchy in your visualization, it will give you all the drill down and up controls on your visualization. Go ahead and save your file.

At this point, we decide that we want City to be in a hierarchy as well. So in the fields pane in the Geo table, I'm going to just right-click on City, add to hierarchy, region hierarchy, and it updates. So now we'll have another level of drilling down. The feature is still enabled, so now I'm going to do my drill down button, and it's going to take me to the state level, and I should be able to drill down again to get to the city level, and it's not letting me drill down to that level. So what it did is it added City to our hierarchy, which we can clearly see in the fields pane, but we need to get the city to be in the access box as well. So what I'm going to do is I'm just going to uncheck region hierarchy, and then I'm going to drag it back into the access box. So now we have region, state, and city in the access box. I'm going to go back to enable—to turn on the drill down feature—so on the right side, and now I'm going to use my double arrows on the left side to drill down. Now I'm looking at the state again; drill down again, and I'm looking at City information, and it's overwhelming the chart, which is why we now have a scroll bar in there. I'm going to use the drill up button to drill all the way back up to the top level, which is region, and then I'm going to use my upside-down pitchfork to expand all down one level in the hierarchy, and so we're seeing the region and the state. And now that we have another field in the hierarchy, we can click the pitchfork again, and now I'm seeing the region, the state, and the City. Go ahead and do your drill up till you get back to just region and save your file.

Our last advanced analytic technique that we're going to get into now, before we get into artificial intelligence visuals, is Analyze. It's on every single report page that you have in Power BI Desktop. And to get to the feature, I'm going to still be working on a drill down/up page right now. I'm going to just select that visualization, and I'm going to right-click in a blank area of it, and I'm going to hover over Analyze, and it says, "Find where this distribution is different." Go ahead and click on that, and it will bring up a bunch of analysis information that Power BI just scanned all the data and came up with. And so it's saying, "Here are the filters that cause the distribution of sales dollars by region to change the most." So California has 14 percent of the records, Texas 6.5 percent of the records, and Florida 6 percent of the records, and those three most affect the distribution. So it's showing you California here, right, and there's a tab for Texas right there, so I can see that that's in a different region, and in Florida, which is in the east region. And notice at the bottom, it says it's comparing proportions, which it's doing now. There is a scroll bar to the right of that, and I can see other analysis information that it gave me. So now it's looking at category; rural has 36.6 percent of the record, so on and so forth. I can keep going down, and there it is by segment; it did an analysis by segment. There is a calculated column; manufacturer is Van Arsdell, so that's coming up there, and so no 77 percent of the records don't have that manufacturer is what that's telling you. But this is just Power BI looking at the data, analyzing it, and giving you these tiles with the analysis. Now let's say I want to keep a tile; I'm going to just scroll back up to the top, and I want to keep this first tile, and it's upper right; I'm going to click the plus sign to add it to this page. Now there's also a thumbs up and a thumbs down; the Power BI people at Microsoft are really committed to listening to end users, so if these analyses are not good for you, you can give it a thumbs down and say this is of no use, or you—if you really like it—you can give it positive feedback. I'm going to go ahead and click the plus sign so I add it to this page, and then I'm going to click on a blank area of the page, and now I'm going to resize my column chart and then resize the analysis that came in at the bottom; I'm going to just make that a little bit taller so I can see it. So that's a built-in feature in Power BI Desktop; it's the Analyze feature, and you get it by right-clicking on a blank area of a visualization.

Now we're going to create an artificial intelligence visual, and there are two ways you can access them in the desktop. One way is from using the Insert tab of the ribbon, and the other way is from the visualizations pane. I'm going to go ahead and click on the Insert tab, and you'll see there is a group called AI visuals. So you were already introduced to Q&A in module 8, and we're going to focus on creating a key influencers visual. Now I did a little bit of pre-work; I created a new page, named it key influencers; I also created three more pages for upcoming stuff, but you don't have to do those now. Just create a new page called key influencers for right now, and then we're going to go up and click the key influencers icon on the Insert tab. We want to expand the framework so it fills the canvas. And when we look in the visualizations pane, there are three fields for it: Analyze, Explained by, and optionally Expand by. We're going to use something that's in the sales fact table to analyze, and that is going to be the sum of Revenue. So as soon as we do that, you'll notice that it plays two tabs on your visualization: key influencers, which is the default tab, and then top segments, and it gives you what influences sum of Revenue to increase, and if you do the drop down, you'll see decrease. We will go over this in its entirety once we're completed the visualization. We're going to end up adding three fields from the product table for the Explained by field. So I'm just collapsing sales fact, expanding product, and the first field we want to explain by is manufacturer. So I'm going to just drag it and drop it in there. Now your visualization says, "No influences found; try adding some more fields in to explain by." So we are—we're going to add the product field underneath manufacturer and explain by, and your visual updated a bit more. Again, we'll review it when we're done. We have one more field that we want to drag underneath product and explain by, and that is the category field. So let's review this visualization. Right now we're looking at what influences the sum of Revenue to increase. The influences are when the manufacturer is one named Van Arsdale and when the category is Urban, and it's showing you the average of the sum of Revenue increases by; so it gives you the number there on the right side. You have a chart, a column chart that's built; it tells you the sum of Revenue is more likely to increase when the manufacturer is Van Arsdale than otherwise, on average, and it's showing you the average; that's the red dash line, excluding selected, and it gives you a value there, and you're seeing Van Arsdale is the tallest column. Now at the bottom there's a check box; it says, "Only show values that are influencers," and we had no change to our chart when we clicked on that. The other thing you can do with this type of visualization is you can hover over the bubble—the 11.5M bubble there—and it gives you more detailed information, basically just in text form. This influencer contains approximately 12.61 percent of the data. When I click on that, it makes the chart disappear, and then when I click on it again, the chart reappears. So let's go and see what it looks like when we change the drop down to decrease. So now you have the same chart but different data, and it's showing when the average of sum of Revenue decreases by, and it's the other manufacturers and one category is mixed in there, yeah, and you have the information also displaying in the chart, and it gives you the baseline average. Let's go to the top segments tab. When is the sum of Revenue more likely to be low? And you have the other option is high. We found five segments and ranked them by averages sum of Revenue and population size. You can select a segment to see more details. So I'm going to click on the bubble for segment one, and it opens up more information underneath it. So you're getting the manufacturer is Palmum, and then it gives you more text and graphic detail. I can click on another one, and it updates the bottom half, and then I'm going to do the drop down next to low and change it to high. And when I change it to high here, it's only showing the highest segment. I'm going to go back to key influencers and go ahead and save your file.

Our next lesson in module 10 is creating an animated scatter chart. Let's go ahead and click the plus sign, and we're going to name the page scatter chart. Again, this file will be a great reference file for you after you complete this video course. So what we're going to do in the visualization pane, you're going to locate the scatter chart visualization and click on it. Let's resize it so it takes up like the upper half of the paint; I'll make it a little bit bigger than that, make it as wide as the canvas, and now we'll start adding fields to populate this scatter chart. So you notice they have several different fields for a scatter chart. We're going to start at the top in the field in the visualizations pane and work our way down. So we are going to want to expand the product table if necessary, and we're going to use the category field for details on the scatter chart. We're going to add our segment groups field to the legend, and from the sales fact table, we're going to want Total units year to date. So I'm going to expand that table so I can see the full field name, and we're going to grab total units year to date and drag it to the x-axis field. We still have more fields to add, so we're going to use the size field here, and we're going to use total units from the sales facts table in the size field, and one more for the play Axis. We're going to expand the date table, and we want to grab the year field—not the year bins, just the regular year field—and put it in play Axis. So now you'll see your scatter chart. Let's go ahead and save our file. Scatter charts in Power BI come with a play Axis. We added year to the play access field, so there is a play button in the bottom left corner of your scatter chart, and you can click that, and you'll see how the scatter chart is animating based on the year. In the upper right-hand corner of the chart, it's telling you what the current year is at any given point in time. Press play again and take a moment to absorb the animation, and you see the current year again in the upper right-hand corner of the scatter chart. It's a really cool feature. So far in this module, we haven't done any formatting on any of the visualizations we created; we'll get to that at the end of the module, which is two lessons away from being able to format all of the charts we are creating during this module. Right now we're going to use a visual to forecast values. Let's go ahead and create a new page and name it forecast. Currently in Power BI, the only built-in visual that allows for forecasting is the line chart. So in your visualizations pane, find and select the line chart. We'll go ahead and make the chart about the width of the canvas and slightly taller, and we can always size it after we complete building it. From the date table, we're going to add the date hierarchy to the axis box, and your visualizations pane; add sales dollars from the sales fact table to the values field, and we're not going to add any more fields at this time. You'll see the line chart is showing the years of data, and when you hover over a data point, it gives you the sales dollar value. So far in this course, we've used the fields well—which we've been using now to add fields to our chart—we've used the format well, which is the paint roller, and again, you'll get to go back there at the end of this module, and we haven't used the analytics well. We're going to click on the analytics icon right underneath your visualizations, and it opens a whole another set of categories. One of those—the second one from the bottom—is forecast. Go ahead and expand the forecast category, and we're going to click the plus sign for the add button so we can add a forecast to this visualization. Now there's defaults that are already filled out, so you actually see the gray shaded forecast area on the line chart; it's forecasting by default 10 points, which for our purposes and our data equals 10 years because we've accessed formatting. We can do the drop-down arrow next to points, and you'll see the rest of the date hierarchy as well as the time hierarchy that's within it. We're going to choose years; nothing's going to change on our chart; we just decide that we want the forecast length to say 10 years. Now we're going to tell it to give us our forecast but ignore the last two years, so it will use the data set for the forecast except the last two years, and that's the next setting down, so ignore last in that box; we're going to change 0 to a 2, and you don't notice anything changing on the chart; you need to scroll down, and you'll see an apply link at the bottom of those categories. Go ahead and click apply, and now you'll see the change on the chart. The green line is representing actual data, and the black line is representing forecasting data. So we told it to ignore the last two years, and that's why you have that green line extending into the gray area. So a forecast is an estimate. The next category down is confidence interval; it defaults to 95 percent. The confidence interval tells you more than just the possible range around the estimate; it also tells you about how stable the estimate is. Let's see what happens if we change it to 75 percent. There's a drop-down arrow, and you can select 75 percent and click the apply link again. So it narrows the forecast; it's less confident, and it's looking at less of a range of data. The next category for forecasting...

Is seasonality? It defaults to Auto. Points: Seasonality refers to predictable changes that occur over a one-year period in a business or economy based on the seasons, including calendar or commercial seasons. So we want it to look within a five-year cycle of our data. We're going to change the seasonality to Five Points. And again, you're going to click the apply link to make that change show up on the chart. So now it's really looking at a five-year cycle within the data set, and the shape of the gray shaded area has changed. Go ahead and save your file now.

If you scroll down farther down, underneath forecasts, you'll see some formatting options there. The color option will change the shaded area as well as the color of the forecasting line. I'm going to do the color drop down, and I'm going to choose an orange color—an orange's color—and you can see that the forecasting line is orange and the shaded area in the back, which is showing what is called the confidence band, right, is also a lighter shade of that orange. If you look at your line style drop down, the line is solid, but you can make it dashed or dotted if you'd like. The forecasting line; the confidence band style is set to fill, so you have that orange—in my case, orange—filled background. I'm going to do the drop down and select none there, and it goes away. I actually like having the confidence band, so I'm going to do the drop down again and choose fill again. And if I want that color, that orange band's color, to be deeper, I can drag transparency to the left so it's not quite as transparent. And when I let go of the slider, you'll see that it deepened the color.

So currently, the built-in line chart visualization is the only one that allows for analytics and, in particular, the forecasting ability. Go ahead and save your file and create a new page, so we're set up for our next and final lesson.

Our last lesson in this module is creating a custom analytics visual. In addition to the visualizations that are built in, you can add custom visualizations in Power BI, that's pending permissions from your admin. And the way to do it is if you hover over the ellipsis button to the right of the last visual, you'll see it says get more visuals. Go ahead and click on it and choose get more visuals. It will take you into the AppSource, which is the tab that it's on by default when you go in here. You also have a My Organization tab. Let's click on that tab. So if your Power BI administrator has added any visuals for the organization, they would be showing on this tab. If not, and if you have permissions, you'll be able to grab some custom visualizations from AppSource. So let's go back to that tab. You'll notice that there's a search box and there are categories. It defaults to the All category. And if you know the name of the visual that you're looking for, you can use the search box. Let's go to the Advanced Analytics category. And if you start scrolling down, you'll see there's plenty of different custom visualizations in here. Some of them will say, in blue underneath them, but they may require additional purchase. Instead of searching through this list, I know the name of the visualization we're looking for. Let's go to the search box and just type violin, as in the musical instrument, and press Enter. And it comes up with the violin plot visualization. It's an advanced analytics visualization, and it's used to visualize the distribution of your data. We're going to click the Add button to the right of it. After a moment, you'll get a message letting you know that it imported the visual successfully. You can click OK to get rid of that message.

Now, when you look in your visualizations pane underneath the Get More Visuals icon, you'll see the little violin, and that's the icon for violin plot. It's underneath the visualizations pane, which in Power BI Desktop, that means that you can use it in this file. But if you close this file and open a new instance of Power BI Desktop, it will not be in your visualizations pane, and you would have to add it again. The alternative is to pin it to your visualizations pane. So when you find a custom visual that you're going to want to use over multiple files, pin it to your visualizations pane. And you can do that by right-clicking the violin plot and choose Pin to visualizations pane. It will move its position, and it will end up being the last visual in your visualizations pane. Go ahead and click the violin plot visual, so we can build this visualization. I'm going to expand the framework so it fills the width of the canvas, and I'll make it a little bit taller as well.

Now, for this visual, we have three fields that we can fill in: sampling, measure, data, and category. The category field can be optional, and you'll see how that works as we start building this. So from the Products table, we want to use the Segment field for sampling, and notice it gives you a message: Please ensure that you have added data to the sampling and measure data fields. You can also supply an optional category to plot multiple violins within your data set. We're going to use all three fields. So the next field is measure data, and from the Sales Fact table, we're going to grab the Sales Dollar field for measure data. And then for the category, we're going to go back to the Product table and use the Category field for category. So that caused us—by using the Category field—that caused us to have multiple violins. They're broken down by category; they have different shapes. If you notice the legend in the upper left-hand corner. So your sales dollars are—in my case, aqua-colored—parts of the violin. You'll see the median value is the white line in the violin, and the mean value is the circle that's in the violin, and sometimes they're in the same position. So if you hover over your different violins, you'll see that it gives you the name of the category, the number of samples, the maximum, minimum, median, mean, and standard deviation values. So a violin plot chart shows the distribution of your data. Go ahead and name that page Violin and save your file. Now would be a good time for you to pause this video if you'd like, so you can go back and apply some formatting options to the visualizations we created in this module, starting with the Binning page.

In Module 10, you got to explore Advanced Analytics by using grouping, binning, drill down and up, and analyze features. All of them give you different ways of looking at your data to analyze it in different ways. You were able to create a Time Series analysis with the animated scatter chart, that was really kind of cool. We use some AI—artificial intelligence—visuals, some of which identified outliers in our data, and that would include the Q&A, our decomposition tree, the Key Influencers visual, and how to summarize on a report. All of those are really cool features that give you different insights into your data. And we ended up by using the advanced analytics custom visual, the violin plot, which shows the distribution of data.

Hi everyone, I'm Trish Connor Cato, and I'd like to welcome you to Microsoft Power BI. We've already created workspaces in previous modules. Now we're going to focus on how to manage them. That will include how to share content, including reports and dashboards, and how to distribute an app. You'll also learn how to assign data set roles to your workspaces and the data that's in it. We have three lessons in this module: Sharing and Managing Assets, Mapping Security Principles to Data Set Roles, and Publishing an App. We're going to be conducting this module in Service. So go ahead and get yourself into Service. We're going to get started by sharing assets. By assets, I mean dashboards or reports. So I'm already back on my Supplier Quality Analysis sample dashboard, and right up at the top, I'm going to click on Share, and it opens the shared dashboard dialog. You can enter multiple email addresses in here, or you can enter groups in here. And the thing about it is if you use an email that's outside of your organization, none of those recipients would be able to re-share the dashboard. So usually, before I put in email address or addresses, I check the settings. These are the defaults. So by default, recipients would be able to reshare your dashboard. If you want someone to not be able to do that, uncheck the box. They can also build content with the data set associated with this dashboard. And the other option is to send an email notification. So again, if you use an email address outside of your organization, recipients would not be able to reshare your dashboard. Make your choices with the check box. Go ahead and enter an email address and click the Grant Access button at the bottom. You'll get a pop-up that says Success: Access has been granted.

Now, what else can we do with this? Let's say you accidentally gave someone privileges to reshare your dashboard, and you want to revoke those privileges. You simply go back to Share, and in the upper right-hand corner, you'll see the More Options ellipsis. Go ahead and click it. From here, you can manage permissions, which we're going to do in a second, or you can copy a dashboard link that you want to send to someone else. Go ahead and click Manage Permissions. So this will show you a list of everyone that has permissions that you shared your dashboard with, and in the lower right-hand corner, go ahead and click the Advanced link. I've blocked out the email addresses for privacy reasons, but on the left side, you'll see the related content that was shared. So if you expand Reports, you'll see any reports that went along with this dashboard, and we gave them access to the underlying data by sharing the data set with them. You'll see yourself as the owner of this dashboard, and you'll see their privileges that you granted the person you shared it with. So the ability to read and reshare; to the right of that is the vertical ellipsis, and when you click on that, you'll see that you can remove just the reshare privilege, or you can remove the access totally.

I've moved back to my workspace, and now I'm going to show you how you can share a report and the options that go along with it. So I'm hovering over the Supplier Quality Analysis report. This is the Power BI report; remember, it has the column chart icon versus a paginated report, which has the page icon. So I'm hovering over the Power BI report, and you'll see the share arrow to the right of it. I'm going to go ahead and click that arrow. Very similar to the dashboard, you can enter a name or an email address here, multiple email addresses, or you can enter in groups. You can add an optional message. It defaults to sending a link, and people in your organization with the link can view and share. If you click on that, you can say people with existing access or specific people, and then the settings underneath control what rights they have. They can reshare the report or not; that is a default, and/or they can build content with the data associated with the report, so they get the data set as well. I'm gonna leave it on People in your organization, and I'm going to click Apply on this screen. Go ahead and enter an email address, and then you're going to click the Send button. Alternatively, you could copy a link; you could access Outlook to share the link or Teams to do so. I've already cleared the pop-up that let me know the link was successfully shared, and so I'm gonna do just like we did with the dashboard for that report. I'm going to go back to Share, and in the upper right-hand corner, I'm going to access More Options and go to Manage Permissions. It shows you who you shared it with, just like the dashboard. You have another access to another opportunity to copy the link up here if you want to distribute it to other people. And just like before, you can click on Advanced in the lower right-hand corner, and you'll see that, in my case, this link has been shared with people in my organization, and they have the ability to read and reshare the related content. Just like with the dashboard, it's on the side, and you can look through those options and then just navigate back to your workspace.

Another way to distribute a report is to copy it, and you can put it into another workspace and then give access to that workspace. And we're going to do that now. We're going to copy the Supplier Quality Analysis report by going to its More Options button and choosing Save a copy. The Save a copy of this report panel opens on the right side. We're going to leave it with the same name, and then you get to select a destination workspace that you have access to. I'm going to go to a workspace called Training Workspace and then choose Save. So it gives me a pop-up where I could go to that report, and I'm going to click Go to report on the pop-up. And notice at the top it tells me I'm in the Training Workspace. So now, if I wanted to give someone else or other people access to this workspace, this is how you do that. You have to go to your Workspaces option on your navigation bar, and you're going to hover over the workspace that you want to give somebody access to. In this case, it's my Training Workspace, and I'm going to go to the More button to the right of it, and I'm going to choose Workspace Access. Here you have different roles that you can assign, and before we assign anything, let's review those roles. There are four roles that you can use: Admin, Member, Contributor, and Viewer. I have several tables here that show you the capabilities for each of these roles. So Members can add members with lower permissions; they can publish, unpublish, and change app permissions. Notice everything else on the screen only the Admin can do. And again, you'll have a copy of this PowerPoint in the video description, so you can go over these capabilities by these four roles at your leisure. So now, Admins and Members can update apps, share items and apps, allow others to reshare, they can feature apps on colleagues' home pages if they have permissions to do so, and manage data set permissions. Contributors can only do these two items if allowed, and the Viewer can only perform this item if they're allowed. So again, I would say that you can spend some time at your leisure going through this table about the capabilities for the workspace roles in Power BI. There's also a link where it says Add Admins, Members, or Contributors where you can learn more about those roles. So on this screen, you're going to enter an email address, you're going to choose the level that they have, and then you're going to do the Add button and close this screen. Go ahead and perform that, and then check out the actions ellipses to the right of each user and look at the options there. You can change their permission level from that actions button, and you can also remove somebody. Once you're done reviewing that, go ahead and close the panel.

The last lesson in this module is how to create and publish an app. An app—and it can be configured with multiple dashboards and multiple reports, as you'll see—I want to go back to my Power BI Video workspace. So I'm going to use my Workspaces icon on the navigation pane and get back to Power BI Video. When I scroll across in this workspace, I'll see that dashboards and reports have include an app column, and they all default to Yes. So when I want to create an app, it won't include a data set, just dashboards and reports. In the upper right-hand corner, you can go ahead and click on Create app, and so everything—if you do it like this—everything that is toggled to be in an app will be in this app. So what you want to do first before going in here is you want to untoggle the things that you don't want to be in the app, the dashboards and reports. So we're going to cancel this screen, and on your workspace screen, you're going to scroll to the right. The only thing that we want to put in our app is the Supplier Quality Analysis data. So for everything other than that, I'm gonna untoggle this Include an app; I'm going to switch these to No, and I'm going to keep scrolling down and make sure that I get everything that I want in here. So there's my Supplier Quality Analysis; it's doing both reports, the paginated report and the Power BI report, and it has the dashboard selected. So I'm going to leave those three on Yes, and now I'm going to go back into Create an app. My app name is going to be Supplier Quality Analysis. Description: I'll just put Includes a dashboard, report, and a paginated report. You could put a site in here—um—where users can find help for something like this. I might put in a Microsoft site about creating an app, and you could do that through a URL if you want. You can upload a logo for your app; you can give your app a theme color; and in contact information, I usually have Show app publisher to default, but you can show item contacts from the workspace or specific individuals or groups. Up at the top, there are two other tabs. There's a Navigation tab; it shows you the dashboard link, right? If you want to change the order of things that are going to be in the app, you can do that on the left side. If you get to this point and you decide that you don't want to show something in the app, you can hide it here. There's an Advanced section where you can give it the default navigation with, and then there's a Permissions tab where you can give access to your entire organization or specific individuals or groups. So I'll have you enter an email address there. When we're almost done with this, everything that people that have the app can do is these check marks—check marks—there. So they can connect to the app's underlying data sets using the build permission if you want them to; they can make copies of the reports in the app; and you can also allow them to share the app and their—and the app's underlying data sets. So permissions—permissions—this is very similar to the dashboard and report permissions. You can also have it install this app automatically for those who have permission when they select it, or they can install it themselves. So what I'm going to do here is I'm going to go ahead and put in an email address, and then I'm going to click the Publish app button. When you click Publish app, it lets you know that it can take within five to ten minutes or up to a day, and then it published. So it didn't take that long, and I can go to the app from that screen.

And I can see that it included what I wanted it to include. So this is the Power BI report here, and then this one is the paginated report that we created in the previous module. You have some options at the top to file; you have the File drop down, so you can edit in Power BI Report Builder. Because we're on the paginated report, you can view. So these are the same things when we—we're looking at the paginated report outside of the app. When I go back to the dashboard, I have some of the same features there that you have with other dashboards, and you have a high-definition navigation pane here. So if you want to collapse that, right, and then you can expand it again, and at the bottom of it, you can go back, so it takes you back to your home page. At this point, on your home page, you have recents, things that have been shared with you, and you have My apps. So if I wanted to get back to that app, I could—I can make the app a favorite, just like we can make different dashboards favorites, and it has a More Options button where I can open the app or hide it from this screen. In this module, we focused on workspace collaboration. You learned how to share dashboards and reports. You also learned how to copy a report and put it in another workspace and then give people permissions to that workspace through workspace roles. We ended up by distributing an app, which contained dashboards and reports, and we learned how to toggle off the ones that—in the workspace—that we don't want to include in the app.

The final module in this course is how to manage data sets in Power BI. Specifically, you'll learn how to set parameters and how to use the data set refresh options. We're going to be using the sample Superstore OD desktop file that was created in Module 2 for these lessons. Our first lesson will involve how to create a parameter; we do that in Power Query Editor. Then we'll publish the…

File to the service, and we'll learn how to view the parameters and change them from within the service. And then we'll work on how to refresh your data set and then your visualizations.

Parameter is really an efficiency feature in Power BI. It serves to easily store and manage a value that can be reused. They give you the flexibility to change the output of your queries depending on their value, and they can be used for changing the argument values for particular transforms; for example, filtering and data source functions. Also, they can be used as inputs and custom functions. You may want to go ahead and get the sample Superstore OD Power BI Desktop file open now.

A parameter is really an efficiency feature in Power BI Desktop; it's actually in Power Query Editor that you set it up. It serves to easily store and manage a value that can be reused, especially when you're filtering. Parameters give you the flexibility to dynamically change the output of your queries depending on their value and can be used for changing the argument values for particular transforms, like filtering and data source functions, as well as for inputs and custom functions. As mentioned, we set up parameters in Power Query Editor.

So, on the Home tab of the ribbon in Power BI Desktop, you're going to go ahead and click the Transform Data button to open the Power Query Editor window. A reminder: in here, your queries are on the left side, and on the Home tab of the ribbon in here, you have a Parameters group, and there is the Manage Parameters icon. Go ahead and click that icon. The Manage Parameters box opens, and it's very tiny in here, but we want to create a new parameter, and a new link is right underneath the Manage Parameters heading, so go ahead and click on New.

You get to give your parameter a name; so the parameter that we're going to create is going to be a parameter of the regions that are in the Orders query. So we're going to just name the parameter Regions. We're not going to give it a description. We're going to go down to Type, and we're going to select Text. For Suggested Values, we're going to supply a list of values, and you're going to click next to Row 1 and type East. When you click right underneath East, it gives you Row 2; you're going to type Central. For the next row, you're going to type South, and the following, and the last one we're going to type West. So after you type West, click on the next line, even though we're not going to add anything there. Once you click on the next line, then the previous entry is accepted. So when we go to the Default Value drop-down, we're going to default it to East, and we're going to use the same for Current Value, and now we're going to click OK.

In your Queries pane on the left, you'll see the Regions parameter and its default value of East. Notice the difference in icon for a parameter. If you were to click Manage Parameter, it would just open the same dialog box where we created it, so if you needed to change anything, you could do that. What we need to do now is go to the View tab of the ribbon, so we can tell Power BI to allow parameters to be used. In the Parameters group, you're going to check Always Allow. And now let's go over to our Orders table and use our parameter.

So when we get to Orders, we're going to scroll to the right until we see the Region field, and I'm going to show you how to use the parameter that we just created to filter that field. We're going to do the auto-filter drop-down next to Region, and if it says List may be incomplete, reminder you want to load more so you see everything that's in that list, and we see the regions there. We're going to hover over Text filters and choose Equals. Where it says Keep rows where Region equals, in between Equals and Enter or select the value, you'll see an ABC icon that represents text. We're going to click on that icon, and we're going to choose Parameter, and notice it puts the name of our parameter, Regions, in there. So we told it to default to the East region. When we click OK, we'll see that it's now filtered for East, but what's cool about this is once we publish this to the service, we will be able to change it in the service.

In order to be able to change your parameter in a service, the data type has to be either Text or Decimal Number. So we're good because all of our regions are the Text data type. What we're going to do now is go back to the Home tab and click our Close and Apply button to make sure those changes get down to the desktop. And when we're done with this, we're going to go ahead and click the last button on the Home tab, which is Publish. We're going to republish this to the service. It's going to prompt you to save your changes, and I'm going to put it in my Power BI video workspace, so I've selected that one. It lets you know that we've already published this, so it says Replacing this data set may impact one report in One dashboard, and we're going to click on Replace, and it lets you know when it's successfully finished publishing it to the service.

Go ahead and click the link to open Sample Superstore OD in Power BI right from your success pop-up. It opens in Report view in the service, and what we want to do is go up to More options and Edit. When we get into the report, we're going to do the plus sign for a new page, and we're not going to select a visualization up front. We're going to expand the Orders table in the Fields pane, and from the Orders table, we're going to want to drag—let's see what do we want to drag into this—we're going to drag Region directly onto the canvas. When you do that without selecting a visualization, it creates a table visualization, and we see based on our parameter that it's showing just the East region, our default region. We're also going to drag Sales into the framework of the table, so now we're seeing the total sales for the East region, and we're going to go up to File and save this report.

Once the report has been saved, you're going to navigate to your workspace where the report resides. So at this point, we're in a service, and we decide that we want to change our parameter; we want to change it from East to West. You can do that in the service, and the way you do that is you're going to hover over the Sample Superstore OD data set and go to its More options, and when you select that, you'll see Settings on the list, and you're going to click on Settings. So in Settings, you have a category called Parameters, and you're going to go ahead and open it, and you can see that it's set to our default of the East, which also showed up on the report that we created. This is where it's useful as an efficiency tool; we can change this. So I'm going to just double-click East, and I'm going to type West because it's a Text data type; we can change it. We can only change Text and Decimal Number data types in the service for our parameters. Once I change it to West, I'm going to Apply. So now it updates, and it's set to West. I'm going to go back into my workspace, and I want to go back to the Sample Superstore report, the OD one, and notice it still says East on the report. So what you need to do here is you need to do two refreshes.

I'm going to go back to my workspace, and I'm going to hover over the Sample Superstore OD data set, and you'll see the Refresh Now button. I'm going to click it; it's the circular arrow, and then I'm going to go back to the report, and the report is still showing East at this point. And now in the upper right-hand corner, close to where your login information is, your profile information, you'll see another circular arrow; this one is to refresh the visuals. So we just updated the data model by refreshing it by refreshing the data set. Now we're going to click the refresh button for the visual, and it changes to West. So that's how it's an efficiency feature; you set it up in Power Query Editor, and then you use it in a report in your service after publishing, and then you can go to the settings for the data set and change the parameter as long as it's a Text or a Decimal Number data type. The type of refresh we did is known as an on-demand refresh; we physically click the refresh button for the data set and then for the visualizations.

Let's go back to our workspace, and we're going to hover over the Sample Superstore OD data set again and go back to More options and back to Settings. You also have the ability to schedule refreshes in Power BI service. So we're going to scroll down underneath Parameters; we're going to go to Scheduled refresh, and we're going to toggle it on. So you can change the refresh frequency daily or weekly. You can say what time you want the refresh to happen, and you can send refresh failure notifications to the data set owner or these contacts. So some people like to refresh like at noon every day, something like that; whatever it is you need to do there is what you can set up, so you don't have to physically come in and do a refresh on demand. When your frequency is set to daily, you have the ability to add multiple times, so you can have it refresh daily multiple times a day. I click the Add another time link, and I'm going to just do the Xbox, the X button rather, to get rid of the additional time. If you change your refresh frequency to weekly, you'll be able to pick the days of the week that you want it to refresh, and you can have it add refresh multiple times a day by adding another time as well there. So instead of doing refreshing on demand a lot of times for a lot of your data sets, you're going to want to refresh them, you know, once or twice a day perhaps, depending on how active that data is.

Let's go back to our workspace so I can take you back to something that was originally covered in Module 2. Hover over Sample Superstore data set, not the OD one, just the plain Sample Superstore one, and go to its More options button and go to Settings. If we go down to Scheduled refresh here, we cannot schedule a refresh. This is the data set that we created from the locally stored Excel file, and remember you can't refresh a locally stored file; it has to be stored in the cloud. That's why the OD1, that represented OneDrive when we created it in a Module 2, it's an Excel file that's stored in Office 365 Excel online, so that one will allow a scheduled refresh; this one won't because it's locally stored. So just like at the desktop level, you can't really refresh the locally stored data; you would have to re-import it into Power BI Desktop, and then if you make changes in the desktop, you would have to republish it to the service; that's the only way to get it refreshed.

So in this final module, you learned about parameters, created them, and you also learned how to change them in the service. You learned how to manage your data sets in terms of looking at how to refresh them, and we did a refresh on demand, and you also learned that you can schedule a refresh for anything but a locally stored Excel file in the service. I have a last challenge exercise for you that you can complete on your own. We've spent some time building visualizations in the Sales and Marketing sample PBIX file, and for your challenge, go ahead and publish this file to the service. Create another workspace in the service first, and then publish the file to your new workspace. Give it a theme, maybe do some quick insights or add Q&A to it, even a video if you'd like. That will be your final challenge. Thank you for attending this video course on Power BI. I'm Trish Connor Cato, and as a reminder, all of the files and additional documentation are in the video description below. For live classes in office ends professional development and private training, visit learnit.com for more details. Please remember to like and subscribe and let us know your thoughts in the comments. Thank you for choosing LearnIt. [Music]