Clean up Excel formulas with the power of Regex Constants

Trish Conner-Cato: Hi everyone. I'm Trish Connor Cato, 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 is 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 the second module, 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 in performance implications of connecting directly to data versus importing it during this module. Module three 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 relationship security and performance. Module five 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 seven introduces you to the fundamental concepts and principles of designing and building a report, including selecting the correct visual. Designing a page layout and applying basic or critical functionality. The important topic of designing for accessibility is also covered with the exception of the first module. All other modules on this slide will be conducted in the Power BI desktop application.

Once we get to module eight, 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'll 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 data sets and Power bi. In this module, you'll learn the

concepts of managing power BI assets, including data sets and workspaces. You will also publish data sets to the Power BI service, then refresh and secure them. The last module in this course is about role level security. It will teach you the steps for implementing and configuring security in Power BI to secure your Power BI asset. Welcome to module one 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 insights 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 analyst 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. 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 and 99 cents 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 $20 a month. 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 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 premier 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 you 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 will 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 wanna 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 re reside separately from the order information on this 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 return status, and we have a user's tab, which contains manager information. I'm gonna 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 gonna get into Power bi. There are many ways to launch it. Like any other application, I'm gonna use my start menu to launch it, so I'm navigating down to my task bar at the bottom of my screen, and in

the lower left hand corner, I'm gonna click on Start. Once the start menu opens, I'm gonna click on any letter that I see, so it collapses all the applications, and I'm gonna click on the letter P. Underneath the letter P, you'll see all of the applications that begin with the letter P. We're gonna 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 gonna do is

sign in. In the middle of the splash screen, you're gonna 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 wanna 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 gonna use the sample superstore cell 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 dialogue box. Navigate to wherever you put the class files for this module. And you should see your sample superstore's file. We're gonna 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 pain 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 gonna put a check mark in front of every table, and as you select a different table to preview, pain 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 gonna 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. And 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'll probably would 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. It's 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 gonna 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 dialogue 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 dot P B I X extension. There are only two extensions

for Power BI Desktop files. The default one is P B I X, which is a Power BI desktop file, and your only other choice would be a dot P B I T, which is a template file. We're gonna leave it on P B I X 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. 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 in 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 pains. My filters, pain 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 pains, so there's the filters, pain, we'll speak more detail on it when we start working on creating visualizations and Power bi. You also

have a visualizations pain on the right side and report view. That's the area where you go to when you wanna 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 pain over there for right now is The Fields Pain. Well, when you look in this fields pain, 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 user's 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, collapse. You can expand the orders table and 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 pain, 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. It, 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 will, 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 the 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 pain. If you'd like to take a moment and look at the fields that are in the returns and the user's table, you'll notice when you expand the user's table that it has

two columns and they're named column one and column two. When you're working with data, you're going to wanna 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 wanna 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 gonna 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 pain 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 pain 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 pain 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 quo will show different things. The last view that we're gonna 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 gonna 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 four, but in the meantime, this view also has the field's pain 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 field's pain. Another thing is there is a search box at the top of the field's pan, 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 pain that you haven't seen before. In this view, this is the property's pain. 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 property's pain 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 to 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 four 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

dataset and Power BI desktop. What we wanna do next is bring in data from an access database. We don't wanna mix the data together with the sample superstore data. So we're gonna 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 North Wind. So what I'm gonna do to start a new instance of Power BI Desktop, what I wanna do is I wanna go up to the top left corner and I wanna 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 and Word or Excel because we are 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 dot pbix Power BI desktop file is listed there. If we're gonna 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 gonna just close

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 wanna 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 keep 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. 90% 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 North Wind. That is in the video description. We're gonna 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 sources than it currently does. So what we're looking for on the left side, I'm gonna click on the other and I'm gonna scroll back up to the top. And you can see this is where you would find web and all kinds of stuff. If you wanna use categories on the left, I'm gonna now click on database and 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 North Wind. I'm gonna go ahead and double click it. So it's gonna 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 object. So what it brings in here is it will bring in any queries and notice the icon for a 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 the database will have temporary tables in them. We don't

wanna bring in the queries, we don't wanna bring in the the temporary tables. I'm gonna 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 North Wind database. We're gonna check the box in front of employees. Order details. We're gonna 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 gonna get a demo of this right now, actually not a demo. You're gonna 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 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 North Wind 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 gonna go down and we're gonna 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' pain. Let's go ahead and save this file. So we're gonna go back up to the left to the Quick access toolbar. First

icon is Save, and we're gonna call it North Wind. 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 North Wind as my file name and I'm gonna 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 gonna 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 gonna show you where to find a setting that

allows Power bi. To do this, we're gonna access the file tab on the ribbon, and when you get there, you're gonna go down to options and settings. You'll 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 gonna click on the options gear year. So what I will say about options in Power BI Desktop is

they come into variety. 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. The the option we're looking for is under current file. So we're gonna go to data load under current file and on the right side of your screen you'll see your

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 check box. 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 gonna be working for working on. If that is your intent to have Power BI help with detecting relationships. I'm gonna go ahead and click the okay button to get out of options. And once we're out of options, we're gonna go ahead and save this North Wind file again by using the save icon on the quick access toolbar. And we're gonna close the file. It's a couple of ways of doing it.

I just usually close the whole window, so I'm gonna travel all the way to the other side of the screen, pass my name and I'm gonna click the X button. If you attempt to close the 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 North Wind opened in its own separate window. Our next task is going to be bringing in data from the internet. We're gonna go ahead and close the sample Superstore file.

I'm gonna 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 gonna 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 gonna 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 gonna click at the top of the screen in the address bar and select the U url. And I'm gonna do control C on my keyboard to copy it. Now I'm gonna minimize, or even close, I'm gonna go ahead and close that website and I'm gonna get the word document off of my screen. Since we closed all of the files in

Power BI desktop, we're gonna have to launch it again. This time I'm gonna launch it because I have it as a button on my task bar. I'm gonna 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 gonna go to the upper right hand corner of the splash screen and close it. We're gonna go back to the get data button on the ribbon, just like we did for

the Access database. And we're gonna use it to access web content. Go ahead and click the button. And when the Get Data Dialogue box opens on the left side, you're gonna 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 dialogue box. Now you wanna switch over to the Word document that has website and additional information in it, and I'm gonna 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 gonna just hold down my control key and click on the hyperlink. It's gonna open a webpage and you see it has some sort of a visualization on it, but most webpages are built using tables. So if we scroll down, we'll eventually run into a table, and that's probably what we're gonna 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 u r url, we're gonna just click

up at the top of the screen and the address bar and select the u r url. Press control C to copy it, and then you're gonna switch back over to PowerVI desktop. We're gonna paste the U R L into the box by using Control V, and then we're gonna click the okay 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, okay, when you paste it in the U R L, 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 gonna 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 pan 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 gonna start selecting tables and I wanna preview them

in table view just to view the data. I do not have to check the checkbox 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. When I click on table two, it has more parts of the data, and the same goes for table three. So I'm gonna 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. Medium household income by State 2021. I'm gonna go ahead and do the load button. In the lower right hand corner, you'll see your load dialogue 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's loaded into Power BI Desktop. You will see the three tables on the right side of your screen in the field's pan. Let's go ahead and save this file and call it median household income. This time I'm gonna go to the file tab ribbon to perform the save still in my working directory. So I'm gonna 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 bought in over on the right in your fields pan,

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 gonna need to be fixed. Let's take a look at the data and data view. So again, that's the second view button on your ribbon, and we're gonna 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 gonna 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 one, two, and three are not very descriptive table names. We're gonna 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, power BI will be able to read that data and the decision that you're gonna need to make in order to optimize performances. Basically this one, do you want to bring in the whole data set into Power bi or do you just wanna 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 gonna 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 gonna 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. Power BI can access that data model. So I'm gonna 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 Excel. 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 gonna 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 detail 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 wanna 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 four. So relationships have already been created in the data model for this

Excel file. Now I'm gonna go ahead and get out of Power Pivot. I'm gonna 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 gonna bring the data from Customer Data Excel file into Power bi. But we're gonna use two different techniques and you'll see the difference as this plays out. The first technique we're gonna use is this simply on the home tab. We're gonna click

Excel workbook and we're gonna 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 gonna do is we're gonna select all of the check boxes and we'll bring all of these tables in or sheets

in and will 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 the data view and look at the data and 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 gonna save this file, so I'm going to just do File New and it's gonna 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 gonna 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 gonna 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 Powerview 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 gonna select that fourth option. And it's gonna take you to your directory and you're gonna 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 gonna go ahead and click start and it will go through the import process. At some point, you'll get the migration completed dialogue, 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 gonna go ahead and click on close. So when you look on the right side of your screen and your fields pain, 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 will 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 and 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 over all 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 gonna 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 gonna 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 gonna grab the sample Superstore

spreadsheet and I'm gonna just move it out to my desktop so it's no longer in the same directory. While the directory is still open, I'm gonna 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 pan. 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 gonna go back to the home tab here, and in the queries group, I'm gonna 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 gonna close that refresh box and then we're gonna go to the file tab and we're gonna 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 in two itself when bringing them in from a database file, or in our case, a Power Pivot file as well. But we're gonna click on data source settings this time. So when the data source setting dialogues 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 cell file doesn't reside in that path anymore. What you're gonna do this time is in the lower left-hand corner, you're gonna 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 verified that it's the right one selected. So where it has that path, I'm gonna click on Browse, and I'm gonna browse to where I put my sample superstore file, which is just on my desktop now, and I'm gonna double click it. So once it has the path, it will be able to continue. Now

go ahead and click okay. And it updates on the data source settings dialogue. So 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 gonna 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 wanna tell it to apply the changes that we just made. So I'm gonna 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 gonna 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. So let's see. If we got rid of this error on the home tab, we're gonna 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 gonna do that

right now. I've minimized my Power BI desktop and I'm gonna grab my sample Superstore Excel file and drag it back into my class file working directory folder. And then I'm gonna bring my Power BI desktop back up. I am going to click the refresh button on the home tab a ribbon, And we get the same error. Some other errors might say Load. This one is saying Refresh because we click the refresh button. I'm gonna 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 gonna 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 gonna okay and close. And remember, you're gonna have to apply to 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 Powerview sheets in it. I'm gonna take a moment to open that file. I

don't have Powerview, so you'll see what it looks like when you open it and you don't have Powerview. 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, maybe 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 Powerview, like I mentioned. And this file contains multiple sheets, just three of them that have the framework for the Powerview report that I can't

see without having to add in. I'm gonna go ahead and close this file and then we'll switch back over to Power bi. In Power bi. I wanna go to File New. Don't want to bring that data from the Power View Reports file into Sample Superstore dataset. So we're launching a new instance. We've done this before. I'm gonna go ahead and close the splash screen because we can't use Get Data to import this data. And we're gonna go to File Import. We're gonna select the fourth choice like we did before, power Query, power pivot, Power View,

and you're gonna double click on that IT spend analysis sample file. We get the same import Excel workbook content dialogue, and we're gonna 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 gonna go ahead and close. This is the era 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 era. So the fix for it, 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 gonna click on the framework of where visualization would be, where the error message is,

and I'm gonna simply press delete on my keyboard. And one, once you know how to build visualizations, you can replace it with something that is supported within Power bi. The last thing we're gonna 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 will get to put into Power BI that's stored on OneDrive Drive. 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 86 17 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 gonna show you what it looks like by going to Report View. You'll learn how to create report visualizations when we get to module seven, but right now, as I hover my mouse over the bar on the report,

It shows me the sales are $2,428 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 gonna change the values of those two sales that we saw in Power BI Desktop. I'm gonna change the 86 18 sale. To 3 86 17, and I'm gonna change the 1527 sale to two hundred and fifteen twenty seven, and then I'm gonna 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 a 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 gonna click the button to refresh. You'll notice that it has the popup 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 and Power BI desktop, it will update the information. But something different occurs in the Power BI Online service, and I'm gonna 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 and 25 cents. When I hover over the data set, I see a refresh button refresh. Now I'm gonna click that button and then I'm gonna 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 in Power BI service, I would have to republish it from the desktop. Now we're gonna 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 dash od 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 gonna go to the more actions button. Hover over open and choose. Open an 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 one drive. I'm gonna click on Copy Path and

then I'm going to just close this file. I don't need to be on one drive in this moment, so I'm gonna switch back over to Power BI desktop because I wanna bring this into a new instance. We're gonna 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 gonna access get data from the splash screen and when the Get Data dialogue box opens on the left side, I'm gonna click on other and then double click web at the top of the

list. So the path is on our clipboard. I'm gonna dorl V to paste it in. And the second tricky wicket you're gonna 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 gonna click okay. The navigator window will open, it's going to be the same three tables, orders, returns, and users that we use 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. It's loading the data to the model and it, if I look over to the right in my fields pain, I have the same three tables. I'm gonna switch to data view. And just as a navigational point, I'm gonna 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 86, 17, and 1527 in terms of sales, we're gonna go to report View, and I'm gonna show you how to build the report, the same one that I had in the locally stored desktop file. So what I'm gonna have you do is over to your right in the visualizations pain, you're gonna click on that very first visualization. It is a stacked bar chart. And then over to the right in your fields list, you're gonna expand your orders table. And I'm gonna 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 seven. And then I'm gonna 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 currents. So in my filters, pain, where it says city is all, I'm going to do the dropdown arrow and I'm gonna just, in the search box, I'm gonna type Kerns, 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 Kearns, 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 and 25 cents. Just like in our regular file. I'm gonna save this file and I'm gonna call it sample superstore dash OD for OneDrive. Now that we saved our file, we're gonna publish it to the Power BI Service. The last icon on the home tab or ribbon is publish. Go ahead

and click it. It will ask you sometimes if you wanna 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, published to Power bi. Select a destination. I have multiple workspaces. The one that we all have in common is my workspace, and I'm gonna 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

eight. 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 gonna just click. Got it. Now at this point, we wanna 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 gonna hover over open and open an app. We're gonna make

the same change in this file, the same two changes that we made in the other file. So I'm in cell W three 30 and I'm gonna change that to 3 86 17, and I'm not gonna go to cell W 3 32 and change that to 2 15 27. 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 gonna wait till it's totally saved and then proceed. So at this point,

I'm gonna 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 gonna go through the dialogue 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 $500. It's now $2,928 and 25 cents. 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 gonna 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 dataset for the OneDrive dataset. I'm gonna go ahead and click the Refresh Now button, and then I'm gonna click on the link to take me to the report. When I get to the report, it's now updated to $2,928 and 25 cents. 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 two, 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 Power Pivot and Powerview 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 three, is about cleaning and transforming your data in Power bi. Hi 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 three 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 user's table and the orders table. We're gonna start by taking a look at the data that is in the user's table. Remember, your view buttons are on the left side of your screen, and the second view button is data view.

I am gonna go ahead and click that button to get into the view. When I get in there, I wanna go over to the fields pain and I want to click on the user's 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 gonna wanna do is rename the table from users to managers. We noticed that column one and column two 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 we'll make that change as well. The final change we're gonna make is we decide we wanna capture the manager's 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 user's table. I'm in the fields pain and I'm gonna right click on it. Choose the rename options from the shortcut menu, and I'm gonna just type managers and press enter for it. To accept the change, notice the

organizational change in the fields pain. 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 gonna go up to the home tab of the ribbon. in the queries group, we're gonna 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 gonna click on managers, so that's our table of interest right now. A few other things I wanna 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 to 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 one and column two. The most efficient way to create a column to hold the manager last names is to duplicate the manager column. I'm gonna right click on the manager column

heading and I'm gonna choose duplicate column from the shortcut menu. Now I have my original manager column and manager copy column. We're gonna rename these columns. I'm gonna double click manager and I'm gonna edit it. So it says Manager first, and I'm gonna 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 manager's table is to replace the first names with the last names in the manager. Last column, we have to do these individually, power Query Editor.

So I'm gonna click the first Manager last, which is Chris. I'm gonna write, click on it and I'm gonna choose Replace Values. It's already in the replace with. And Chris's last name is Evans, so I'm gonna just type in Evans and click Okay. We're gonna replace Aaron's last name, and Aaron's last name is going to be Rogers. Gonna just type that in, replace with, and click. Okay. 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, replaced 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 write click on a applied step, you can delete that step just by clicking delete, or you can delete that step in 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 gonna perform some transformation steps on the orders query. So on the left side of the screen in the Query's pain, I'm gonna go ahead and click on orders. The first thing we wanna 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 managed 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 gonna go ahead and click the Choose Columns icon and the columns that we're gonna remove. I'm gonna 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 gonna click okay 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 gonna take on the orders query is to filter the data for just five particular states. I'm gonna 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 dropdown, 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 gonna 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 wanna filter for Arizona. So I'm gonna check Arizona, California, Florida. I'm gonna scroll down until I see New Jersey and I'm gonna check New Jersey and New York. And now I'm gonna click, okay. So now the data is filtered for just those five states. We decide that The last

things we're gonna do for right now is we're gonna rename state or province column to state, and we're gonna sort it in ASC sending order. So I'm gonna just double click where it says state or province, and I'm gonna 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 gonna click on the auto filter arrow and right at the top I'm gonna 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 Empower Query editor. If we wanna get the changes down to our data model, empower BI desktop, we're gonna 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 gonna 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 manager's 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 gonna 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 gonna do now is save

the Power BI desktop file. Our next lesson, enhancing the data model is where we're gonna 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'll 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 gonna need to go back to Power Query Editor. So on the Home Tab Ribbon, go ahead and click Transform Data,

and the Power Query editor window will open. . You'll notice on the home tab a ribbon. In the combined group, you have two choices that are available, merge queries and append queries. You also have sub choices for each of those. When you click on the Merge Queries dropdown, you can see that you can either merge to queries or perform the merge and create a new query. We're gonna select Merge queries as new. We wanna 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 gonna scroll across until I see the region field, and I'm gonna just click in it letting it know that that is the common field. Underneath that, I'm going to select the dropdown and select the manager's table. And in the manager's table, I'm also gonna 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 2,428 rows from the first table. It also has above that join kind. Now table joins can be a really intense topic, and we're gonna 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 gonna 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 dialogue box, we wanna change the join kind from left out. I'm gonna do the dropdown 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 gonna go ahead and click okay. 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 the new query. The information from the manager's table is gonna 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 dropdown 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 gonna 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 gonna just click okay in here as they're all checked. And you'll notice if you scroll to the right, you now have three additional columns, managers, region Managers first, and Managers. Last, take a moment to change the managers dot manager first and the managers dot 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 no record. So we decide we're gonna filter that out. I'm gonna go to the managers dot region auto filter arrow. I'm gonna load more at the bottom because the list is incomplete and I'm gonna uncheck Central and click okay, so we're not seeing the null record. And we'll go back to Auto Filter Funnel now and we'll sort the regions again. The list may be incomplete, so load more and then sort them in ascend order. So we'll have East at the top of the list. To rename this query, we could

right click on it in the Query's Pain and Choose Rename. Or because we're in Power Query Editor, we can rename it under query settings and properties. So I'm gonna click on the name Merge one there, and select it, and I'm gonna name it Orders by Region and press enter. We want to go ahead and close and apply our changes, so we're gonna 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 gonna wanna merge the orders and returns tables together into a new query, and you're gonna wanna 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 return dot 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

Pain, you'll notice that you have your orders by Region Merged Query and your returned orders merged 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 gonna 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 gonna do is we're gonna 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 column. The next

check box is column distribution. I'm gonna leave column quality checked, and I'm gonna check column distribution, and it expands that area underneath the column headings. It's showing me that I have 11 distinct values in 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 gonna cover. That opens a panel at the bottom of the screen underneath your data set, and it's giving you column statistics to 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 gonna go back to the home tab of the ribbon, and this time I'm gonna click the close and apply dropdown, the first dropdown.

And I'm gonna just close Power Query Editor without applying the changes. And I will save my Power BI desktop file. Notice how frequently I save the desktop file. That's where your data model is stored. To recap, module three, 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 four 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 relationship 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 role level security. Also known as r l s. Lesson one is working with tables. Working with tables can mean many things. For starters, we're gonna work with formatting some of the columns in the orders table. The first thing we're gonna 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 pain to the right click on the orders table in your fields pain, and now you're seeing the data in that table. Some of the formatting changes that we're gonna wanna make here is we're gonna want the discount column to be formatted as a percentage. We're gonna want unit priced and shipping cost columns to be formatted as currency just to name a few of the changes we're gonna wanna 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 gonna wanna do, we're gonna 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 gonna go ahead and click

the percentage. 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 gonna change that too to a zero. And you can see the impact on the data. Now I'm gonna click in the unit price column. And in the same formatting group, I'm gonna click on the dollar sign icon, which is the currency format. Go ahead and do the same thing for the shipping cost column formatted in currency, I'm gonna use the fields pain on the right to navigate

to the next field that I want to format. So I'm gonna 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 gonna go to the format dropdown and you're gonna click on the format that just says March 14th, 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 gonna

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 gonna 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 dropdown arrow and you'll see the different things that you can categorize. So we

are 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 U R L to be within your data, you could use a web u r URL and categorize it. If you have a u R URL for an image and you want that image to

show, you can use the image U R L, 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 dropdown. We're gonna 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, Citi 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 to categorizations help on maps. Lesson two is dimensions and hierarchies. A subtopic of working with tables. To get started, we're gonna 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 an effective 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 gonna 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 gonna 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 gonna make a copy of the orders query and transform the copy into a customer's Dimension Query in the Query's pan on

the left. Go ahead and right click on Orders and choose Copy, right click anywhere in the Query's Pain and choose Paste. We're gonna remove the columns that we do not want from the orders two instance, the pasted version. And we're gonna 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 gonna do and choose columns is uncheck Select All columns, and then I'm gonna select the columns that we want in the customer's dimensions. Query customer id, customer name, customer segment. We're gonna

grab region, state City Postal Code and go back up to the top and select discount as well. And click okay 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 gonna 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 gonna select orders too. And you'll see this convention sometime. So I'm gonna 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 gonna 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 gonna wanna choose the four columns that begin with the word product and include them only in the new table. I'm gonna 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 gonna 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 gonna go up to add column, tab on the ribbon. And in the general group, you're gonna click on the dropdown next to index column, and we're gonna choose custom. We're gonna say we want the starting number for our products to be 0 0 1, and we

want to increment by one and click okay. So notice it dropped off the zeros and that's fine. We're gonna 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 Power BI desktop. So we're gonna 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 pain. And we decide we wanna rename that index field that we added. We wanna rename it product id. So in the fields pane, I'm gonna right click on it and I'm gonna 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 gonna 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 wanna start with

the broadest category in terms of column and end with the narrowest category. So in order to do this, we're gonna right click on region in the fields, pain under the orders table, and we're gonna select create hierarchy. If you notice now, right underneath the region field, there is a collapsed region hierarchy. And I'm gonna 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 wanna add the next broadest category after region would be state. So in the fields pain, I'm

gonna right click on the state field, and I'm gonna hover over add to hierarchy and click on region hierarchy. We're gonna add two more fields to the hierarchy. We're gonna 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 two, 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 gonna 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 colon, one as seen on the slide. An example of that would be one manager has one region. If the manager's tables and the region's table were separate tables, the one to many relationship is the most common. It's represented by one colon 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 colon.

One. Many to many relationships are the least common. They're represented by asterisk colon, 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 gonna 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 gonna click on the file tab, options and settings, and then options in the options button underneath current file, I'm gonna click on data load. We briefly reviewed these settings in

module two, 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 checkbox 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 checkbox 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 okay. 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 gonna go over to Model View and we can see that some relationships have been created

in this file. First thing I'm gonna do is collapse the properties and the fields pains so I get more space and I'm gonna scroll to the right and see if there are any other table cards that are out of my view. And I'm gonna 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 gonna 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 gonna just move the customer's card underneath the orders card and you can see that it's joined to the orders table. It is also joined to the manager's table. If I hover over the join line between DIM customers in orders, you'll notice In Dem customers it's highlighting the customer ID field that is the matching field between the two tables. We decide that we're gonna focus on our original orders, returns, and manager's tables. So I'm gonna 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 pains. We're also gonna hide returned orders, dim products, and orders by region. And I'm gonna just move those kind of over to the right, the ones that I'm not gonna 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 gonna size them so I can see all of the

fields in each card. Makes it a little bit easier to work with when you don't have to scroll for a field or it's outta 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 wanna 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 ID is in orders, and it's in the returns table. To create the relationship, I'm gonna click

and hold on, order ID and 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 wanna 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 okay. To close the edit relationship window, let's go ahead and review the model interface. The first thing I wanna 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 dialogue box opens good tip about this dialogue box. If you go and do major table transformation, come back to model view, click on managed 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 if it found any. We're gonna close that dialogue box. On the right side of your screen, expand both the property's pain and the field's pain in the field's pain. Expand the orders table and click on the order date field. You'll notice that the property's pain 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 in 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. Um, 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 and 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 and 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 customer's 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 I icon in the upper right corner of their title bars. And 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 pain 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 pain. 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, r ls and Power BI can be used to restrict data access for given users filters, restrict data access at the role level, and you can define filters within roles in the online service. Members of a workspace have access to data sets in the workspace. R l s 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 gonna 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 gonna be using the modeling tab on the ribbon and in the security group. Go ahead and click on Manage rolls to create our roll. We'll start by clicking the create button and we're gonna name the Roll East. In the center of the managed rolls box, you're gonna 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 gonna put in a simple DAX expression, Dax DAXs data

analysis expression. It's a formula language and you'll learn more about it in the next module. In the meantime, you're gonna click in that table filter DAX expression box on the right, and you're gonna type in open square bracket as field names need to be enclosed. In square brackets, you're gonna type region and a closing square bracket. Then you're gonna type the equal sign. And in double quotes, because it's a tech space field, you're gonna 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 gonna 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 managed rolls box, I can click on the check mark which says Verify DAXs expression. If it found anything wrong with my expression, it would let me

know. At this time, gonna go ahead and save to test your roles. Also on the modeling tab, right next to Manage Rolls 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 okay. You'll notice the yellow band at the top of your screen that says, now viewing as east Go back to date of view. And when you click on the orders table, you'll see that it's only showing the East region in. We're

gonna click on stop viewing in the Yellow band. And now we're seeing the full data set. We're gonna 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 gonna 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 too. So it saved a 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 gonna hover over the data set and

we're gonna go to the vertical Ellipses More Options button. And we're gonna 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, male enabled groups, security groups, individual email addresses you just cannot use Groups created in Power bi. I'm gonna enter an email address from my organization and click the add button. After I do that, I'm gonna click save at the bottom so it saves that person to my

E 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 role 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 gonna 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. Daks 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 two. We'll be starting with the Sample Superstore

desktop file if you wanna get that open. This module has five lessons. We're gonna 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 gonna 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 gonna 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 equal and over in your fields pain to the right, you have a new table called table Right now we're gonna 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 gonna 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 gonna start by beginning to 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 gonna 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 gonna 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 gonna 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 pain. 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 1,746 rows. So there is 1,746 distinct order IDs in the orders table. In your fields pain. Click on the orders table and look at the status bar and you'll see that there's 2,428 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 gonna use the date disfunction

to calculate the difference in days between the order date and the ship date in the orders table. And we're gonna name the column days to ship. So to get started, what I'm gonna do, we can do this from the ribbon, or we can do it by right clicking in the fields pain. So what we are gonna wanna do is I'm gonna right click on the orders table in the fields pain, and I'm gonna select new column. The same effect happens when we did a new table. Where you in your formula bar, you get one column

equal and in your fields pain, you have a column called column. In this moment, well we're gonna name it Days to ship. So in the formula bar, I'm gonna double click the word column and I'm gonna type days to ship. And then I'm gonna navigate to after the equal sign, we're using the date disfunction. So I'm gonna start typing da. And when I see it on the list, I stop typing again. You wanna avoid typos wherever possible. In this case, I'm gonna use my down arrow to highlight date diff in the list. And I'm gonna

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 date diff 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 gonna start typing orders for the name of the table and I'm gonna arrow down in the list until order date is highlighted and I'm gonna tab it in and we want the entire

date. So a subsequent menu comes up, date is already selected, and I'm gonna tab that in. Now we're gonna use a comma which separates the arguments. You'll notice now that date two 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 gonna start typing orders again, and I'm gonna 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 gonna tab in date and I'm gonna type a comma. The interval we

want the difference in dates in is days, so day is already highlighted. I'm gonna tab that in and I'm gonna press enter. You'll see your screen flicker while it's doing the calculation, and now as 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 gonna 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 gonna be using the Rank X function to do this, and it has several variations. We're gonna 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 pain, and then click the new column button. It's gonna do the same thing as when we write clicked on orders and chose new column in the formula bar. We're gonna go ahead and rename the column. So I'm gonna double click the word column, and I'm gonna call it sales ranking Highest Sales ranking highest. And then I'm gonna get myself after the equal sign. And the function we're using is Rank X. 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 gonna reference the orders table so it has a separate table argument. So I'm gonna just start typing the letter O and the orders table by itself shows up on the list. And I'm gonna

tab that in. Now I'm gonna 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 gonna start typing orders again, and then it'll show me the table as well as all the fields in the table. And I'm gonna 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 gonna edit the Rank X function we just did, because we want to rename it sales ranking lowest. And we're gonna 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 gonna do our edits in the formula bar. So the first thing I'm gonna do is I'm gonna double click the word

highest and change it to lowest. And inside the parentheses, I'm gonna click after order sales, after the closing square bracket, and in between the parenthesis, and I'm gonna 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 wanna skip that argument. So we're gonna 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 gonna switch the order to ascending. So ASC is already highlighted. I'm gonna tab it in and I'm gonna 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 rank X handles ties. So imagine we had two sales values of, I'll just say $1 each because we have it in ascend 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 gonna go ahead and open the Excel sample superstore file so we can make a change in there. We're gonna change one value in this Excel file to force a tie in sales values. I'm gonna use the name box up here in the upper left corner

and I'm gonna just go into the name box and I'm gonna type W 360 9 and press enter and it will take me to that cell. So cell W 360 9 is selected and I wanna change that value in that cell to 52 97, mimicking the value above it. So I'm gonna just type 52.97, press enter and I'm gonna go ahead and save and close this Excel file to get that changed. To show in desktop, I'm going to have to refresh. And that's on the home tab, 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 Tide sales values in. So I'm gonna go to the order ID auto filter dropdown, and in the search box you're gonna type 88 53 and you'll see several orders come up, order IDs with those numbers in it. What we wanna do is we wanna uncheck select all and you're gonna check 8 85, 38 and 88, 5 39 and press Okay. You'll see that both of

these orders have the same sales value and the ranking is the same 5 46. We are gonna 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 5 47. So that would be the next ranking. We had two at 5 46, right? And you'll see there is no ranking 5 47 because it's going to skip that number. If we had three at

the same value, they would all be 5 46 and then it would skip two numbers. Change your search to 5 48. 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 Rank X function. So again, when you have sales values that are the same and they tie and their number in the ranking is like 5 46 and there's two of them at that number, it's gonna skip 5 47 unless you tell it not to do so by using the optional last

argument. So we're gonna modify this Rank X function again. Um, we're gonna first go up to the formula barn, we're gonna change it after the word Lois, put no skip, so we'll know this is the one that's not gonna skip any numbers. And after that you're gonna click after a S C and inside the closing perin and type a comma. So you can see that last optional argument is the Ty's argument. If you don't use this argument, it defaults to skip and we've seen that behavior. We're gonna tell it to use the dense argument so

it won't skip any numbers. So since it's already selected, I'm gonna tab it in and I'm gonna press enter. So it recalculates. Now keep in mind, since it's not skipping numbers, the rankings have changed. If you wanna look up order IDs 88 5, 38 and 88 539, 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 will be the next number. It won't skip any numbers. Go ahead and check that out and then save your. So far in this module, we used 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 date diff and the Rank X 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 gonna 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 wanna 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 gonna get started using quick measures. We wanna 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 pan, I'm gonna right click on the orders table and

I'm gonna select new quick measure. The dialogue box will open for quick measures, and on the left side you have calculation and a dropdown. 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 gonna go ahead and do the dropdown. You wanna 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 wanna use is we're looking for

one under the total section, and we're gonna select total for category filters not applied. That's the calculation we want to use for the base value. We're gonna expand the orders table on the right side, and we're gonna just drag the sales sealed into the base value text box and it says some of sales, but we want the average of sales. So I'm gonna go over to the ellipses, the more options button, and instead of some, which is the default, I'm gonna click on average. And for category in the fields list, we're gonna 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 okay 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 sge based on the selections in the quick measures dialogue box. It built a nested calculation

for you. It used a calculate function with a nested average and a nested all function. You'll see your measure in the field pain 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 gonna go to report view the first view button in

report view. Just go to your fields pain 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. And 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 field's pain, you're gonna go ahead and grab the customer segment field and drag it to the access box right in the visualizations pan, drag it and drop it. We're gonna do one other field for this.

We're gonna grab product category field, and we're gonna drag it to the small multiples box in the visualizations pain. And I'm gonna 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 21 39 0 7 for technology. When I hover over it, you'll see the tool tip updates, 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 gonna create a measure from scratch to calculate the average sales per product category for the orders table. So I'm gonna just right click on orders again in the fields pain. This time I'm gonna do new measure instead of new quick measure. And it just brings up the formula bar. So we're

gonna 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 gonna 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 gonna go ahead and tab and

calculate. The first argument is expression. We wanna calculate the average of orders sales field. So for the expression argument, we're gonna use the average function, start typing average. And when you see it, you're gonna 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 gonna start typing orders and I'm gonna just do my down arrow until orders sales field is selected and I'm gonna tab it in. Now we wanna get back to the calculate function at this point. So after order sales,

we're gonna go ahead and type a closing parent. And it takes us back to calculate. We are still in its expression argument. And now we want to advance to the filter argument, which is optional. So we're gonna type a comma and it takes us to that filter one argument. For the filter one argument, we're gonna use another function, the all selected function. So I'm gonna start typing all, when I see it on the list, I'm gonna highlight it. So it tells you that all selected returns, all the row in the table, or all the values

in the 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. Pain 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 all selected, it would ignore any filters inside the query. For example, like on this filters pain, but it would keep filters that you can control from outside the query. In our case, it's gonna return all of the product categories. We don't have any filters, so it's gonna return all of them. So I'm gonna go ahead and tab in all selected, and I'm gonna 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 gonna get that in. We're gonna 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 pain, and again, it will only show on a reporting visualization as it is not part of your data set. Save your sample superstore file. The final lesson in this module is using time intelligence functions and key performance indicators. We're gonna 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, the 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. Calendar auto 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 gonna use calendar auto. It will return one column with all of the dates in the data model. After we create that table, we're gonna amend it by adding other columns. So let's get started In your fields Pain, make sure the orders table is selected. And then on the table tools tab or ribbon, you're gonna click New table. In the formula bar, we're gonna double click on the word table and we're gonna name it dates and navigate to after the equal sign, we're gonna use calendar auto. So I'm

gonna start typing ca when it shows up on the list, and you'll see that it says it returns the table with one column of dates calculated from the model automatically. I'm gonna go ahead and tab it in and we are just gonna do a closing parenthesis at the end and press enter. So if you look over in your fields Pain, you'll see the dates table when you expanded. It has one column called date and you can take a look at the dates. If you go over to your left, go to date of 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 20 14. 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. In addition 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 calendar auto function within an Ad columns function.

And to do that you're gonna click after the Equal sign right before Calendar Auto in the Formula bar. And you're gonna type start typing, add and add columns. Function comes up, it does exactly what it sounds like. It's gonna add more columns to this table. I'm gonna go ahead and tab it in. Now we're gonna click after the closing parenthesis after calendar auto, and we're gonna type a comma. So this is now part of the ad column syntax. It wants the name of the column that we're adding. We're gonna have to put it in double

quotes because it's text. So in double quotes, I'm gonna type year and close the quotes and type a comma. And I'm gonna use the year function to extract the year of the date. So I'm gonna start typing year, it shows up on the list. I'm gonna tab it in. And what you're gonna do is you're gonna just in square brackets, you're gonna type date, well when you type the square brackets, you'll see dates show up on the list. So I'm gonna select it from the list instead of typing it. So to extract the year of

the date, now I'm gonna do a closing parent to close out the year function, and I'm gonna type a comma. Now, I don't want this to be one long run on function, so I'm gonna 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 gonna name it quarter, and we want the letter Q before the quarter number. So in double quotes, we're gonna type the capital letter Q. We're gonna use the ampersand

for concatenation, meaning combined, combine the queue with the quarter number, and we're gonna use the quarter function. So go ahead and start typing it and get it in on that bracketed date field. So I typed the square bracket and it popped up on the list. We need a closing parenthesis, another comma, and you're gonna shift enter again to get down to the next line. We wanna 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 column. So we're gonna name another column month and that's gonna be in double quotes comma, and we're gonna tell it how to format the date. So we're gonna 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 gonna do a comma and now we're gonna tell it what format. So in double quotes, I'm gonna type four lowercase msms to indicate, give me the full name of the month. If we

did three s, it would be an abbreviation of the month. We're gonna close the double quotes. Closing perin, one more comma and shift enter. We also wanna extract the month number, so we wanna have a column called month number. We're gonna do that in double quotes, comma, we're gonna use the month function here. The month function extracts the number of the month and we're gonna do it on that bracketed date field, which is the only field in this table so far. We're gonna do a closing par and I'm gonna do shift enter one more time

and type One more. Closing per. 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 market as a date table so Power BI will know which table to reference when you're using Time Intelligence functions. We're gonna in the fields pan, we're going to right click on our dates table

and offer the shortcut menu. You'll see mark as date table, hover over that, and then click on mark as date table. The dialogue 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 gonna show up on the list here. It tells you it was validated successfully and you can click okay. 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 gonna do it based on the order date field in the orders table. So I'm gonna 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 sa, what that is saying. Go ahead and save your file. Now that we have our dates table related to our orders table, let's go back to date of view and we're gonna 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 gonna get rid of them in the formula bar. So in your date dysfunction 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 gonna do the same for orders ship date. We're gonna get rid of the.date after it, the qualifier, and we're gonna 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 inform. We're gonna 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 pain and choose new

column. And we're gonna name this column in the formula bar. You're gonna name the column end of month and position yourself after the equal sign. Well one at a time. Intelligence functions in power bi is end of month, so start typing end of month. 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 gonna start typing orders and then I'll see the table as well as all of its fields. And I'm gonna just

go down until I have orders order date highlighted and tab that in. 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 gonna do another new column in the orders table. And this one is gonna be for the end of quarter. So I'm gonna right click on orders. Again, choose new column. I'm gonna name the column end of quarter and get after the equal sign. And there is an end of quarter

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 gonna click in the end of month column and on the column tools tab of the ribbon, I'm gonna access the format dropdown and select the date format that says March 14th, 2001. Due the same for the end of quarter column. We're gonna 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 gonna use this file for key performance indicator visualization. This file has more detail than the 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 field's pain. We have comparative columns in this data, two of which of our 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 gonna collapse the sales table and expand the timetable. The timetable has a fiscal month column, and we're gonna 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 K P I, 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 seven, we'll do a far deeper dive into reporting visualizations, but for right now, we're gonna 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 K P I,

you won't be able to have sorting capabilities. So this is how it works in the fields pain. We're gonna expand the sales table and we're gonna grab the total units this year, calculated column, and drag it right into the center of your screen onto the Kansas. Now we're gonna expand the timetable in the field's pain, and we're gonna drag the fiscal month field into the highlighted box on the canvas. At this point, this is not a K P I 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 wanna 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 ascend order by fiscal month. So in the upper right hand corner of the visualization, you'll have the more options, vertical ellipses icon. And we're gonna click that and hover over sort by at the bottom. You're gonna click on fiscal month and you'll see that it

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 gonna 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 gonna convert it to the KPI Visualization in the visualizations pain. 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 gonna 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 K P I visualization. When you look in the visualizations pain 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 pain. I'm gonna 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 gonna do here is rename this page, page one. We're gonna double click on it and

we're gonna name it K P I. Press Enter. So we 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 used 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 six is optimizing model performance. You'll be introduced to steps, processes, concepts, and data modeling best practices necessary to optimize the 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 two. In this module, we'll use the direct query method to access a data source, will 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 and 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 A Zero 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 recent 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 dataset. 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 gonna get started by using the retail analysis sample desktop file, where we created our K P I 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 dataset for a direct query, that dataset needs to be published to the service. So we're gonna go ahead and publish this dataset 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 published. 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 gonna 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 dot pbix in PowerBI. Go ahead and click the link. It opens the report, which is all of the pages that are in our report view in a desktop, because I was

on the KPI page, that's the page that it's on here in the service. We wanna navigate to the workspace where we sent this data. So on the left side of your screen, almost toward the bottom, you're gonna hover over the workspaces icon and select it. I'm gonna 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 dataset, 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 gonna click on Got it. On the publishing, the Power BI screen, we wanna access direct query from a new Power BI file. So we're gonna 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 gonna go ahead and cancel that dialogue. When you're

bringing in from a Power BI Data dataset, 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 gonna go ahead and click on Power BI data sets, and it's only gonna show you the data sets that are published to the. We're gonna 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 gonna 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 pain. 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 and the tables and the relationships that have been created in this data. I'm gonna go back to report view and we're gonna recreate the K P I report that we did in the previous module. So we're gonna expand the sales table in the fields pan, and we're gonna grab the total units this year field and drag it onto the canvas. And then we're gonna expand the time table, and we're gonna drag fiscal month into the framework on the canvas as well. Next, we're gonna access more options in the upper

right hand corner of the visualization, and we're gonna hover over sort by and we're gonna click on fiscal month. We're gonna go back to more options and click on Sort Ascending. So remember, you have to sort before you turn it into a K P I, because the K P I doesn't allow sorting. I've resized the column chart a little bit, and now in the visualizations pain, I'm gonna go to the K P I visualization and convert this into a KPI P I. 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 9,000. Kilobytes 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 gonna be using the sample Superstore desktop file if you wanna 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 gonna 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 gonna start by creating a basic measure to calculate the total sales from the orders table. So over to the right in the fields pain, I'm gonna right click on orders and choose

new measure. We're gonna name the measure, so I'm double clicking the word measure in the title bar, and I'm gonna name it Total Sales and navigate to after the equal sign. And I'm using the calculate function with a nested sum X function here. So I'm gonna start typing, calculate, and I'm gonna tab it in from the list. And then I'm gonna start typing some, and I'm gonna highlight some X and tab that in as well. So the first argument for some X is the table. I'm gonna start typing orders, and when I see the table

on the list, I'm gonna tab it in. I'm gonna type the comma to separate arguments, and the expression is going to be on the orders sales field. So I'm gonna start typing orders again, and I'm gonna just down arrow until orders sales is highlighted. And I'm gonna tab that in and I'm gonna press enter at this point. If there was an error, you would have red highlight up here and it would be letting you know. And 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 gonna create where we're gonna also use a variable. Now we're gonna create another measure in the orders table that includes variables. So I'm gonna right click on orders again and select new measure in the formula bar. I'm gonna name this 1 20 10 total corporate sales, 2010 total corporate sales. And I'm gonna position myself after the equal sign. We're gonna go ahead and press shift enter so we get another line. This is gonna be

a multi-line function, so you don't ever wanna be in a position where you have to read a long line from left to right, especially when you're troubleshooting. And we're gonna 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 gonna type var var is a keyword. It will not show up on the function list. They have variance functions that show up on the list. We just want var plain plane. So we're gonna do shift enter afterwards to come down to the next

line. We're gonna name this variable corporate sales. I'm gonna 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 gonna use the filter function. Go ahead and grab that with a nested all function. Grab the all function and we're gonna refer to the orders table customer segment field. So when I start typing orders and I

see orders customer segment on the list, gonna highlight it and tab it in. And we're gonna do a closing parenthesis. So that orders customer segment is the table name or column name for the all function. We're gonna do a closing parenthesis to return to the filter function. And we're gonna type a comma to get to the filter expression argument. So this is where we're gonna tell it to filter only for corporate customer segment. So we're gonna reference orders customer segment field again, and we're gonna 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 gonna 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 gonna 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 and this variable we'll tell it to only use 2010 dates. So we're gonna do a var keyword again and shift enter. We're gonna 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 gonna be another filter all. So I'm gonna start using the filter function and then I'm gonna bring in the all function. This time we're using our dates table year field. So I see it on the list. I'm gonna grab it dates year, and I'm gonna do a closing perin to come out of the all function, a comma to advance to the filter expression argument for the filter function. And we're gonna reference the dates year field

again. And this time we're gonna 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 gonna use the keyword return and shift enter, and we're gonna use the calculate function here. And we wanna 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 gonna grab total sales measure. We're gonna 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 x y icon indicates it is a variable, a name variable. I'm

gonna tab it in, type A comma and shift enter. And I'm gonna start typing included dates, which is our second variable, and I'm gonna grab that and get it in there. And a closing perin. Now at this point, the return is what's really happening here. It's gonna calculate total sales, but it's gonna be filtered for the corporate customer segment and for the year 2010 order dates. So, When you do something like this or in any DAX calculation really, you might wanna 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 gonna do shift enter one more time and we're gonna 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 gonna

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 gonna go ahead and press enter and we shouldn't get any error messages for this. And in your fields pain, 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 gonna go up to the formula bar and just select everything, including the comments. And I'm gonna do control C to put it on the clipboard. I'm gonna write, click on the orders table again in the fields, pain and select new measure. And I'm gonna use Control V as in Victor to paste it. Now I just have to update it. So I'm gonna start with the name of it. I'm gonna 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 gonna press enter. So now I have two measures, one for 20 10, 1 for 2011. I'd also like the same thing for 2012 and 2013. So I'm gonna 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 pain. And now we're gonna use a multicard visual to see all of the numbers. So you're still in report view, and

what you're gonna do is you're gonna go to your visualizations pain and you're gonna look for the multi roll card visualization and click it. And I'm gonna just expand the width of the visualization. And then in the fields pan, I'm gonna click and hold on 2010 total corporate sales and drag it into the card visualization. Do the same with 20 11, 20 12, 20 13, 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 wanna break them down by filtering. You also wanna 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 wanna 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. So 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 in 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 Cato, 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 seven is gonna take us on a deeper dive of the full capability of Power BIS reporting feature. You're gonna 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 the 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 gonna start a new page by clicking the plus sign to the right of your page tabs, and we're gonna create a pie chart visualization. In the visualizations pain. You're gonna 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, and that's not a necessary step, it's just something I choose to do now. And in this pie chart, we wanna show regular sales units and markdown sales units. So in the fields paying to the right, you're gonna expand the sales table and you're gonna identify the regular sales units field. And again, if you wanna 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 gonna just drag it into the framework of my pie chart. That's one way of getting that field in there. Now we wanna look for markdown sales units field, and this time what I'm gonna do is I'm gonna check the box in front of it in the field's pain. 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 wanna 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 tool tip. Whatever fields you use in the visualization will automatically show up in the tooltip, 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 individualizations pain, your, 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 tool tips fields, depending on the visualization type, that will change in the visualization's pain. What we wanna do is over in the fields pain, we want to find two different fields, and we're gonna add them two tips. Again, the fields that you're using individualization automatically show up as tooltip when you hover, although they don't show in the tooltip field. In the fields paying. Find regular sales dollars in the sales table and drag that field

to the tooltip box. Also, find markdown sales dollars and drag it into the tooltip 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 tooltip. Now we wanna 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 wanna change the title of the chart so it's more succinct. We wanna 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 pain, 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 wanna 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 gonna expand the title format option by clicking the down arrow, and I want to change what the title text says. So what I'm gonna do is I'm gonna click in title text, and I'm gonna do control A to select everything that's in there. And I'm gonna just type regular sales and mark down sales units. 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 gonna click on the second alignment button and you'll

see that it instantaneously happens on the pie chart. Now I'm gonna 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 gonna do the color drop down and select a comparable color for the background of my pie chart visualization. The last thing we're gonna do with the pie chart formatting is apply a shadow effect to it for more visual appeal. So I'm gonna expand the shadow format category, enable it, and I'm gonna 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 visual. We should probably rename the page from page one. That's not a very descriptive report page name. So I'm gonna double click on the page one tab, and I'm gonna 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 dropdown 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 gonna 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 pain, you wanna find the clustered column chart and click on the icon. I'm gonna 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 wanna 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 gonna do is in the sales table, in the fields pain,

I'm gonna expand this year's sales. If it's not expanded, do the arrow in front of it and I'm gonna check the box in front of value. We want this year's sales value. Then I'm gonna 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 field's Pain. And you can check the

box in front of chain, or you can click and hold and drag chain to the access box in the visualizations pain. At this point, we decide we'd like to see the territories as well in our visualization. In the visualizations pain, we have a small multiple 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 gonna grab territory and drag it into the small multiples box, and I'm gonna 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 gonna put territory in small multiples. We decide that we're gonna 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 gonna do is if your filters pain is collapse, go ahead and expand it.

And in the field's pain, 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 gonna select visual level filters, so we only want it to apply to this particular visualization. In your filters. Pain, it already has chain and last year's

sales. This year's sales and total sales variance, they're all set to show everything is 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 gonna just check the box in Front of Georgia, and you'll notice now that the visualization is only showing information for Georgia. I'm gonna uncheck Georgia and it's back to the way it was, where everything, it's showing all of the territories. When we created our K P I in a previous module,

we had to sort it before we turned it into a K P I. We decide that we wanna sort our clustered column chart here. We wanna in sort it by this year's sales in ascending order. So sorting can be a two-step process. In the upper right corner of your visualization, you'll see the more options. Ellipses button. You're gonna click on that, hover over, sort by and click on this year's sales. Then you're gonna go back to the more options button and you're gonna click on Sort Ascending. So now the visualization is sorted in ascending order.

By this year's sales, we decide that we wanna add data labels to this chart. That wasn't an option in the format. Well, for the pie chart, so in your visualizations pain, we're gonna 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 gonna name this report, page t y for this year, l y 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 gonna have to filter this particular visualization by territory, so we're gonna remove the territory filter from the filters. Pain again, territory is not a field that we're using on this visualization, so it will allow us to remove it from the filters. Pain. The ones that are on the visualization, you cannot remove from the

filters. If you hover over territory, the top it's 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 NRE versus Markdown report page and the K P I report page. We'd like to use a visual filter known as a slicer. So what I'm gonna do is I'm gonna make room for the slicer on this page. I'm gonna just leave more space on the right side where I'll be

able to pop the slicer visualization in. And since we're gonna wanna use the same slicer on two other report pages, K P I, 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 t y lly 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 pain, you're gonna 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 gonna do in the store table, in the fields pane, we're gonna just drag chain over to the slicer and we realize the slicer doesn't have to be that tall. So I'm gonna resize it doesn't need to be take up that much space. And I decide that I'm gonna do a little bit of formatting to the slicer. With

the slicer selected, I'm going to the format well in the visualizations pain, and I'm gonna just enable a background color for the slicer, expand background, and choose a complimentary 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 gonna enable border and expand it. And I'm gonna leave the border black, but I'm gonna 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 gonna just check the box in front of fashion's direct and it filtered the visualization for that chain. I'm gonna uncheck that box so I get both chains showing. Now with your slicer still selected, you're gonna 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 wanna use the same slicer on multiple pages. When you click on Sync Slicers, you get a sink slicers pain in between your filters and visualizations pains 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 gonna 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 eye, 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 T Y L Y N variant, so it's synced and visible. On this page, we're gonna select both check boxes for the reg versus markdown page, and we're gonna select both for the K P I page. And 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 sync slices pane open. So to get rid of some of the clutter, I'm gonna close that pane by using the X and on T Y L Y in variance page. I'm gonna 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 Lindsey. On this page, uncheck Lindsay's, and filter for Fashions Direct. And when you go to your K P I page, you'll notice the numbers have changed as it's only filtered for fashions direct. If I go back to T Y L Y and variance page, that is also filtered for fashions Direct. So I'm gonna 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 change. In this next lesson, we're gonna

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 gonna put our drill through report on a separate page. So I'm gonna go ahead and click the plus sign to get a

new page. And I'm gonna double click page one 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 gonna build, we're gonna use the multi row card visualization in your visualizations pain. Locate the multi-row card icon and click it. We're gonna use three fields from the sales table to populate this card. The first field we're gonna use is last year's sales, so I'm gonna just click and hold and drag it over into the card framework. I'm gonna

grab the value field from this year's sales and drag it over. And the last field is we want total sales VAR percent. Once I drag that into the card, I'm gonna widen the card so it only displays one row and I don't need it to be that tall, so I'm gonna make it less tall by using the sizing handles. At the bottom of the visualization pain, regardless of the type of visualization that you select, you have a drill through section. We're gonna use that section now to add a field as a drill through field. And

then you'll see what happens with our visualization. We wanna use the category field from the item table as our drill through field. So I'm gonna locate it in the fields pan. I'm gonna click and hold and drag it to the box that says add drill through fields here at the bottom of the visualizations pain. 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 roll card, but right now we wanna proceed so you can see the functionality of using drill through. We're gonna 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 Pain. 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 gonna drag category to the access box in

the visualizations pain. And we're gonna drag from the sales table this year's sales value to the values box in the visualizations pain. And we have our stacked bar chart. Now I'm gonna make the bar chart just a little bit wider and we'll come back and format it later. And I'm gonna click away from it because we wanna add two slicers to this page. One for chain and one for buyer. So in your visualizations pain, locate your slicer icon and click on it. And I'm gonna 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. So I'm gonna just pop it over here on the right side, and you'll notice the red dash 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 gonna 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 dropdown. So to the right of chain, you'll see the dropdown arrow. It says, select the type of slicer, and we're gonna choose dropdown. So now when you do the dropdown, it defaults to all, and you'll see the two choices. This is 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 dropdown. We'll do more formatting on the slicer as well a little bit later. Right now I'm gonna click on

the blank area of the canvas, and I'm gonna grab the slicer icon again. I'm gonna 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 gonna drag buyer into the new slicer. Make that one a dropdown as well. And for right now, I'm gonna just move the buyer one down a little bit. We're gonna 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 at

last year's sales. 23 million end 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 gonna do, and it even tells you that when you hover over a category, it says right click to drill through. I'm gonna hover over shoes and right click on it. Hover over drill through and it shows the target page. I'm gonna 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 pain, you'll see it says 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 gonna filter this report by a buyer, so I'm gonna go to the dropdown next to all in the Buyer slicer, and I'm gonna check the box in front of

Barrett Galvin. So now this report has updated, it's just showing his sales. I'm gonna 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 wanna 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 write 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 gonna use the back button To go Back to our source report, we're gonna do the buyer slicer, drop down and uncheck Barrett Galvin. So we get the full information back in this visualization.

Now we're gonna 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 has an updated. You have to clear it on your drill through report page as well. So in the bottom of the visualizations pain, we're gonna 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 gonna 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 gonna go over to the paint roller in the visualizations paint to go back to the format. Well, we don't need data labels. We're seeing the numbers. It's a multi roll card, right? We don't want to put a title on the card. Maybe. We do wanna give the card a border for some visual interest. So I'm

gonna toggle border to on and expand the category. And I'm gonna give it a border color, like a bluish color. And I'm gonna 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 gonna reselect the card and go back to the format. Well, and I decide I'm gonna give the card a background color. So I'm gonna toggle background to on expand the category. And I'm gonna choose a lighter color. And I'm gonna 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 sorta 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 gonna use another back arrow for our back button. So what we're gonna 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 gonna use is shapes. So under shapes they have block arrows and we're gonna select the arrow left block arrow puts it in pretty big. So I'm gonna make it really tiny and I

am going to move it so that it is right above my multi roll card. And with the block arrow still selected, you'll notice you have another pain. You have a format shaped pain that shows up where the visualizations pain used to be. And in that pain you have an action option. We're gonna 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 gonna leave it on back. And now I'm gonna 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. Spend 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 gonna 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 gonna expand selection controls and make sure selector option is toggled to on. I'm gonna collapse selection controls and I'm gonna expand the background and choose a background color that's complimentary 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 gonna 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 gonna 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 T Y L Y and variance page. And the first thing we're gonna do is resize the column chart visualization so it's not as tall as it is. We're gonna

wanna put another visualization underneath it. So we're just making space for that conditional formatting. And power BI lends itself to the table visualization or the matrix visualization. In the visualization pain. We're gonna make sure that we don't have anything selected on our canvas. And we're gonna select the table visualization icon from the sales table in the fields pain. We're gonna wanna add last year's sales to the table. So I'm gonna just drag it into the table framework. And we're also going to wanna 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 visualizations pain. So if you notice we have last year's sales and this year's sales in the values box. We wanna conditionally format this year's sales. So I'm gonna right click on this year's sales hover over conditional formatting. And I'm gonna click on background color. So the background color for the specific field that we right click on. This year's sale dialogue box comes up

on the screen. Take a moment and explore the format by drop down. We're gonna 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 gonna leave it like that. It tells you it's based on this field, and that's also a dropdown. 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

gonna 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 wanna 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,500,000. So we're gonna start putting those numbers in boxes under minimum. Where it says enter a value, you're gonna put in your 20 million. For the center, enter a value

box, it's gonna be 23.5 million, and the maximum is going to be 25 million. Before we click, okay, let's take a look at this. Year's sales value 22,051,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 okay and look at the color that it used to highlight this year's sales. Now, keep in mind, if

our data changes is 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 dropdown 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 gonna 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 gonna expand items and do the dropdown next to background color, and I'm gonna choose a dark color. If I click on a blank area of the canvas and go to the buyer, all dropdown, I'll be able to see all of the buyer's names clearly. Now, because it's a contrasting background color, we're

gonna 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 gonna go up to the view tab on the ribbon in the show Paynes Group. The last group, you're gonna click on bookmarks, and it opens a panel to the left of your visualization pain with the report filtered. For Evangeline Bright the way it is, we're gonna click add at the top of the bookmarks pain,

and it adds a bookmark, it calls it bookmark one to the right of it. We're gonna go to the More Options ellipses, and we're gonna choose Rename, and we'll just call it Evangeline Bright, the buyer's name, and press Enter now. Go ahead and close your bookmarks pain by using the X in the upper right hand corner. Access the buyer's slicer. Drop down and choose. Select all at the top the next time that you wanna see it filtered. For Evangeline Bright, you don't actually have to do the filter, you just need to access the bookmark. We're

gonna go back to bookmarks on the view tab and in the Bookmarks Pain, you're gonna 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 wanna 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 pain and do your buyer slice or dropdown again and go back to select all. Our next lesson is about accessibility features, empower 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, their 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 modes, 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, and this lesson will 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 wanna 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 gonna do is configure alt text for our bar chart on a sales by category page. So I've just selected the chart and I am gonna go to the format Well in the visualizations, pain 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 wanna give a more detailed description. So in that alt text box, you're

gonna say you're gonna 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 F 11. 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 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 gonna 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 gonna 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 gonna be using the keyboard to navigate through the.

You wanna 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 Paynes group, you're gonna click on selection and it opens the selection pain 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 gonna go to is the slicer that we're currently on, and then the second thing is the second slicer, And we wanna 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 gonna use the up arrow to move it to the first position. I'm gonna click on the number two and say, that should be moved to the last position. No, 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 gonna press the tab key on my keyboard, and you'll notice that it is

selecting the bar chart. This year's 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 gonna 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 gonna click selection and it opens

the selection pain. At the top of the pan, it defaults to layer order and to the right, you're gonna click on tab order. So we wanna make sure this year's sales by category is in position number one, which it is. I'm gonna click on number two, slicer, and that's the buyer slicer. And I want that to be in the last position. So I'm gonna go above the names of the objects and I'm gonna click the down arrow button to move that one down. Now, when I test test my tab order, I'm gonna 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 gonna go ahead and close the selection pan and save my file. The last accessibility feature we're gonna 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 gonna access it by using the dropdown 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 in 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 gonna 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 visualizations pain. It's a visualization that you're gonna have to add. So in order to do that, let's go over to our visualizations pain, and after the last visualization, you'll see the vertical ellipses icon, and it says, get more visuals. We're gonna 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 dialogue 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 gonna go back to the AppSource tab. You'll notice they have categories of visualizations and we can click in the search box and type histogram am. 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 app AppSource store. We want to default histogram chart, so we're gonna

click the ad button on the right. And 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 gonna click okay on that dialogue. 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 want it to retain over different files, you need to pin it. So I'm gonna right click on that histogram icon and choose pin to

visualizations pain, and it will ultimately move it up so it's amongst all the other visualizations in that pain. Now that we've pinned the histogram visualization icon to our visualizations pain, we're gonna start a new Power BI desktop file and bring in information from an Excel file. I'm gonna 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 shows up in the new file as well. On your splash screen, we're gonna click on Get Data, and when the

dialogue box opens on the right side, we're gonna double click Excel workbook and we're gonna use the histogram Excel file that you grabbed from the video description earlier. I'm gonna just double click. And let it connect. And this file only has one sheet in it called employee salaries. I'm gonna click the check mark and you'll see it just has generic employee data and their monthly salaries in dollars. We're gonna click the load button at the bottom. This data is ideal for a histogram, and you'll see why in just a moment. 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 gonna do is go back to report view and we have this blank page here. Let's go ahead and rename page one. So it's called histogram. And the first thing we're gonna do is create a table

visualization using the employee salaries data. So in your visualizations pain, find your table icon and click on it, expand your employee salaries table in the field pay. And we wanna 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 gonna happen is when we put this information into a histogram, it's gonna 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, 2000 to 2,500. Another bin would be 25 0 1 to 3000, 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 pain, you're gonna 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 pain, you'll notice that we have a values field and a frequency field for the

histogram. We're gonna use the monthly salary field from employee salaries table as the value field. And we're gonna 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 gonna do the dropdown arrow next to first employee, and we're gonna click on count. So what you're looking at, I'm gonna just make my histogram bigger. So the frequency is over on the left, and we're gonna 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 2000 through, that's the beginning of the first bin. The second bin begins with 2.5 1000. The third one is 3,000, 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 wanna 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 gonna just go to the format well and I'm gonna expand X axis. And where it says decimal places two, I'm gonna change the two to a zero and press enter. And I did that on the wrong axis, so I'm gonna 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 two for decimal places to zero. 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 gonna close this file. If you wanna keep it, you can save it and just call it histogram. So I'm gonna go ahead and do save, and it'll prompt me and to give it a name and I'm gonna 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 gonna 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 pain, I'm gonna go ahead and collapse the fields pain for now. When I look in the visualizations pain, 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 the 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 and 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 gonna do is we're gonna write, click in a blank area of that visualization in the lower left, and we're gonna 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 gonna do control V as in Victor to paste. So now we have this visual on its own page, and we can go over to the visualizations pain and

swap out fields if we want to. We are going to delete the page that we just pasted the visual on, so I'm right clicking on page. 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 eight, 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 dataset that we published to the Power BI service in module two, 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 realtime 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. Text

box a theme. We're gonna 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 and 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 gonna get started by accessing the sample superstore OD report that we published to the service in module two. First thing I wanna point out here is the difference in icons. When we published 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 gonna click it and it will navigate us to the report. So this was a simple report that we put together earlier and we're gonna edit it. We wanna 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 wanna 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. And this view should look very familiar to you. It's almost exactly the same as report view, empower bi desktop, so you have your fields and visualizations and filters, pain on the right. Let's start by clicking on a blank area of the canvas. And we're gonna wanna add another visualization here. And so we're gonna use the table visualization, find it in your visualizations pain, and we're gonna expand the orders table in the

fields pain. And we wanna add customer segment field to our table. The other field that we wanna 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 doesn't need to be as big as it is to show that little amount of data before we add our other two visual. 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 gonna 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 gonna 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 gonna 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 gonna click on the blank area of the canvas. And the last visualization we're gonna add is the card visualization, and we want to just have that show

the total sales. I'm gonna 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 gonna just click on the blank area of the canvas. And now we want to save this report and you can

do that from the file dropdown 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 pen going across the top of your screen, you'll notice there's an icon that says Pin to a dashboard. We're gonna 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 gonna create

a new dashboard and we're gonna name it Sample Superstore. And then we're gonna click the pen live button and it tells you Pen Live page enables changes to reports to appear in the dashboard tile when the page is refreshed. So we're gonna go ahead and click on Pen Live. And it tells us, it pinned it to a dashboard, and if you catch that popup quick enough, you can go to the dashboard from that popup. 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 gonna 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 had 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 gonna create a dashboard based on streaming realtime data. Your dashboards will update in realtime 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 realtime data, there are three primary types of realtime 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 gonna 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 realtime push data set because it creates a database which maintains history We can use the Power BI visualizations on that. Data is 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 counterintuitive 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 gonna start this lesson by creating a form in Microsoft Forms that will be used to push the data into the dataset 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 wanna 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 gonna create a short three question form that will be used as the vehicle to push data into the dataset we're gonna create in the upper left corner. Go ahead and click directly on new form. It may ask you to pick an account. Sometimes it'll even asks you to sign in where it says Untitled form. We're gonna

click and we're gonna just call this streaming survey. We don't need to enter a description, it is optional. We're gonna go ahead and click the add new button to add our first question. And the type of response is gonna be a choice. So we're gonna click on Choice. The question is, which BI technology are you most interested in learning about? So I'm gonna 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 gonna click where it

says Option one, and the first option is Power apps. Option two is Power Automate. And we're gonna 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 gonna 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 gonna toggle multiple answers on, and we're not gonna make this question required. Instead we're gonna 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 gonna 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 gonna click on beginner. For option two, we're gonna select the preset intermediate, and we're gonna 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 gonna go ahead and add new to get our third question. And I got this popup. Every time something is updating, they let you know I'm gonna just get rid of it by doing. Got it. Has nothing to do with what we're doing right now. So I'm gonna 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 gonna type provide answer in city SST for state format. And I'm gonna give an example. For example, I'll put in San Francisco ca and close my parenthesis. 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 gonna 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 gonna go ahead

and click that icon and at the bottom I'm gonna choose create a workspace. So the panel opens up on the right to create the workspace. We're gonna 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 gonna 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 gonna select streaming data set. And the panel on the right opens up. We talked about the three methods of getting streaming data, realtime streaming data into Power BI API is the method we're gonna use. That's for the push data set. Streaming would be Azure Stream, and then we have Pub Nub. We're gonna leave it

on API and click on Next. We're gonna name the dataset Streaming dataset, 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 gonna leave all of them defaulting to text, but go ahead and do the dropdown 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 gonna

be technology that was referenced in the first question. So we're just gonna call this value technology. And again, we're gonna 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 gonna pull whatever value is put in the response for that question from the form. Ultimately, that data's gonna be pushed into this data set. Our next value that we're gonna use here is gonna 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 a historic data analysis. By default, it is off. We wanna 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 dataset 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 gonna just go ahead and click Dun in the lower right hand corner, even though we don't have any data in our streaming data set yet. We're gonna go ahead and create our reports so that when we do fill out our form and push data into the dataset, the report visualizations will automatically populate. To do that, I'm gonna use the more options button to the right of the dataset, and I'm gonna 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 field's pain, it just says realtime data, and we're gonna expand that table. So we're gonna create a few visualizations here and they'll populate when we push data into the data set. So the first visualization, we're gonna grab the table visualization icon, and we're gonna 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

gonna click on a blank area of the canvas and we're gonna use a map Visualization in your visualization's. Pain. The map visualization looks like a globe. Go ahead and select that. And we're gonna use location field from the fields, pain as the location. We're gonna use experience as the legend for the map visualization, and we're gonna use techno 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 gonna put a count of experiences and that goes in

the size box for the visualization. So I'm gonna 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 gonna click on a blank area of the canvas again. And this time we're gonna add a stacked column chart. So I guess that's the second icon in the visualizations pain. 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 cuz we don't have any data yet. We're gonna add one more visualization and we're gonna 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 gonna use technology. And notice how when you put it into the fields box, it says First technology. We're gonna do the dropdown 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 gonna go up to file and you're gonna choose save, and it's gonna want you to name it. We're gonna just call it streaming and click save. So I mentioned earlier when you're using streaming data, you can't use the pen live experience for dashboards because it's counterintuitive, 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. And 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. And we're gonna call this dashboard streaming dashboard. Notice the pen button does not say pen live. It knows that it's streaming, so it's not gonna offer you that option. We're gonna go ahead and click pen, and we don't have to go to

the dashboard yet. You can just close that popup if you'd like, or it'll disappear in a moment. And then I'm gonna hover over the map and find its control buttons, which are showing up underneath. I'm gonna grab the pen visual, and now it's on existing dashboard, and it's the one that we have in this workspace. So we're gonna just click pin. I'm gonna hover over the technology and experience chart and use the pin, and I'm gonna do the same for the card visualization and use the pin. 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 dataset, workspace, and let's open up the dashboard that we created. We wanna 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 wanna 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 gonna

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 gonna go Access Power Automate. You can use, if you wanna 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 wanna open Power Automate in a new window, you can use the Vertical Ellipses and say Open and 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 gonna start from a blank. So on the left side you have the menu, and on that menu you're gonna 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 beyond in the service, which is online. We're using a form that we created in Microsoft Forms, which is also online. So we're gonna do an automated cloud flow, and the first thing we have to do is give it a name. It says add a name, or we'll generate one. We're gonna name the 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. And if it's not showing on your list, when a new response is submitted, that's the actual trigger we're gonna 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 gonna go with when a new response is submitted, and we're gonna click on create. So now for this particular trigger, you have to tell it which form due to dropdown next to pick a form, and your streaming survey should show on that

list. So that's the first step. The 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 Form step. So in the search box, go ahead and type forms just to narrow the list. I really can tie 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 dropdown and select streaming survey and click in the response ID field. You'll get this popup 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. Their 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. And the action that we want is add rose to a data set. For that one, we have to tell it which workspace. So do your workspace drop down and you're gonna 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 dropdown 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 realtime data. So 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 gonna click on for experience. Click in the box. And what is your current experience level on the technology is the question. And for location, you're gonna 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 gonna do is I'm gonna be a respondent for my own survey, and I'd like you to do the same thing. What you're gonna do is click on your survey. If it's not there, you can go to all my forms to find it. So I'm gonna 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 gonna 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 gonna just return to my streaming dataset, workspace, and I'm gonna select a streaming report. Once it loads, I'm gonna go up to more options and edit it. So it looks like report view in the desktop, I'm gonna 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 pain, expand realtime 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 pin visual push pin above it, and we're gonna pin it to our streaming dashboard. Go ahead and click pin. And then I'm gonna navigate

to the dashboard from the popup. It's gonna ask me if I wanna save changes to the report and I'm gonna choose save. So now on the dashboard, I have both the empty one and the populated one. I'm gonna remove the empty one by going to its more options icon and choosing delete tile. I'm gonna scroll to the right so I can see the more options for the populated chart. And I'm gonna 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 gonna 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 dropdown next to light, which is the default theme, you have dark, colorblind,

friendly, and custom. I'm gonna select dark, and you know, that could be hard to read. The part of the dashboard that I see behind. It could be for good for some people. I'm gonna go back to the dropdown and select colorblind friendly, which really looks a lot like whiten here, but apparently just like in the service, they have a colorblind friendly theme. And then I'm gonna 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 gonna do that

by accessing the color dropdown. And I'm gonna choose, you can make the color choice that you'd like, but I'm gonna choose like this light teal color. You can have a background color for the dashboard tile, so the things that are on the dashboard if you'd like. Why don't you take a few moments and go through those settings and figure out the way you want it to look. Once you're done 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 gonna 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 gonna use the back button. You can add an image, a text box, or a video here. I'm gonna go ahead and click on video, and then I'm gonna click next. So it adds the ad

video tile box here and you have to have the video r l. 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 gonna click on that

so that the video page comes up. Now I just stopped the video from playing because what I wanna do is go all the way to the top of the screen and see the address bar where the U r L is. So I'm gonna select, I'm gonna click in the address bar at the top of the screen and I'm gonna do control C to copy that U R L. 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 paint on the right, we're

gonna 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 Learn it in the subtitle just so you can see how that looks. And in the content section for the video U R L, you're gonna do Control V as in Victor to paste. And now you're gonna click the apply button. You saw the popup that said the content was added to your dashboard. So everything you add to your dashboard at this point is gonna 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 Learn. 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 K P I and gauge visualizations and Power bi. I'm gonna 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 this feature. I'm gonna 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 gonna click on add alert Rule. It defaults to being active. You can have alerts set up and inactivate them that if you don't wanna use them for a while and then just reactivate them when you want to use them again, we're gonna leave the default title it again, repeats. And in here it has the name of the field, the actual name of the field, and the condition defaults to above. You have a

dropdown where you can choose below and the threshold, we're gonna 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 wanna get alerts every hour, you can change that way. 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 gonna uncheck to send me an email too, Bach Box. It's recommending that

you can also use Power Automate, where we set up our flow to trigger additional actions. We're gonna 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. Um, depending on your resolution, it might already be expanded, but I'm gonna 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 gonna introduce it to you now. It is called q and 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 and 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 the service, but the end result will be the same. It's gonna 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 and 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 gonna 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 gonna do pen, and in the upper left-hand corner of the screen, you'll see an arrow and it says Exit q and 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 wanna spend a

few moments going back to q and 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 gonna

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 sample, a subset of your data was analyzed and the following insights were found. There's

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, total downtime, minutes max, and total defect reports. So it gives you visualizations as well as the written insight. And just like q and 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 pen in the upper right hand corner, and I'm gonna go ahead and pin it to my dashboard. You can scroll through these other insights and if you see anything else that you wanna add to your dashboard, go ahead and pen it. I found another one. The count of plant by category that I'm gonna add to my dashboard. And then I'm just gonna 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 and 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 gonna show you how to set this dashboard as a featured dashboard. You're gonna do it by using your More Options button and choose set as featured. It wants you to confirm that you do wanna set this as your featured dashboard, so I'm gonna 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 pain, 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 gonna actually go to the dashboard, we just wanna 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

gonna 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 gonna go back to our workspace and we're gonna 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 gonna go to the edit dropdown and we're gonna click on mobile layout and it changes the view. 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 think 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 gonna do is we wanna keep the top two tiles. Cause 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 unins. . And for this column chart, I'm gonna do the same. And you can see the unpinned tiles here. Now there's more on here. We really just wanna keep the cards, so it pretty much grabbed everything, but you

could only view a certain amount in the mobile layout. If you wanted to view more, that's fine, but we wanna 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 pining 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 Gage card and K P I visuals. At this time, you got introduced to q and 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 Cato, and I'd like to welcome you to Microsoft Power bi. Module nine will guide you through how to create page reports in Power bi. These types of reports are designed to be printed and or published. They can also be

exported to P D F 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'll 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-ag 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. Page 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 page reports and 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 page 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 gonna use the Power BI Report builder to create paginated reports. We're gonna design a multi-page report layout. We're gonna 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 P D F. So I'm back in the service and I'm in my Power BI video workspace. And the first thing we wanna 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 gonna click on that arrow. if I scroll to the bottom of this screen, under more ways to create your own content, I am gonna click on samples. And these are some sample data sources that they have within We're gonna look for the supplier quality analysis sample tile, and click on that. And it brings up a subsequent SC screen. You can learn more about it. It tells you about

this data that's in here. And we're gonna 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 gonna 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 wanna see what this will look like if we were to print it, to show it in print preview. I'm gonna do the dropdown 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 and print preview for that. So this is the difference between a basic Power BI report visualization and a paginated report. And I'm gonna 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 gonna do on the left side is I'm gonna go to my workspaces icon. I'm gonna 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 gonna go to the more button on the right side of my workspace and access workspace settings on the top. I'm gonna click on the premium tab and I'm gonna 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 gonna 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 acc uh, 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 gonna 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 gonna navigate back to my workspace, my Power Bi Video Premium Workspace, and I'm gonna just use the link in the upper right hand corner to get back to it. In order to access Report Builder, we're gonna 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 gonna launch Power BI Report Builder 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 gonna use the table or Matrix Wizard. So I'm gonna just click on that and the first screen that comes up is to choose a data set. We're gonna connect to the supplier quality

analysis data set that's in our premium workspace. So we're gonna 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 gonna click on the new button and we're gonna name our data source here where it says data source one, we're gonna just name it Supplier Quality Analysis. And underneath that, where it says Select Connection type, it defaults to Microsoft SQL Server. We're gonna do the dropdown and we're gonna choose Power BI Data Set. In the connection

string area to the right, you're gonna click on Build. So it's gonna 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 gonna go to my Power BI video Workspace, and I'm gonna 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 gonna 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 gonna click okay and then I'm gonna click okay again. And now it shows supplier quality analysis as my data source connection. And I'm gonna do the next button in the lower right hand corner, honor in the new table or matrix dialogue box, it says Design a query. Build a query to specify the data you want from the data source. We're gonna 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. Pain under Model on the left hand side. In that list, we're gonna 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.in front of it. We're gonna click and hold on that field and drag it right into the space over here and drop it. So now it just shows vendor there. On

the left side, under model, we're gonna expand measures and then expand metrics. And there are two that we're gonna wanna choose from here and we're gonna drag it into the query window. The first is total defect quantity. So we're gonna 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 gonna drag that into the query window as well. , these are the three fields that we're gonna

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 gonna go ahead and click next. The 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 gonna 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 are 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 dropdown next to either of those measures, you'll see the other functions that are available. We're gonna leave it on sum and we're gonna choose next at the bottom. So this one is just preview of the report. So it's gonna have the vendor, the total defect, total defect, quantity, and total downtime in minutes for each

vendor. And now we're ready to go ahead and click finish. Once we click 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 gonna save this report as supplier quality analysis. Notice the type of report is report definition language r D l Go ahead and save. So it updates the title bar. We know that we save the work that we've done so far. The

first thing we wanna 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 gonna right click in the second. You can see where it says sum total defect, the one that's not bolded. I'm gonna right click on that. And on the shortcut menu, I'm gonna hover over row group and click on group properties. On the left side of the group properties dialogue, I'm gonna click on sorting and I where it says sort by vendor, I'm gonna do the drop down and I'm

gonna choose total defect quantity. And to the right of that in the order I'm gonna 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 tick 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 gonna click okay. So when we ultimately run the report, it will be sorted in descending order by total defect quantity. Go ahead and save. Again, you wanna 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. Couple of things I notice is we might not want our headings to wrap within the cell like that, so we might wanna 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 in, 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 gonna address our column width. So if you click in the little

table area there, what you're gonna wanna do is you wanna 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 gonna click and hold, and I'm gonna just drag it. So I see the full title total defect quantity, and it's not gonna wrap in the cell like it did on when we ran the report. I'm gonna 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 gonna click away from it. The next thing we're gonna do is we're gonna replace this report title where it says click to add title, right. We're gonna replace that with a text box with our report title in it, and we're gonna cause that text box to repeat on every page of the report. So what I'm gonna do is I clicked where it said click to add title, but I'm gonna 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 gonna 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 gonna 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 gonna click as far as I can get into the upper left corner of the report design canvas. I'm gonna click and hold, and I'm gonna just

draw a text box about that size. Your insertion point is already in the text box, so we just need to type the title of this report, and that's gonna be total defects and downtime minutes per vendor. So I'm gonna just type it in that text box, total defects and downtime minutes per vendor. And I'm gonna select the text. And on the home tab you have a font group. I'm gonna use that big A to increase the font size. So I clicked it once, I'm gonna click it again and again. So I clicked. Upper case A

to increase font three times for our title. Once my title is formatted the way I want it to be, I'm gonna select a 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 property's 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 wanna find underneath other. You're gonna click where it says repeat with click to the right of over there. And when you access the dropdown to the right of repeat with you only have two choices, none and tablets. One, we're gonna choose tablets one, 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 in 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 wanna 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 gonna 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 gonna 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 gonna toggle fixed data from false to true. And there's a dropdown to the right of false that you can use to select true. You're gonna toggle keep together from false to true. And you're gonna toggle repeat on new page from

false to true. And I made an error in that selection. So toggle, keep together. Back to false. And what I meant to say is, you're gonna 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 wanna keep them with the group that's coming after it. And we wanna repeat the heading on new pages. We're gonna return to the drop down to the right of column group and turn off advance mode. And then

go ahead and save your report again. Now if you look at the report data pain 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 gonna appear over multiple pages. We're gonna 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 gonna just save the report again, and now I'm interested in publishing it to the work. So on the home tab, the last button is in the share group and it's published. 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 publish button is dimmed out. That's because that's not a premium workspace. I'm gonna click on my Power BI video Workspace, and then when I click in the file name box, I'm gonna type supplier quality analysis. And notice my publish button is active here, so I'm gonna 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 gonna 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 wanna start by going to the workspace. So I'm gonna 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 gonna 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 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. In 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 gonna click on preview and it may take a moment to load again, but when I preview the

report and there's my page navigation at the top, and I navigate to the next page, it's gonna load every time for a page. You'll see that it's not repeating the report title, it's just gonna repeat the column headings. The report title is a text box object. So that's really, if you wanna repeat it, it's only gonna 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 gonna 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 wanna 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 gonna expand those built-in fields again, and this time I'm gonna drag. overall page number into the footer section on the left side, and I'm going to save this report. Click in the pain. It's considering it already saved. I'm gonna collapse built-in fields here. And I'm gonna 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 gonna use the same one. We're gonna basically override it by publishing. It lets you know that that item already exists. You

wanna replace it, we're gonna click on yes, and then you can open it again in Power bi. I'm gonna 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 P D F 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 page report in Power BI Report Builder. By creating and configuring a data source and a data set. We used a 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 gonna do now is we're gonna go back to that Get Data arrow in the lower left-hand corner, we're gonna scroll down and in the lower left click on samples. And this time we actually wanna pull the desktop file,

not the report and data set into the service. We actually wanna just download the desktop file. So we're gonna click on sales and marketing sample tile, and instead of clicking the connect button, which will give us the data set in any reports, we're gonna click on Learn More. Right underneath the connect button, it's gonna 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

this 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 P B I X file, which is the desktop file. You'll notice you can also download the Excel workbook if you want it to, so it walks you through. You can get the sample from the service. If you keep scrolling down, you can get the P B I X file. There's a link there to the P B I X file, so I'm gonna click

on that link and it's gonna start downloading the file for me. Actually, I have it downloading twice cuz I click the link twice, but it's okay. I can stop one of them and once it's done downloading, I'm gonna 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 gonna launch it and it's gonna 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 Ence, which works in conjunction with Microsoft, and we don't need that page. So I'm gonna just hover over the info page tab and do the little X and it's upper right hand corner. It will always prompt you if you're gonna delete a page. So I'm gonna go ahead and choose delete on the prompt, and it's gonna start rendering the images on each of these pages. So we have a market share page, year to date, category, page, page, a sentiment page, and a growth opportunities page. If it needs us

to use Microsoft R 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 gonna 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 access and total units for values. So we're

seeing all the segments. If we wanna take a look at the data first, let's go to data view on the left hand side, and we wanna 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 gonna click on in the fields pain, so we can see what's in the sentiment table. If we scroll down in the fields pain, 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 a 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 wanna group the youth and regular segments together. So in the column chart, I'm gonna click on youth. I'm gonna hold down my control key and click on regular. So both of those segments are selected. I'm gonna write click on youth or regular, and I'm gonna 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 Pain, 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 pain. So regular in 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 Benning, which is performed on numeric fields. We're gonna 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 wanna select the clustered column chart visualization in the visualizations pain. And I'm gonna go ahead and expand the size of that chart. We're not going for exact here. And I'm gonna name that new page, Benning, 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 slash 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 gonna build this clustered column chart. Um, we are going to, in the field pain, we're gonna expand the geo table and we're gonna drag region, make sure your chart is selected here. We're gonna drag region to access in the visualization pain. We're gonna expand the sales fact table and we're gonna grab the sales dollars and put that in the values field in the visualization

pain. So now we wanna 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' pain. And we are gonna expand, make sure your date table is expanded in the fields' pain. And we're gonna 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 gonna 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 okay. After you change the bin size to five, and just like a group, it creates a new field in the field's pain. So in your date table, you have your year bins. I actually have two of them in there. I'm gonna 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 gonna 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 is 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 gonna do is we're

gonna make a duplicate of our binning page. So I'm gonna right click on the bidding page tab, and I'm gonna choose duplicate page. I'm gonna rename the duplicate of binning to drill down slash up, which is the name of the feature. And the reason why we did this is because we're gonna create another column chart that's very similar to this one. So instead of starting from scratch, we're gonna just modify this one. So I'm gonna 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 are gonna create a hierarchy first of the region field, and we want it to include the region and the state. So in your fields, pain in your geo table, what you're gonna 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 field's pain. 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 gonna right click in the field's, pain 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 an up feature in your visualizations pain. Do the X to the right of region in the access box to get rid of it, and we're gonna 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 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 gonna 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. In 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 are 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 gonna 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 wanna get back to region, I would use the up arrow, which is now enabled, which says drill up. So I'm gonna 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 Citi to be an I hierarchy as well. So in the fields, pain in the geo table, I'm gonna just right click on Citi 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 gonna do my drill down button and it's gonna 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 pain, but we need to get the city to be in the access box as well. So what I'm gonna do is I'm just gonna uncheck region hierarchy and then I'm gonna drag it back into the access box. So now we have region, state, and city in the access box. I'm gonna go

back to enable to turn on the drill down feature. So on the right side, and now I'm gonna 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 gonna use the drill up button to drill all the way back up to the top level, which is region. And then I'm gonna 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 gonna get into now before we get into artificial intelligence visuals is analyzed. It's on every single report page that you have in Power BI Desktop. And to get to the feature, I'm gonna still be working on

a drill down up page right now. I'm gonna just select that visualization and I'm gonna right click in a blank area of it and I'm gonna 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 caused the distribution of sales dollars by region to change the most. So California has 14% of the records, Texas 6.5% of the records,

and Florida 6% 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% of the records, 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 Adel. So that's coming up there. . And so no, 77% 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 wanna keep a tile. I'm gonna just scroll back up to the top and I wanna keep this first tile in it's upper right. I'm gonna click the plus

sign to add it to this page. Now there's also a thumbs up in the thumbs down. The Power BI people at Microsoft are really committed to listening to end users. So if these analysis 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 gonna go ahead and click the plus sign. So I add it to this page, and then I'm gonna click on a blank area of the page. And now I'm gonna resize

my column chart and then resize the analysis that came in at the bottom. I'm gonna 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 gonna 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 pain. I'm gonna 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 and a in module eight, and we're gonna 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 gonna go up and click the key influencers icon on the insert tab. We wanna

expand the framework so it fills the canvas. And when we look in the visualizations pain, there are three fields for it. Analyze, explain by, and optionally expand by. We're gonna use something that's in the sales fat 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 place two tabs on your visualization key influencers, which is the default tab, and then top segments. And it gives you what influences some of revenue to increase. And if you do the dropdown, you'll see decrease. We

will go over this in its entirety. Once we're completed the visualization, we're gonna 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 wanna explain by is manufacturer. So I'm gonna just drag it and drop it in there. Now your visualization says, no influences found. Try adding some more fields into explain by. So we are, we're gonna 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 wanna 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 Adel. 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 Rsk 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 AK is the tallest column. Now at the bottom there's a checkbox. 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.5 M bubble there, and it gives you more detailed information. Basically just in

text form, this influencer contains approximately 12.61% of the. 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 dropdown 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. 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 summer revenue more likely to be low? And you have the other option is high. We found five segments and rank them by average as sum of revenue and population size. You can select a segment to see more details. So I'm gonna click on the bubble for segment one and it opens up more information underneath it. So you're getting the manufacturer is poem 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 gonna do the

dropdown next to low and change it to high. And when I change it to high here, it's only showing the highest segment. Gonna 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 gonna 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 gonna do in the visualization pain, you're gonna 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 gonna start at the top in the field in the visualizations pain, and work our way down. So we are going to want to expand the product table if necessary, and we're gonna use the category field for details on the scatter chart. We're gonna

add our segment groups field to the legend and from the sales fact table, we're gonna want total units year to date. So I'm gonna expand that table so I can see the full field name. And we're gonna grab total units year to date and drag it to the X access field. We still have more fields to add, so we're gonna use the size field here. And we're gonna use total units from the sales fax table in the size field. And one more for the play axis. We're gonna expand the date table and we wanna grab

the year field, not the year bins, just the regular year field, and put it in play access. So now you'll see your scatter chart. Let's go ahead and save our file. Scatter charts and power bi come with a play access. 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. We're just two lessons away from being able to format all of the charts we are creating during this module. Right now we're gonna 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 pain, 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 gonna add the date hierarchy to the access Box in your visualizations paying add sales dollars from the sales fact table to the values field. And we're not gonna 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 gonna click on the analytics icon right underneath your visualizations, and it opens a whole nother set

of categories. One of those. The second one from the bottom is forecast. Go ahead and expand the forecast category and we're gonna 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 dropdown 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 gonna choose years. Nothing's gonna change on our chart. We just decide that we want the forecast length to say, 10 years. Now we're gonna tell it to give us our forecast, but ignore the last two years. So we'll 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 gonna change to zero to a two. 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%. 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%. There's a dropdown arrow and you can select 75% 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 gonna change the seasonality

to five points. And again, you're gonna 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 forecast, 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 gonna do the color dropdown and I'm gonna choose an orange color, an Oranges 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 dropdown, 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 gonna do the dropdown and select none there, and it goes away. I actually like having the confidence band, so I'm

gonna do the dropdown 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 ellipses 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 app source, 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 that 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 gonna 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 okay to get rid of that message. Now, when you look in your visualizations pain 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 pain, 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 pain and you would have to add it again. The alternative is to pin it to your visualizations pain.

So when you find a custom visual that you're gonna wanna use over multiple files, pin it to your visualizations pain, and you can do that by right clicking the violin plot and choose pen to visualizations pain. It will move its position and it will end up being the last visual in your visualizations pain. Go ahead and click the violin plot visual so we can build this visualization. I'm gonna 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 wanna 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 gonna use all three fields. So the next field is measure data. And from the sales fact table, we're gonna grab the

sales dollar field for measure data. And then for the category, we're gonna 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 the, 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 bending page in module

10, you got to explore advanced analytics by using grouping bending, 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 used some ai, artificial intelligence visuals, some of which identified outliers in our data, and that would include the q and 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 gonna 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 dataset roles, and publishing an app. We're gonna be conducting this module in the service, so go ahead and get yourself in the service. We're gonna 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 gonna click on share. And it opens the share dashboard dialogue. 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 re-share 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 re-share your dashboard, make your choices with the checkbox. Go ahead and enter an email address and click the grant access button. At the bottom, you'll get a popup that says, success access has been granted. Now what else can we do with this? Let's say you accidentally gave someone privileges to re-share your dashboard and you wanna revoke those privileges. You simply go back to share. And in the upper right hand corner, you'll see the more options ellipses. Go ahead and click it from here. You can manage permissions, which we're gonna do in a

second. Or you can copy a dashboard link that you wanna 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 a lower right hand corner, go ahead and click the advanced. 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. On the right side of the screen, you'll see yourself as the owner of this dashboard and you'll see the privileges that you granted the person you shared it with. So the ability to read and re-share to the right of that is the vertical ellipses. And when you click on that, you'll see that you can remove just the re-share privilege or you can remove access. Totally. I've moved back to my workspace and now I'm gonna 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, uh, versus the 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 gonna 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 re-share 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 gonna click apply on this screen. Go ahead and enter an email address and then you're gonna 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 popup 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 gonna go back to share and in the upper right hand corner, I'm gonna 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 wanna 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 re-share the related content. Just like with the dashboard is 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 gonna do that now. We're gonna

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 gonna leave it with the same name. And then you get to select a destination workspace that you have access to. I'm gonna go to a workspace called Training Workspace and then choose Save. So it gives me a popup where I could go to that report and I'm gonna click Go to report on the popup. 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 gonna hover over the workspace that you wanna give somebody access to. In this case, it's my training workspace and I'm gonna go to the more button to the right of it. And I'm gonna 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, contribute, 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 a 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 re-share. They can feature apps on colleagues homepages if

they have permissions to do so and manage data set permissions. Contributors can only do this, 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 a admins, members or contributors, where you can learn more about those roles. So on this screen you're gonna enter an email address. You're gonna choose the level that they have, and then

you're gonna 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 in it can be configured with multiple dashboards and multiple reports. As you'll see, I wanna go back to my Power BI video

Workspace. So I'm gonna use my Workspaces icon on the navigation pain 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 wanna do first before going in here is you wanna un toggle the things that you don't want to be in the app. The dashboards and reports. So we're gonna cancel this screen, and on your workspace screen you're gonna 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 un toggle this. Include an app. I'm gonna switch these to no, and I'm gonna 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 gonna leave those three on. Yes. And now I'm gonna go back into create an app. And 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 U R L. 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 wanna change the order of things that are gonna 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 wanna 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 organiz. Or specific individuals or group. 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, is 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 are 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 gonna do here is I'm gonna go ahead and put in an email address and then I'm gonna click the publish app button. When you click publish app, it lets you know

that it can take within five to 10 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. So it built my app 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 and 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 de navigation pain here. So if you wanna 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 homepage. At this point. On your homepage 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 and Power bi specifically, you'll learn how to set parameters and how to use the data set. Refresh options. We're gonna be using the sample superstore OD desktop file that was created in module two 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 dataset and then your visualizations. A 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 wanna 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 PowerVI desktop, you're gonna 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 managed parameters icon. Go ahead and click that icon. The managed parameters box opens and it's very tiny in here, but we wanna create a new parameter and the new

link is right underneath the managed Parameters heading. So go ahead and click on new. You get to give your parameter a name. So the parameter that we are gonna create is gonna be a parameter of the regions that are in the orders query. So we're gonna just name the parameter regions. We're not gonna give it a description. We're gonna go down to type, and we're gonna select text. For suggested values, we're gonna supply a list of values, and you're gonna click next to row one and type East. When you click right underneath east, it gives

you row two. You're gonna type central for the next row. You're gonna type south and the following. And the last one, we're gonna type West. So after you type west, click on the next line. Even though we're not gonna add anything there. Once you click on the next line, then the previous entry is accepted. So when we go to the default value dropdown, we're gonna default it to East and we're gonna use the same for current value. And now we're gonna click okay. In your queries paint on the left, you'll see the region's 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 dialogue 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 gonna check, always allow. And now let's go over to our orders table and use our per. So when we get to orders, we're gonna

scroll to the right until we see the region field. And I'm gonna show you how to use the parameter that we just created to filter that field. We're gonna do the auto filter dropdown next to region. And if it says lists may be incomplete reminder, you wanna load more. So you see everything that's in that list and we see the regions there. We're gonna hover over text filters and choose equals where it says keep rows where region equals in between equals and enter or select a value. You'll see an a b ABC icon that represents

text. We're gonna click on that icon and we're gonna 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, okay, 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 gonna 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 gonna go ahead and click the last button on the home tab, which is publish. We're gonna republish this to the service. It's gonna prompt you to save your changes and I'm gonna 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 and one dashboard. And we're gonna 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 popup. It opens in report view in the service, and what we wanna do is go up to more options and edit. When we get into the report, we're gonna do the plus sign for a new page, and we're not gonna select a visualization upfront. We're gonna expand the orders table

in the fields. And from the orders table, we're gonna wanna drag. Let's see, what do we wanna drag into this? We're gonna 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 gonna drag sales into the framework of the table. So now we're seeing the total sales for the east region, and we're gonna go up to file and save this report. Once the report has been saved, you're gonna

navigate to your workspace where the report resides. So at this point, we are in the service and we decide that we wanna change our parameter, we wanna change it from east to west. You can do that in the service. And the way you do that is you're gonna hover over the sample superstore od dataset and go to its more options. And when you select that, you'll see settings on the list and you're gonna click on settings. So in settings you have a category called parameters and you're gonna 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 gonna just double click east and I'm gonna 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. Thanks. So now it updates and it's set to West. I'm gonna go back into my workspace and I

wanna 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 gonna go back to my workspace and I'm gonna hover over the sample superstore OD data set and you'll see the refresh now button. I'm gonna click it, it's the circular arrow, and then I'm gonna 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 gonna 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 clicked the refresh button for the data set and then for the visualizations, let's go back to our workspace and we're gonna 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 gonna scroll down underneath parameters. We're gonna go to scheduled refresh and we're gonna 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 dataset 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 refreshed daily, multiple times a day. I click the add another time link, and I'm

gonna 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 gonna 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 two. Hover over sample superstore dataset, 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 the 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 OD one that represented OneDrive when we created it

in module two, it's a 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 reimport 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 P B I X 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 and 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.