Power BI : Power BI Essential Training
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
The is a Full Course in LinkedIn : Learn Microsoft Power BI Desktop
Power BI Essential Training ::
Requirement : Hardware
Available RAM : 1.5 GB or more recommended (1 GB minimum) in order to run power BI . He is running 32 GB of Ram in his computer
CPU : 1Ghz or faster x86 - or 64bit processor
Display setting is something you will never have to adjust. 1440*900 or 1600*990
Display setting for text 100% or less
Software ::
Windows 7 / Windows Server 2008 R2
.NET 4.5
Latest Browser :
Power BI Pro / Power BI
Three different ways power BI is licensed : Power BI is the free version (This will allow you to do 90% of the work what we do in the course )
Power BI pro has a monthly license associated with it. It might be already included with the system Office 365 that you have or you can get a 60 day trial for Power B1 pro .
We will also be using Power BI desktop , which is a free download which sits on your computer and which allows you to model and transform data , Additional there are some apps for mobile devices .
Power BI is all about visualizing data and sharing those visualizations with others
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
DATA SKILLS
If you are working with Excel and you know how to
- Enter , Edit , format text and numbers
- Format a Table
- Find and Replace
- Sort and Filter data
- Insert and format charts
1 . Create Rich , Interactive reports using Power BI
Skills covered ::
- Business Analytics
- Performance Dashboards
- Microsoft Power BI
- Data Visualization
1. Getting started with Power BI ::
- Overview : Power BI Concepts
- SignUp for powerBI
- Navigating in the power BI service
Overview: Power BI concepts ::
Selecting transcript lines in this section will navigate to timestamp in the video
- [Instructor] A simple understanding of business intelligence is that business intelligence is about connecting business decision-making to facts about the business and its environment, to take a deep dive and understand the data underneath your business, so that you can make better business decisions. We start by getting data from one or more sources. If we have multiple sources of data that we're tying together, someone needs to build a model that expresses the relationships between the data sources. Using that model of the data, we will create visualizations, charts, tables, and so on, that we can share with our colleagues. And with all of this information summarized and illustrated in a way that's accessible and useful, our team or our department or organization will be able to make better business decisions. Until recently, business intelligence was big business, enterprise BI, and the players were big players. SAP and Oracle, IBM, Microsoft, and so on, large companies creating really large enterprise tools. And enterprise BI was the realm more of the IT professional than any other group. That meant that IT folks were required to develop a great understanding of what was needed in the business so that they could retrieve exactly the right data to create the reports that end users and business managers were requesting. Really, the first alternative to this type of enterprise business intelligence was Microsoft Excel, because Microsoft Excel allowed users to analyze data for themselves, even if they had to take it offline, even if they had to use last month's data. Excel is a popular business tool, precisely because so many people learned Excel to be able to get a better handle on their business, on the data for their product, their department, their functional area. For many of us, Excel was our first step into self-service business intelligence. And the first version of Power BI was built almost totally in Excel, and with add-ins for Excel. Microsoft has continued to evolve Power BI, and it now has a set of tools that are not Excel-centric. Before we begin, then, let's have some core understanding of what it is we do with Power BI. First, we will create datasets, the models I referred to a moment ago, which could be data all from one source, or from multiple sources. We'll use those datasets to create reports. Don't simply think of rows and rows of figures with labels. Reports in Power BI can be amazing. Funnel charts, tree maps, geospatial maps, and different types of visualizations. These reports aren't meant to be shared directly. If we want to share the information in our reports, we'll take parts of those reports, some of the visualizations, and we'll create dashboards, and this is very easy to do. We have a number of components in Power BI. We'll focus on a few of them in this course. First, the Power BI service, also called powerbi.com, or what most people mean when they say, "Power BI." It's web-based, and for many users, this is the only tool they use, because the Power BI service allows users to manipulate visualizations to do a deeper analysis of business information. If you are an end user, you may spend almost all of your time in Power BI. But if you are a business analyst or a power user, then you will probably also use Power BI Desktop, a free download that sits on your Windows computer and allows you to do things that we previously did only in Excel, to model our data, to transform our data, and we can also create reports that we publish to the Power BI service. There are also mobile apps for Power BI, for iOS, Windows, Android. There are some other components in the Power BI ecosystem that we will not be focusing on in this course. There's a visuals marketplace which is where you can get custom visuals that aren't included out of the box with Power BI, some for a cost, some for free. There's a Power BI gateway. Gateways are used to synchronize data coming in and out of Power BI from enterprise systems. And if you're already using something like PowerApps or Flow, and you have gateways, they're the same gateways. Power BI Embedded is a subset of Power BI that's used to include dashboards and reports and custom applications. Power BI Report Server is used in the place of Power BI service for companies that don't have their data stored in the cloud, for companies that have all of their data on premise. So you can think of this as Power BI service on-prem, in a way. And finally, there are a growing set of developer tools for Power BI. Regardless of our edition, we're going to use the tools in Power BI in exactly the same way. We're going to get data using either the Power BI service or Power BI Desktop, we're going to model that data, and if we need to do that, we will do that in Power BI Desktop. Create visualizations and reports using either the service or the Desktop, and then share them with your team, using either Power BI service or Power BI mobile apps.
Sign up for Power BI ::
Selecting transcript lines in this section will navigate to timestamp in the video
- In this video we're going to sign up for Power BI. If you already are signed up, you already have a license, just bear with me and hang out while the rest of us do this. I'm going to go to either powerbi.com or powerbi.microsoft.com. They both resolve to this same page. There's a start free button here and a sign up free button. These buttons will actually point you towards Power BI desktop. We'll be installing that later in the course. What I'd like you to do, if you don't yet have Power BI, is go to Products, Power BI Pro. That will take you to this page and this is a link to sign up for a free subscription to Power BI Pro for 60 days. There is a requirement though that you must enter a work or organizational email address, a .com, .edu for example, not Gmail, not Hotmail, or Live.com. So I've entered clearly not a organizational email address and I click sign up. Notice I get a message back: That didn't work. This looks like a personal email address. Enter you work address so we can connect you with others in your company. And that's true even if there are no others in my company by the way. And don't worry. We won't share your address with anyone. So I click OK got it and then I will indeed put in my work email address the next time I sign up. What if you don't have an organizational email address? You're not a student. You don't have a work email address or not one that you can use for this purpose. In that case, there is a workaround. You can use your non-work email address, non-organizational address, to get a trial subscription to Office 365 and then use that Office 365 account, which will have on Microsoft.com as its extension to sign up for the trial for Power BI. But after I enter a work email address, (typing) I'll then be prompted to sign in with my password if I haven't already. In my case, it will tell me you've already signed up. You already have a Power BI account, so to finish signing up, just sign in using your existing password. If you don't yet have a Power BI account at this point, you'll also be prompted to provide a password and some other authentication to make sure that you are you. And when you're all done signing up and have your license for Microsoft Power BI, you'll then log in and you will be in the Power BI service, powerbi.com. If you already have your account then, simply click here to sign in or if you are already running one of the Office applications, click the waffle and choose Power BI to launch the Power BI service.
Navigating in the Power BI service ::
Selecting transcript lines in this section will navigate to timestamp in the video
- [Instructor] I'm logged into the Power BI service and let's just have a quick tour here. This is our interface to be able to connect to items that we've created, items that have been shared with us. On the left hand side, we have a navigation pane that has a collapse and expand button here at the top, so we can hide the pane or open it back up. At the top, we have a list of favorites and frequents, and if you've just signed in to Power BI, there may not be anything here, but this is a mix of reports, dashboards, workspaces, and apps, and you'll get familiar with these icons, these are all items that I've used frequently by count, not most recently but frequently. If I want to favorite an item, I can, for example, if I want to ensure that the Retail Analysis Sales Manager Dashboard is always at the top, I simply click the star to favorite, and it is now at the top. If I want to remove it as a favorite, I actually need to go to my Favorites and unmark it as a favorite, and then it is simply once again an item that is frequent. Below my favorites, I have the items I've most recently used, like that Retail Analysis Sales Manager Dashboard. Workspaces, until recently, with the exception of My workspace, which is my default workspace in Power BI, these other workspaces were created because an Office 365 group was created, now we can create workspaces in Power BI that don't automatically create Office 365 groups. We'll talk about workspaces later in the course. If I want to look at all of the workspaces, I can open up the list, and if you have a lot of groups in your organization, you might want to be able to search for a particular workspace, and notice that works quite well. If you switch to a particular workspace, then you can see that that workspace might have Dashboards, Reports, Workbooks, Datasets, and Dataflows. If I look at My workspace, always listed at the top, My workspace has Dashboards, Reports, Workbooks, and Datasets. If I want to open any of these items directly, I can, for example, if I wanted to look at a specific report, I can simply go to Reports and click on that report to load it. A very simple report. Click Home to return to home. While Dashboards, Reports, and Workspaces are commingled here within Power BI home, on the left, they're not. On the left, if I open My workspace, notice I have first my Dashboards listed, then my Reports, followed by my Workbooks, followed by my Datasets, and I can click on any of these to expand and collapse them. I have a search box so I can search my workspaces for a particular report or dashboard. If I click the more button, you'll notice that we are seeing the new look for Power BI here because the new look is turned on. If I turn it off, it will take a moment and it will switch me back to old look of Power BI. If you have this old look for Power BI, you won't have it a lot longer. It's very similar but it has a little bit more break out here at the top, and I'll turn the new look back on again. Where you'll see the biggest differences in the new look is not here on Home, but when we are actually working with reports in Power BI. Other items that are here, Settings, the ability to download Power BI Desktop, Power BI for Mobile, get more help and support, and provide some feedback. If you're working on a public computer in Power BI, it's a good idea to sign out when you are done. This is Power BI Home.
1. Get started ::
- Overview : Power BI data sources
- Get existing content
- Creating refreshable data files
- Upload a CSV file
- Upload an excel workbook
- Connect to a sample
- [Instructor] When you click the get data button in the lower left-hand corner, you end up on the get data page in the Power BI service. Under discover content, we have two types of data sources. First, apps that have been created and published by someone in your organization. So someone on your team, or in the future, perhaps you will create an application. And applications can include different types of Power BI artifacts. So you might have an app that was published for use by your sales team and you would click the get button under my organization to connect to apps that have been published by other people in your organization. That's also where you would go to get template apps for online services. You're already logged in to your organization. But if we choose to get an app from an online service, typically you have to have an account and credentials so that you can log in. For example, if you want an app that connects to Mailchimp, you need to be able to provide your Mailchimp account login information. On the right-hand side, we can create brand new content. We can bring in data that we created in Microsoft Excel or stored in a CSV file, and we can also create data models in Power BI desktop and bring those in to Power BIs service. If I want to get data out of a file that isn't in Excel or a CSV or Power BI desktop, then the first task I'll have is to get that data to one of those three applications. Fortunately, Excel is a great translator. You can almost always get data either in Excel or in a CSV file. And then, under create new content, we have one more choice, which is databases. We can use Power BI desktop to connect to online databases like Azure SQL database. We can also connect to databases that are On-Premises, but they require a Power BI gateway that would be provided by your organization's IT department. For example, by your database administrator. Those databases are beyond the scope of this course. Below discover content and create new content, we have some other choices. We have samples, which are provided by Microsoft, partner showcase, which are samples provided by partners, and we have organizational content packs and service content packs. In the past, the content that was listed under my organization and services was all provided in content packs, rather than apps. And you'll still be able to connect to some of that data for a while longer, although many of the service content packs are no longer available. They've been deprecated. If you've already loaded them in Power BI, they will still be there. But when you remove them, you won't be able to add them again. And then, finally, you can work with an existing data set that has been published. This is a way that someone in your organization can give you a headstart, here's a data set you might want to use, without necessarily providing you an entire app like you would find under the my organization tile. With all of these choices, what we're choosing is a data source. When we get data and select that data source, Power BI creates a data set, which is a definition or information about the data source and that data connection.
Get existing content ::
Selecting transcript lines in this section will navigate to timestamp in the video
- [Instructor] Whether I want to get an app that was created by someone in My organization, or an app from an online service, I simply click the Get button, and I'll be taken to an interface for apps for Power BI. My organization's apps are on this tab, other apps are found here and then I have some specific choices that are not Power BI apps. These are apps for Dynamics, for Office 365, and so on. And clicking any of these choices, or this choice will take me to AppSource, which is where all of the apps are, for Dynamics, and Office, and the Power Platform. If I'm looking for a specific app, I can enter a search string and look, and this will return for example, apps for Dynamics Business Central. I can clear the search box to return to the entire list of apps, and you can scroll this, won't take you forever. There are three pages of service apps right now. And when you find the app that you want to install, then you simply click to install it. And the application that I'm looking for is the LinkedIn Sales Navigator Analytics Integration. So I want to analyze my LinkedIn Sales Navigator data. This is typical of the apps that are available. Here on the left, we have Pricing, this one happens to be free, not all of them are, there some very expensive apps, acquire using a work or school account. In other words, this is not for your personal account, but your LinkedIn Sales Navigator account, which would be a work account typically. And it says to get started, you must have a LinkedIn Sales Navigator Enterprise Plan and be an Admin or reporting user on a Sales Navigator Contract. If any of this is something you need more information about, talk to your IT department. And this is what the dashboard will look like. This particular app was built by Microsoft. I'm going to click GET IT NOW. And because this is a full featured application, it will load with sample data, so that I can install it and take it for a little bit of a spin without needing to connect to my own data. And we have a choice to install this in its own Workspace, I'm going to overwrite a prior version that I had installed at one time in the past. My Workspace has been updated, I can go there. And at the top note, you're viewing this app with sample data. To view your own data, connect, and there's a connection here. And I have Dashboards, Reports, and here's my data set. So I'm going to go first to my report, and click and it will show me again, this information using sample data. And that's what's in this particular application. Later on, I can choose to connect my data, or I might decide that I no longer want to work with this app, in which case I could remove it. But right now it's on my Favorites and frequents list, as well as on my Recents list. Simply because I chose Get Data, and in doing so, when I use My organization or Services, I get an entire application.
Creating refreshable data files ::
Selecting transcript lines in this section will navigate to timestamp in the video
- [Instructor] I'd like to connect to some data that I have stored in a CSV file, so I'm going to click Get Data, and you'll notice immediately that I have some different choices here, Local File, OneDrive Business, OneDrive Personal, SharePoint Team Sites and learn about importing files. There's a real difference between that Local File that's green and the OneDrive and SharePoint choices that are in blue. And the difference is in the refresh. Power BI doesn't have the ability to reach down into your local computer, pop open a folder, and ask for a refresh of a CSV file. The same thing is true of network shares. Therefore, if I connect to a local data source that is on my computer or stored in a Network share, not only will it not be automatically refreshed, it's not even refreshable. If I want to refresh it, I have to delete it and go through the Get Data process again. On the other hand, if I push my data into cloud storage, into OneDrive for Business, OneDrive personal or SharePoint library, or for that matter, a SharePoint list, that data will be refreshed hourly, and even more frequently if there's a change in the data that Power BI is aware of. Therefore, I'm only going to want to connect to data that's on a local computer or Network share if I don't want it to be updated later. I would encourage you if you're not sure, to simply store your data sources in the cloud, in SharePoint or in OneDrive. In order to do that, I'm going to open my OneDrive for Business, I'm going to open the folder, and I'm going to drag the Power BI exercise files that I've downloaded from the LinkedIn Learning Library, and I'm going to simply drop them here, so that they appear in my OneDrive. And therefore, when I connect to these files, I'll be connecting to a refreshable data source. But you have control over the permissions on the files and folders that you place in your OneDrive. So I have a folder that I store data in for apps. I have a folder for items that I'm sharing with Simon. If you wish, you can create particular folders and give them specific permissions in terms of sharing, but you can also simply store them here because you want to use these data sources for Power BI. And this is how you create a refreshable data source, you store your file in the cloud.
Upload a CSV file ::
Selecting transcript lines in this section will navigate to timestamp in the video
- [Instructor] I'm going to create a new data set from a CSV file, under Create New Content/Files, I'll click the Get button, and I have stored this file in my OneDrive for Business, so I choose that as the location for my data source. Power BI is connecting to OneDrive for Business, and loading files, and this is in a folder called Power_BI_Exercise_Files that I downloaded previously in Chapter Two, and it is GDP_Americas.csv. I select it, click the bright yellow Connect button, I give OneDrive for Business a moment to process the files, it tells me it could take a little while to import this data. It shouldn't take long, it's not a large file. And if I wish, I can click View Dataset to immediately view my data here in Power BI. You might wonder, "How do I know my data's here?" If you look at the Fields list on the right hand side, you'll notice GDP_Americas and my six columns of data. This is a refreshable dataset, because it's in a source that's available all the time to Power BI, and it will now be listed in my workspace under datasets.
Upload an Excel workbook ::
Selecting transcript lines in this section will navigate to timestamp in the video
- [Instructor] I want to get some data from an Excel workbook. Workbooks are a little more complex than CSV files. A CSV file is all data start to finish. But a workbook can have titles, can have more than one worksheet. So when I choose get, and I choose OneDrive for Business, because that's where I'm going to store the files so that they're refreshable, and I find my Excel workbook, which is called catalog underscore requests, and click Connect. The most important thing is that this workbook actually has its data source formatted as a table. Here is this catalog request file. And I know it's formatted as a table because when I click any cell here, there's a table design tab. And if I go to table design, this is table one, I could give it a different name if I wish. And if I have a workbook with a number of different tables in it that uses data sources, it is a grand idea to name them. But the essential is that every data source you wish to use in Excel be formatted as a table. Well, I only have one choice when I'm connecting with a CSV file. I have two different choices for using Excel with Power BI. The first is to connect to it really in the same way that I connect to a CSV file. It's out there in OneDrive, and I'm importing it and I'm using it, just as I would have CSV. But I have another choice as well, which is to be able to connect to it and to be able to view it in Power BI exactly as I would if I were viewing it in Excel Online. And then I can schedule a refresh to keep this up to date. So if I choose Import, again, this will work very much like importing a CSV. Right now Power BI is processing the file, it's reading it and looking for tables. Now it's importing. And here is my catalog requests data. If I click to load it, and if I go home, and I look in my workspace, you'll notice that my catalog request data set is listed right here with all of my fields.
Connect to a sample ::
Selecting transcript lines in this section will navigate to timestamp in the video
- [Instructor] Power BI Environment also includes free samples. The free samples will have not just datasets, but also reports and dashboards. To load the samples, go to Get Data and then below Discover content there's a link here for samples. These are the samples that are available. There are currently eight of them. Each of them includes real data that has been anonymized. And the sample that I would like to look at is the Retail Analysis Sample. If I click, it tells me that this industry sample dashboard and underlying report analyzes retail sales data. Click Connect and wait while the data is being imported. And that was really fast. If I look in my workspace it has a dashboard, it has a report, no workbook, and it brings in its own dataset. The report comes first. And if I click and open it, it will arrive with all of its visualizations. As with all of our other dashboard reports and datasets, this will be part of our workspace in Power BI as long as we don't remove it, which we could, of course, by going to our reports and removing it. So if I were to delete this report it says I would be permanently deleting Retail Analysis Sample. Just the report. And I'm going to choose not to. But something different is going to happen when I go to the dataset. First, there's not obvious trash can. I have to go out of my way to find the delete command. It's under the More Options button. But notice that if I permanently delete the Retail Analysis Sample dataset, I'm not just deleting the dataset. The reports and the dashboards will also be deleted. I'm not going to do that. We're going to return to this report and this dataset later in the course. Now that you know how to load a sample, if you're working along with me I would encourage you to load this sample, Retail Analysis Sample, because I will be using it as a demonstration for some of our upcoming work together.
3. Create a report with Visualizations ::
- Overview Visualizations
- Using Visualization
- Create a New report
- Create and arrange Visualization
- Format a Visualization
- Creating a chart visualization
- Use text, map and gauge visualization
- Use a slicer to filter visualization
- Sort, Copy and paste visualization
- Download and use a custom visual from gallery
Overview: Visualizations
Selecting transcript lines in this section will navigate to timestamp in the video
- [Instructor] Reports like the retail analysis sample report that we see here are composed of visualizations which is another word for chart although some of them are not traditional charts what all of these visualizations have in common is that they are based on the same dataset. We can construct reports from scratch, we can edit reports that already exist, and the way we'll work with reports is to modify and manipulate the visualizations. So it helps us to know what visualizations are possible. We see a number of visualizations here and some of them look familiar. On the upper left-hand corner, a pie chart. One the right side, a column chart. Lower right, a bubble chart. Lower left, a geospatial chart, or a map. And if we click the second page of this report here in the page tabs, district monthly sales, again, some very familiar chart types from Excel. But even this item in the upper left corner that lists our district managers is actually a visualization that is composed of text rather than images. On the third page, new stores. Additional visualizations. Line charts, column charts, map charts. Let's take a look at the visualizations that are available for our use in Power BI. First we have the chart visualizations. Charts in the traditional sense of the word. You'll know these from Microsoft Excel. Bar charts, column charts, line charts, area charts, which are really just filled in line charts, and pie charts. Hopefully you're familiar with all of these, because despite the fact that one of them is named pie, these are the bread and butter of chart visualizations. Then we have some slightly more esoteric charts, but we also know most of these from Excel. A combo chart, which is a combination of a line and a bar. Ribbon charts, not necessarily in the Excel province, but we see scatter and donut charts in Excel, and then we have gauges. Gauges show a gauge like a gas gauge. Very easy to see. There's a goal and we've completed some percentage of it. Funnel charts are used to show time or expenditures for different phases of a project or can be used to display a pipeline. And then finally waterfall charts used to show changes. For example, a variance overtime. Again, all of these are charts. Then we have text visualizations. A single number card or big number card is a card that simply has one number in the middle of it, but we also have multi-row cards that allow us to show details for a record. KPI which allows us to display key performance indicators where we have both values and goals. Tables and matrix visualizations are other ways to present text. We also have geospatial visualizations. Maps where each data point is a pin in a map, or a filled map where the intensity of a color shows something about the contribution in that area. For example sales. Filled maps are almost always used for recognizable geographic areas, states, or counties, or countries for example. Because if you don't understand the shape that's being filled in, it doesn't create a useful graphic. We also have access if it is turned on in your organization to ArcGIS maps for embedded location based analytics, and each and every one of these geospatial visualizations needs some geospatial data. For example, latitude and longitude can be used with maps and filled maps and all of them can use ZIP codes and addresses. In terms of other visualizations, a treemap isn't really a map. A treemap displays hierarchal data using nested rectangles. So for example if we had sales by country each country would have a rectangle but inside that country we could have smaller rectangles that represent specific states or that represent specific vendors or products. Slicers are used in PowerBI as they are used in Excel with tables and pivot tables. They're a visible filter and although almost all of the visualizations that we've talked about can be used as a filter, slicers are a clear invitation to users to filter a page. In addition to these visualizations, we have the ability to add custom visualizations. In Power BI we combine these visualizations to tell a story. On this page of the report the story of new stores within our sales organization. When we're ready to share parts of that story with others, we have a couple of choices. One is to create an app workspace and to share the report and the dataset and anything else with our colleagues so that they can all collaborate and edit it as well. The more traditional choice is to take one or more of the visualizations from this report and perhaps visualizations from other reports and pin it to a dashboard and then that dashboard can be shared more broadly. With so many great visualizations available, often you will need to focus most on choosing the visualization that best tells the story that you need to convey. Even though charts are very attractive, all of these may provide the clearest story in the dashboard that you want to share with your colleagues.
2 . Using Visualization ::
Selecting transcript lines in this section will navigate to timestamp in the video
- [Instructor] Later on when we will be sharing our data and analysis, we won't be sharing these multi page reports, we'll actually be sharing dashboards. You and I, as the owners of these reports have access to the data set and the report, but the people who we share this with, they all start with a dashboard. So let's go there and see how dashboards work. I'm going to open up My Workspace under Dashboards, Retail Analysis Sample Dashboard. We'll load and you'll notice that many of these visualizations are the kind of visualizations we just saw in the report. They're actually not just the kind of, they are the visualizations from the report. Because how we create a dashboard is we take visualizations from the report and pin them here as tiles. Our users have some specific things that they can do within the dashboard. Remember that you and I are actually the owners of this dashboard. So if we make changes, they will be lasting changes. But if a user wanted to resize an item, there's a resizing button here in the corner. So they could change the size of a particular graphic by grabbing the corner and dragging it. And other items will move in response, you'll notice. There are other options on the More Options menu at the top so a user could add a comment, or could add a specific question. This is the way you start a conversation. Got three choices here. Enter your comments here, which might be, "Could we show this as we have in the past "with one decimal place?" And if I wanted to have somebody specific answer this question, then I could mention someone. So for example, there I am. @Gini von Courter. And then I will actually get notified about this. So I can post this. Close it, have an entire conversation, and comments. And notice that I can tell that there's a conversation here, because there's an icon. I can also go to Q&A and ask a question, but Q&A is right here as well. I can open this specific tile in Focus Mode. What Focus Mode allows me to do is to see it writ large. Now, that's pretty silly with this tile. But if I look at a tile that has a lot of data in it, like this one, it might help me to be able to view this in Focus Mode, to be able to see legends more clearly, or to see my data points writ large. Each time you want to exit, there's a door out in the upper left hand corner. I can edit details, I can ask for alerts. I can pin a tile, and because I am the owner, I can also delete a tile. One of the typical things a user will do though is they will use a tile to drill down to the data, to get back to the report. So if I click this year's chains, notice that I'm taken right back to the report and your users will be as well, and in the report, they can do some other interesting things. For example, I can use one visualization to filter another. So I have sales by two chains, Lindsey's and Fashions Direct. If I click on Lindsey's, notice that all of the other tiles now are being refreshed to just show Lindsey's data. These are just the Lindsey stores, just the Lindsey sales variants. And I can click on Fashions Direct and display just the information from Fashions Direct in each and every visualization in my report. I also have choices to add comments here, to export data and so on. I can always refresh to see all of my data again. Or if I prefer, I have notice, filters here on the right hand side that I can apply. So I can say for example, I would like to look for specific districts, for specific store types, and so on. At any point, I can click the drop down to see who should be contacted about this particular report and when it was last updated. I can always go back to My Workspace to navigate back to a specific dashboard. And with the dashboards, as with the report, I can find out my contact here, and I can actually click here to send an email to this particular contact. We'll be creating dashboards later in the course. In the next few movies we're going to be creating visualizations and reports, like this one. But remember, that if your goal is to be able to share your data with your colleagues or with Power BI consumers, you're creating the report so that you can create a dashboard.
3. Create a new report ::
Selecting transcript lines in this section will navigate to timestamp in the video
- [Instructor] Within the Power BI service, we have two different methods for creating a new report. First, if there's a report that already exists that's pretty close to the report that you want to use, you could begin by saving a copy of that report. You give it a different name, so for example if I wanted to modify this report so that it was focused on district sales only, I could call it Retail Sales - District Analysis. And I will get an exact copy of this report that is based on the same data set. I could then edit this report, which we'll get to in the next movie. Now, I don't actually want to do this, so I'm going to go back to home, I'm going to go to my workspace. And delete this report that I just created. Are we sure we want to permanently delete it? Yes. The other possibility is to create a report directly from out data set. So, if I go to my data sets down at the bottom, choose retail analysis sample, I could also have done this from home from the list of data sets. This is actually a report editor right here. It's a little hard to tell when you're first working with this, but notice that if I click the file drop down, I have choices like save this report, save a copy of the report, print the current page of the report. This is a clue when you're working with Power BI. If you don't know if you're in a report or a dashboard where you are, you can simply click the file button, or click one of the commands and get a clue about where you are. But if, for example, I wanted to look at districts, I wanted to see the name of the district manager, I can simply click that check box and it is immediately generating a visualization for me in my report. And if I wanted to save this report I could, and it would be added to my list in the workspace. From the dataset itself, report is simply a few clicks away. There's a third way to create a report, and that is to use Power BI Desktop, a tool that we'll install on our computer, and we'll talk about that later towards the end of the course. But in the Power BI service, whether you begin by copying then editing an existing report, or start with your dataset by adding fields to create visualizations, either of these methods will give you a report that you can then save in Power BI.
Create and arrange visualizations ::
Selecting transcript lines in this section will navigate to timestamp in the video
- [Instructor] When you get ready to create a report then, you can start with your dataset. And this first button under actions is create report. So returning to our retail analysis sample, this is our first page of a report. It's named page one. We can add extra pages here. We will almost always have multiple visualizations. So we'll want to be able to learn how to deal with arranging visualizations. If we simply select a visualization, for example, a stacked bar chart, we'll get a stacked bar chart with no data in it here. I could then populate it with data from my fields list, but instead I'm going to click back in the report canvas and I'm going to add a map as well. And now I have two visualizations so that I can show you a little bit about how we will arrange these. I can resize a visualization that's selected by grabbing any of the handles so I can resize it in every direction here in the report. Or I can simply drag items around. Notice that I get some guidelines to help me do that so that I can arrange items centered with each other directly above or below each other. And I can relatively easily rearrange these items then. To resize them, grab any of their handles and resize them. And note that whatever item is selected, takes up more space because we have a toolbar, we have a filter button and some other buttons, but if you point then you can see what size they each are. Also when you drag them to be the same size, you'll get a guideline again that will help you for the actual size of these items. And let's add that list of district managers in that we had previously and Power BI will choose a reasonable type which is going to be text. If you ever wonder what the menus is of a visualization, it's selected when the visualization is selected, so this is if I hover over it a stacked bar chart. This is a map and this is a table. I'm going to move this item a little further down, take my list of district managers and move it up to the very top. I'd also like it to be larger and rearrange my other two charts. With our maps selected, I can add some data to it. Stores have postal codes. If I expand store, I can tell that's mappable data because it has a map icon. I can click and populate this with our stores. I want to actually change this visualization type. I want to show the total units that were sold this year, and that's just going to give me a simple bar. So let's swap that visualization type, and rather than having a chart, let's just have a card, four million total units this year. We can make that a little smaller. We don't need it to have so much space. Our district manager list is here and we'll learn more about formatting this list in a moment. But, if I click on any one of the district managers, the other two visualizations will change in response. I'll get a more specific map and I'll simply get the total units sold in that manager's district.
Format a visualization ::
Selecting transcript lines in this section will navigate to timestamp in the video
- [Instructor] The top section of the Visualizations pane allows us to choose or change visualizations to change, for example, a column chart to a bar chart or change a card to a map. And then below that, we have different possibilities based on the visualization that's selected. For example, if I choose a visualization that has text in it, it shows me values and whether or not it has Drillthrough. Next, formatting and then finally, some analytics that might or might now be available based on the visual. If I have a map selected, on the other hand, I've many other choices available like map controls and map styles. So let's go to our first visualization which is a table visualization. And check out the formatting options that are available. First, we have our general formatting. This is where it sits on the page and how big it is. And then alternate text that we could provide, for example, we could say the alternate text might be that this is a list of district managers. Next, we have style. And we have some choices. The default style, bold, header. If we chose, for example, bold header, notice that looks pretty slick. But if I've chosen something different, like alternating rows, and then I revert to the default, it will go back to the default. We could turn a vertical or horizontal grid on or off if we wish, we could have some row padding, outline color and here is what I begin to care about then, our text size for this visualization because I would like these names to be larger. You can use the spin boxes or you can type a value but that now is large enough for me to be able to read. Column headers, in this case, district manager. What font family we want to use, what text size we're using again for our column headers. How value should be formatted, how the field should be formatted. Do we want to have a title on this? It looks like it has one, it really doesn't. What it has is it has a column header. So if I wanted to place a title on here, I could. And then I could enter title test. And I'll lowercase that so you can tell the difference the title appears here in the top. If I turn it off, that's fine too. I can have word wrap on and font color. I can modify the background. I can lock the aspect ratio, modify the border and provide a tool tip. Lots of choices that are available for me. If we look at something like the map, I have other choices. Map controls. Do I want to have zoom buttons included? Right now I don't have them. But if I turn them on, then we'll get a plus and minus which makes it really easy for people to be able to zoom in the map. If this is a heat map, then that's a different style of map. Notice, not specific points, just hotspots. Again, title, background, locking the aspect ratio, borders and tool tips. The same types of choices that appear in almost all visuals. And if I want to change, for example, my card visual, I have the choice to turn data labels on or off, they're on by default. I can add a title to this. I can do something different with the background. Right now it has a white background color but I might like this to stand out a little bit and I have colors that are available to me for my theme so I could put a little light yellow wash behind that, for example, modify the background. And if I wanted to change this, to change the number of decimal points, I can go to my data label and set this specifically to one decimal point if that's the standard that we've used in the past. If I want to modify the category label to make total units this year larger, I can do that as well. And if I'd rather it be a darker color than that gray, I can choose a darker color. These are all of the types of choices that I have while formatting my visualizations.
Create chart visualizations ::
Selecting transcript lines in this section will navigate to timestamp in the video
- [Instructor] Let's add another page to our report. Simply click the New Page button, and we're going to create a couple of different charts. The kind of charts that we'd see in Microsoft Excel. So, let's start by choosing in the Item table, we're going to select Category. That's a list of words, so real quickly we get a list. What I'd like to know is, in each category, what were the total sales this year and last year? So, that information is in a related table, it's not an item, it's actually in the Sales table. So, I'm going to choose Total Units Last Year and Total Units This Year, and this started out as a table and nothing that I've done is changing that from a table. So, I have lots of tabular data. I'm not going to bother formatting this so it looks any larger, because what I want to do is turn this into a chart. I'm going to turn this into a stacked column chart simply by clicking on Stacked Column chart with this visual selected, and real quickly I get a stacked column chart. Going to make this a little bit larger. And we have the ability to format this, we have all the tools we need right here, but this is a good time to talk about view. Right now, the view that I'm seeing is a view that fits this to the page and that page is pretty small. The actual size of this is fairly large, so I don't necessarily need to change this if I don't wish to. I can also fit this to the width as opposed to just the page, but that would be the same, because the width is my constraint here. Some other choices that I have are to show gridlines, if I'd like to show gridlines. If I show them, to snap to the grid. To show a selection pane, a bookmarks pane, and so on. If I switch to Reading view, then I'm prompted to save the report so let's not go there yet. But again, if you'd like to work with a larger view of any of this, you can go to actual size real quickly and that will give you your chart. Now, if I wanted to remove one of these, for example, if, with my visualization selected, I wanted to remove Total Units Last Year and just show this year, I can do that. I could also choose to show this as a pie. Or as a treemap, which is like a pie, but it's all squares instead of slices. So, this is what my treemap looks like. I'm going to switch my view to something that gives me a little more space. I'm going to fit this to the width. I'm going to take this treemap and I'm going to cold Control and hit C, hold Control and hit V, that was a real quick copy/paste, now I have two of these. This one I'm going to slide next door, line up in exactly the same place, and this is the Total Units This Year by category, right? But now, I want to change from Category. On the right hand side under Item, I'm going to select Buyer instead, and this is the Total Units This Year by buyer. That's a slick representation of that where each buyer has a different size of, a different piece of the rectangle. I'm going to return to Total Units This Year by category, convert it back to a pie chart. Once I have all the visualizations in the report I wish to have as I wish to have them, then I should start formatting. Based, for example, on what the actual size is. Because remember, that I have a lot of space being take up by tools over here on the right. If I collapse my visualizations and collapse my fields and now choose fit to page, notice a lot bigger. But as soon as I start pulling out my report tools, then my report canvas gets smaller to accommodate. If I make a change and I don't like it, for example, if I decide that I want to change this particular visualization to a donut and I think, oh not so much. Control + Z will undo, Control + Y to redo a change that we've made. Before we leave charts, this is a good time for me to make a pitch about the difference between learning Power BI and using Power BI. This data that we're using is not your data or my data, it's anonymous data in a sample. But when you begin working in Power BI with your own data, something close to miraculous happens. The entire work of Power BI will get much easier. It certainly gets more intriguing, because you have something at stake, but also you have expertise. If everything that we're doing feels a little remote right now, don't let that bother you. Like working with Excel pivot tables, working with Power BI becomes easier the closer you are to the data that your working with, and the more relevant that data is to your work.
Use text, map, and gauge visualizations ::
Selecting transcript lines in this section will navigate to timestamp in the video
- [Instructor] I have added a new page to our report, we have space for some additional visualizations and what I'd like to do is create first a list of territories. So I'm going to click Territory. It's going to create a map because that's what it does but I don't want it to create a map. I actually would like to have a list. So I'm going to choose Table and that should give us a list of territories. Perfect. Doesn't need to be anywhere near the slide. We'll do some other formatting in a bit. Next, I'd like to create a map. The map that I want to create is where the cities that each of our stories are in so I'm going to click City and hopefully, we get a map here. Not yet. I'm going to switch map types to ArcGIS maps for Power BI and see if that will give us the map that we want. There we go. There are stores. And then I'd like to create a gage so I'm going to click and choose a gage, a visualization now. So these are perfect for key performance indicators. I'm going to choose Sales and we have the key performance indicator value, goal, status and it has a spot here for value so I'm going to click and choose value. Pretty slick. This year sales. And then I'm going to click the maximum value and choose goal and then we'll drop it at minimum but that's okay, I'm just going to move it, drag and drop. And notice that our goal is 23 Million and where we are now is 22 Million. If you want to change the colors so that 23 Million stands out a little bit more so we can tell, there's actually a slice you can change your data colors. We can also change the gage access so that we have a specific minimum and a maximum and now we have all of our elements. So if I choose a particular territory like Pennsylvania, there are our Pennsylvania stores. This is looking good. At any point in time you can save your report and I think this is a great time to do so so I'm going to choose File, Save and I'm saving not just page three but all three pages of my report and I'm going to name this "Retail Analysis" and my initials and save it and I suggest you save yours as well.
Use a slicer to filter visualizations ::
Selecting transcript lines in this section will navigate to timestamp in the video
- [Instructor] While you and I know that each of these visualizations can be used to filter the other visualizations on the page, it's nice to have a very specific way for users to filter, and that is to include a slicer. I'm going to take this list that I have, that is currently a table, and I'm going to simply convert it to a slicer visualization. It has a little funnel on it and now we have a slicer. Should be exactly the same. It mostly is. I'm going to change our font size, just for our work while we're creating this. Make this a little bit longer. This is actually a multi-select slicer. So if I wanted to look at North Carolina, South Carolina and Georgia, I can simply choose Georgia, North Carolina, South Carolina, but hold Ctrl while I'm doing it. It knows exactly how to behave and my users can usually figure that out pretty quickly too. So here are our stores in those three territories, and the sales goal and the sales for those three territories. Slick. If I want to select just one state, I simply go choose one state. And if I want to clear this filter, I can do that easily too. Simply clicking on the item that's selected brings everyone back. Now, there's another style here and that's a dropdown. With the dropdown, it takes up less space and I can then go in and just choose the items that I want. For example, just North Carolina. In most cases, I prefer the list style because it makes it clearer what has been selected. The rest of the list is still visible. It also makes it clearer how to use this. The list doesn't go away, the order remains the same. I can put the slicer any place I wish on the page and it allows my users to select or multi-select information and to filter. They can also choose, if they don't wish to use the slicer, to simply use the visualizations themselves. The fact that I converted the table to a slicer simply makes it easier to do the filtering that they could already do, using the visualizations.
Sort, copy, and paste visualizations ::
Selecting transcript lines in this section will navigate to timestamp in the video
- [Instructor] Returning to page one of our report, I've adjusted our map and card visualizations to have a little bit more room so that I can add another field to District Manager, which is what were the total units sold this year. That's a field from our sales table. And now I need to give it a little space. Now, this is currently sorted by the first name of the District Manager. But if I wanted to I could sort this instead, by the total number of units this year. The first time I do it, it sorts top to bottom, highest values on top, I click again, we'll have the highest values on the bottom. So when you have more than one column, you have the ability to determine which column is used to sort. I've already shown you how to copy and paste a visualization on the same page, but you can add a new page, and take a visualization and copy it. For example, this list, select it, hold Control, hit C, go to the new page. Click in the Report Whole control, and hit V, and we now have that same visualization in not one, but in two pages. Once you've copied the visualization, you can decide what you'd like to do with it. Would you like to keep it exactly the same, would you like to modify it. The choice is up to you.
Download and use a custom visual from the gallery ::
Selecting transcript lines in this section will navigate to timestamp in the video
- [Narrator] There are other visuals that we have not yet discussed. Spend some time looking, for example, at the decomposition tree and key influencers. You can add Q & A directly to any report page. And you can put a power app directly into Power BI. But I want to show how to import a visual that you don't already have. You have some choices, if you were sent a file for a visual, this is how you would import it. But usually we'll look on AppSource for visuals that we don't already have. This will take you to the Power BI Visuals marketplace. Also, if your organization has some specific visuals that they've already selected, they would appear here. But for each of the visuals, you'll have a sample. So here's a bullet chart, a bar chart with extra-visual elements, histograms, KPI column charts, variance charts and so on. What I'm looking for is a specific type of a gauge, and the gauge that I'd like to have is something that looks more like a thermometer in this case, there's a Cylindrical Gauge by MAQ Software. I'm going to select this. Looks like it's free. Some folks have had some issues with it, so we'll give it a go anyway and see if this going to do what we would like it to do, the visual will be added to our toolbox right here and we can then select it. There's the visual and we're asked to provide data fields: actual value, target value and so on. This is going to be like the gauge we worked with earlier. So the actual value is value and the target value is goal which is only slightly higher. Now if I want to set that as the max, I could as well, but I like that right there. That looks good. Now we can format this. Make whatever changes we'd like to. But, I now have this new cylindrical gauge available to me for any of my future reports and dashboards in Power BI. If I want to delete it, I can click and under delete a visual, I can select any of the custom visuals I've added and delete them here as well. But for now, I'm going to hang onto it. It does what I'd like it to do. If I'd like it to have a little more contrast in its height, I can make it taller, notice I get more of a gap. And I can make it skinnier, I have lots of choices about what I'd like to do with this specific visual.
4. Modify and print a report ::
- Manage Report Pages
- Add a filter to a page or report
- Set Visualization interactions
- Print a report page
- Export to powerpoint or PDF
- [Instructor] When I add new pages to my report, they're automatically named Page 1, Page 2, Page 3, the next available page number. To change the name of a page, I can either double-click it and enter a new name. Press enter, or I can right-click on a page, choose Rename Page to select it for editing. Same thing. You can use about any character you wish. I could give these long names. And I have those previous and next tab buttons over here on the left-hand side that nobody particularly likes, but remember that when your users are working with this in reading view, then they will actually have room for a large number of tabs because they appear on the left side in a pane, like tabs, for example in OneNote. So this is really for the convenience of yourself and anyone else who is editing this particular report that you would keep the name short. If I want to rearrange tabs, it's a drag-and-drop operation, much as it is with Microsoft Excel tabs. If I have a page that I decide I no longer need, I can simply delete it. When I delete it, I'm prompted that at the next save, this page will be permanently deleted. So right now, it's sort of in a recycled pages bin but I can still recover it by simply undoing, CTRL + Z, for example. There it is back again. CTRL + Y removes it again. But at the point where I save this report, I can no longer retrieve that page any longer.
2. Add a filter to a page or report ::
Selecting transcript lines in this section will navigate to timestamp in the video
- [Instructor] We've been using a slicer to filter our fourth page of our report territory. And we can do that, it works just fine if I make a selection here. I'm effectively filtering the other visuals on this page. You'll notice that they're being recalculated and a new display is provided. But as a report creator, as the owner of a report I have the ability to add filters to visuals but also to add filters to pages and to the entire report by applying a filter on all pages. Let me show you quickly how we do this and some other related concepts that might be useful to you. What I'd like to do is filter this entire page, Category & Buyer on Territory and I don't necessarily want to add another slicer. I just want to add for this particular page a version that is based on a specific geography. So the first thing I'm going to do is I'm going to duplicate this particular page so I have a duplicate. And then I'm going to change its name so instead of Category & Buyer the name of this is Southeast Category & Buyer. Now I'm going to add a specific filter. We'll return to store, that's where we find our Territory field. And I'm going to grab Territory and drag it and drop it on filters on this page. There are two types of filtering. Let's look at basic filtering which says choose the values you want to display. So the Southeast is Georgia, North Carolina and South Caroline and notice that already Power BI is going to work to further filter this. So now the territory on this page is Georgia, North Carolina or South Carolina. And the territory here is all territories. There's no page or report filter here. But if I return to Southeast Category & Buyer this is filtered three specific states in this southeast region. It would be a good thing for me to add something that explains that to people but if they know and if for example I create four of these. One for southeast, one for northeast, one for each of the regions, then that works just fine and people get used to that very quickly. Could my users do this on their own? Sure but then they have to know which states are in which regions. So they either need then to be comfortable creating filters or we'll throw a slicer on here for them. But if each time we need to look at Southeast Category & Buyer separately I may as well create a separate filtered report page. In addition to basic filtering I have advanced filtering. Advanced filtering is for example, show items where the value is or is not contained, does not contain, starts with, is blank, is not blank. So you have you really full featured filter set here. And if I apply a filter creating with a basic filter and then I look at advanced filtering it will show me what that filter would look like had I created it in the advanced filter. I can do something similar and create an entire report that has a particular filter. Before I forget I'm going to delete this page cause I don't need it any longer. What if I wanted to filter the entire report in exactly that same fashion. Perhaps I need a report just for southeast. The way to do that is to save a copy of the report. And then apply a filter for all pages. So I'm going to call this Retail Analysis - Southeast. Give it a moment. Here's our new report. It's been saved. And let's choose Territory and rather than dragging it to filters on this page, drag it to filters on all pages. And set the territory to Georgia, North Carolina and South Carolina. With this one simple filter I've now filtered the entire report. At this point I would likely remove the slicer from Territory. But I might want to keep it just so that my users could here further filter and say I just want to look at South Carolina on this page for example. In report view I'm going to compress the filters pane here. This looks good. Quick save. Switch to reading view. And this then is my filtered report. Three district managers. Smaller goals. Category & Buyer. And a trimmed down territory. And while my users can make whatever choices they want about modifying filters, when they reset it to the default it will return to the report level filter or page level filters that I applied when I created this report.
Set visualization interactions ::
Selecting transcript lines in this section will navigate to timestamp in the video
- I'm going to return to the unfiltered retail analysis report that we've been working with in this course. We already know that if a user selects something in one visualization, whether it's a slicer or something else, that it automatically serves as a filter for the other visualizations. That's a default behavior, but I might wish to change it. For example, perhaps when I have this list of district managers, I don't want users to filter the goals based on district manager. I simply want the list to be here so that people can see the total units and the manager's name. And the way I do that, first, let's kick this into edit mode. And what I'm going to do is I'm going to select a list of district managers that particular visualization, and I'm going to choose visual interactions and I'm going to turn on editing the interactions. So there are several possibilities here because we have a chart. The first possibility is that the default behavior that the selected visualization filters this visualization. The second possibilities will highlight in the third is it will do nothing. So I'm going to choose none and now with none chosen, when I make selections here, it does not affect my custom visualization and if I'm done with this I can lock this down, done editing the interactions. To change this back, return and say no. There's the default filter behavior. So now when I select an item, one of the district managers, it's filtering, changing the values. If we go look at our overview where we also have district managers, no debt, we have the default behavior and if I want to change that default behavior, visual interactions, edit the interactions, say no, I don't want this to change and I don't want this to change. You should have a good reason for doing this because when you change default behaviors for a report, for example, when you have a user drill into this report from a dashboard, it can be confusing if it doesn't behave as they expect it to, but there are times that you want to make sure that you don't have filter in. For example, if you always want to show all the stores, regardless of which manager you're choosing, then we might want to put a title up here, all locations, for example, but each time you are finished working with this, it's a good habit to turn off editing interactions until the next time you need to do so.
Print a report page ::
Selecting transcript lines in this section will navigate to timestamp in the video
- One reason to create a report is that you're creating something that is printable, or exportable, by your users. The basic printing unit in Power BI, for a report, is a page. So if I choose export here, to print, it takes a moment, but note that it is going to print one page, the page I'm looking at right now. And now I get to make some choices about things like headers or footers, if I change my page orientation to landscape it will look better. That's better, but I promise you there is actually a slightly easier way to print than this, and that is to export it as a PDF, which I'll show you in the next movie.
Export to PowerPoint or PDF ::
Selecting transcript lines in this section will navigate to timestamp in the video
- [Narrator] If I wanted to print my entire report, all four pages, then I should take the time to export it first to a PDF. I'm going to export it with current values and if I had any report tabs that were hidden, which is something that we can do, I could exclude those so that I was only printing the report pages that were visible. I'm going to take a second to create this PDF. I've done this before. It doesn't take that long. It says a few minutes. This is a relatively small report, only four pages. It will take less than 30 seconds typically. My file's ready for download. I'm going to open it. This is pretty big. I might want to make it a little smaller. But, I have some options here. It's in a scrollable mode now. I see my pages. But if I change my page view and turn off continuous scrolling, it makes it easier for me to see my discrete pages. Here's page one, page two, page three, page four. Now, here's something that's worth noting. That most of the custom visuals that you will use do not survive exporting. This is our lovely arc GIS map and it just isn't going to work. If printing's a priority, that might be a reason not to use this particular visualization, because the regular map typically works, but the arc GIS map has some other features that we really like about it. But here is my printable PDF. I have one more option. If you like using PowerPoint this is pretty cool. I can also export to PowerPoint. This one typically takes a little bit longer than generating the PDF. But again, our basic unit here is the page, so in PowerPoint each of these pages will become a slide of its own. If my goal was to create something that I could display a set of visuals in PowerPoint then, I'd want to group my pages so that each page was appropriate for a specific slide. If you think well that's not how I would arrange it for print but I do want to do that, remember that you have the ability to save as and to save a report so that you could create a version easily exportable to PowerPoint and another version that would largely be used for print by your users. My file's ready. Let's pop it open. Windows is going to load PowerPoint. PowerPoint's going to load our new presentation. It's choosing a particular template. If I wanted to view this in Power BI I can. That's a slick option. Again, each slide is a page. This visual isn't going to work here either. In PowerPoint of course, how I could get that visual here would be to use the snipping tool or another screen scraper to pull this in, because this isn't something that's going to refresh all the time but it does tell me when it was last refreshed and when it was downloaded. During run time from the slide presentation if a user clicks here they will be taken to Power BI so that they can select and view this particular report.
5 . Create a Dashboard ::
- Create and Manage Dashboards
- Pin a report tile to a dashboard
- Pin a live report page to dashboard
- Pin a tile from another dashboard
- Add a tile to the dashboard
- Add a comment or alert to a tile
- Subscribe to a dashboard or a report
- Build a dashboard with quick insights
- Create a mobile view
- set a featured(deafult) dashboard
1. Create and Manage Dashboards ::
- Well we love our retail analysis report it's not our end point, we want to create a dashboard so that we can share some of our visualizations with other members of our team. We're simply going to use these for analysis and decision making. When we create dashboards we have a number of ways to create them. We can go to dashboards and click the create button, we can create dashboards from our content but the most common way that I create dashboards is starting from my reports. Unlike a report a dashboard is a single page a dashboard, for example this retail analysis sample dashboard doesn't have tabs for different views of the data. Therefore it's important that the data that is selected tells the most compelling story. Another significant difference between dashboards and reports is that our dashboards support natural language queries. Where it says ask a question about your data which our report does not. If I'd like to open a dashboard that already exists I simply click on the dashboard to open it as I did a few moments ago. If I want to share a dashboard I have an icon that allows me to do that. If I want to remove a dashboard or throw one away, I can do so right here. If I want to print a dashboard, if I want to print or export a dashboard I can, right here. I can choose one dashboard that is my home dashboard and it will be set as featured that then becomes my default dashboard. And I can favorite different dashboards that I wish to be able to view when I choose favorites. I can also favorite reports by the way, another feature that reports and dashboards have in common. This dashboard is composed of specific tiles, if I want to print one tile, for example this tile, I can switch to focus mode right click in the tile and choose print. Therefore a dashboard gives our users the ability to print tile by tile or to print the entire dashboard. Combined with the ability to set one dashboard as featured and to favorite dashboards this is an easy way for a manager, for example, to be able to view a dashboard and print all or part of it that they would like to share with their direct reports and organization. In the next movie we will create a dashboard from a report.
Pin a report tile to a dashboard ::
Selecting transcript lines in this section will navigate to timestamp in the video
- [Instructor] I love this particular visualization right here, which is Total Units This Year by Buyer, a treemap. I want to put this in the dashboard that I'm going to create. I'm simply going to point to it, and I am in the Report Viewer, but it doesn't matter if I'm in the Viewer or the Editor because I'll have exactly the same choice, this pin button that pins the visual to a dashboard. It says, where would you like to pin to? Now, when we select it, the Retail Analysis Sample, it came with the report and a dashboard, as well as a dataset. I'm going to create a new dashboard, and I'm just going to put my initials and Dashboard. Obviously, in a work environment, I would be choosing the dashboard that would be helpful and useful for my users that would briefly and accurately name this dashboard, but I'm simply going to create a new dashboard and pin this visual to it. Says, the visualization has been pinned to your dashboard. You can now create a phone view to optimize your dashboard for mobile phones as well. That's kind of slick. I can also go view my dashboard. Right here, there's my dashboard with my single visualization, Total Units This Year by Buyer. Let's grab another visualization. Let's go back to our report. And on the Overview page, I like the District Manager and Total Units This Year visualization, and it asks me where should it go, and I'm going to say on an existing dashboard, and it's already selected my dashboard, choose Pin, go to my dashboard, there we go. If I wish, I can rearrange these items, just as I would if I were working in my report. One of the benefits of a dashboard is that I can include visualizations from more than one report, and therefore, from more than one dataset. I'm going to return to the reports, and this time, I'm going to choose the report that came packaged with the sample, Retail Analysis Sample. And on the second page, which is called District Monthly Sales, there's this year's sales by store number, that's pretty slick. District Sales Report, notice the form factor on this. We'll talk more about that later, but it didn't look like a regular page, it looked like a phone page for a reason. But under District Monthly Sales, I'm going to choose this year's sales by store number, and I'm going to pin it to an existing dashboard. Remember that, even though we're choosing visualizations from the same dataset but different reports, it could be different datasets as well. Now, note that if I click on one of the visualizations, it takes me back to the appropriate report that this particular visualization came from. Another way to think about this is that the reports that we create are really providing detailed information. Dashboards are summaries that allow us to drill down into the details. This is our dashboard thus far.
Pin a live report page to a dashboard ::
Selecting transcript lines in this section will navigate to timestamp in the video
- [Instructor] When I took a visualization from a report and pinned it here as a tile, what I get is somewhat of a snapshot. It's not exactly a snapshot, because if the data changes, if there have been updates to the number of units that have been sold this year, then I can update my data here. Either I can open the dashboard again, and each time I do it will be refreshed, or I can choose to refresh it, just like that. And this specific tile has now been refreshed just now. I can refresh others as well. But these tiles are not synchronized with each other, they have no visual interaction. If I click here in Annelie Zubar, it's not going to filter the other visualizations on the screen. What it's going to do is take me into the report page that the district managers list comes from. Also, if I return to my report and I made some changes. Let's say I applied a report level filter or a page level filter, that would do nothing at all to these visualizations. Filters and layout changes are not automatically updated. What if I had visualizations on a page that I wanted to be able to use them together as I can in a report? I can do that, but I need to create the tile in a different way. Let's return to my report, you return to yours that we created earlier. Actually, I'm going to choose goals. You'll remember here that when I select Tina for example, that we get a redraft over here on the sales and the goal. That looks really good. I would like to have this page functionality in my dashboard. What I'm going to do then, is I'm going to choose pin a live page. Notice it's on the more menu. And it's here and it's also in edit view, both. So if I'm in edit report, it's over here on the right hand side, pin a live page. I'm going to click pin a live page, and it asks me what dashboard would I like to put this in? I'm going to put this in a new dashboard. I'm going to name this dashboard with my initial and goals. Note it says "Pin live page enables changes "to reports to appear "when the page is refreshed." And by the page, they mean the page in the report. So I'm going to pin this, it's going to create a new dashboard. When I open that dashboard, this is what the dashboard looks like. And, note, that it behaves here in the dashboard as it did in the report. If I modify this page of my report, for example to add a filter, based on territory, something we did earlier, for Georgia, North Carolina, and South Carolina. And then I save the report. When we return to the goals dashboard, note that what we have is filtered. It's the same filter that we applied in the report has been applied here. I'm going to close this dashboard, return to my report, remove the filter from the visualization, and I'll meet you in the next movie.
Pin a tile from another dashboard ::
Selecting transcript lines in this section will navigate to timestamp in the video
- [Instructor] Remember that whenever we look at a report, all of the visuals have something in common. They're based on the same dataset. This is not true for dashboards. When I create a dashboard, I'm allowed to pin tiles from multiple reports, and therefore, multiple datasets. I'm also allowed to pin tiles that come from other dashboards. I'm working a lot with dashboards and reports right now, and rather than having to go to my workspace, I could put the dashboards I'm working with right now in favorites, and I'm going to do that. I'm also going to throw a couple of reports into favorites that I'm working with. There's no need to add datasets to favorites, so simply our reports and our dashboards. Now when I go to favorites, there are my items, dashboard with this teal icon and ribbon on the left-hand side and a dark green for my report. So let's now go to the retail analysis sample dashboard. This is the dashboard that came with our dataset. I'd like to send a particular tile from this dashboard to our other dashboard, specifically, stores opened this year by chain. I'm simply going to click the more button at the top, choose pin tile, and I'm going to pin this to the goals dashboard using the destination theme, or I could keep the current theme. Actually, both themes are the same, so it doesn't really matter, but using the destination theme is the default. I'm going to click pin, click go to dashboard, wait for it to load, and it will have the two visualizations from the same page and the visualization that I just added. If I ever wish to remove this tile or any other tile, that's another choice that we find on the menu. If I delete the tile from here, this tile still exists in the other dashboard and, in fact, also exists in the report that came with this sample.
Add a tile to a dashboard ::
Selecting transcript lines in this section will navigate to timestamp in the video
- [Lecturer] There are other ways we can add tiles directly to a dashboard. In this movie I'd like to show you how to use the add tile function that you'll find when you are looking at a dashboard and you have the ability to edit it or you own it. Simply choose from the moment you add tile, and you'll get a list of types of tiles that you can add. Currently we have five, web content, image, text box, video, and custom streaming data. Web content is content for which you have URL but it is not an image or a video. Next we have images, and images are images that are online, not an image sitting on your network or on your local PC. An image for which you have a URL. The same thing is true with video. And at this point if we choose video, Power BI will allow you to insert video from YouTube and from Vimeo. Those are the only two choices. If I want to add a text box, I simply click text box and next and I'm allowed to display a title and subtitle if I wish, and allowed to display content. The reason that I would use a text box might be simply to create a snazzy title but it could also be that I'd like to provide some instructions to users, or other information, for example documentation. Now I can fill in the details. I can choose a font, a font size, and enter some text here, and if I wish by the way I can choose a font color based on my theme. So I'm going to choose this dark coral, and apply it. And here is my new text box that I have added. My final choice under add tile is real time data. My final choice is real time data, custom streaming data. This is beyond the scope of this course. But this allows you to have data automatically update in a dashboard in real time. An example of this might be to connect sensors when manufacturing floor to a dashboard, or to connect any kind of a smart device that has the ability to report its own state in real time to Power BI. And therefore we create a dashboard that's live 24/7/365. If we want to remove tiles after we add them, we already know how to do that. Simply click, and delete the tile. Unlike a tile, the add comes from a report or another dashboard. If I delete this tile it will be gone because this is the only place this tile exists. If I want to change it rather than deleting it, I choose simply choose to edit it and I have the ability to modify my tile details.
Add a comment or alert to a tile ::
Selecting transcript lines in this section will navigate to timestamp in the video
- [Instructor] One of the really interesting features of Dashboards is the ability to set criteria on specific tiles so that we can receive alerts when a value rises or falls above or below that criteria. Here's an example. This year's sales, I'd like to know when sales are going to be above $21 million. Now obviously, they're there already and I'm choosing them over $21 million because that way, you'll be able to see how this works. But I could choose any other value I wished, I'm going to go to manage alerts, I have no alerts currently set. I'm going to add an alert rule and the alert picks up alert for and the name of the tile, this year's sales, set alerts rule for this year's sales when the condition of this year's sales is above $21 million, and you have two intensities of notification you can request. You can request a notification at most, every 24 hours, or at most, once an hour, this will continue to send you something if the value changes and triggers this condition. I can also be sent an email if I'd like to do that. I'll have it send me an email as well. Note also, there's a nice clue here that I could do even more with this. I can use an alert sent to the notifications center, to trigger automation with Microsoft Flow, also known as Power Automate. So if I would like to notify a number of people that a value has gone up in a particular way, with Microsoft Flow or Microsoft Power Automate, I can automate so many different applications. I could post something on a page in Teams, for example, if I wished, with a link. There's a number of different things that I could do. So here's my alert rule, it is currently active. If you write a rule and you don't want to use it for a period of time, you just turn it off, you don't need to delete it, although you could, but I'm going to save and close this. And it's not going to trigger immediately, even if I refresh my data, it's not going to trigger immediately, but it is going to trigger at some point, and when it does, I will have a notification. I'll have a bell icon there that shows me I have a notification, when I got my notification center, I'll have a notification there, I'll have it on my mobile device and because I asked to be sent an email, it will be there as well. Sometime in the next 15 minutes or so, I will get a notification based on this rule that I have set. If I want to remove a rule or edit a rule, I simply return to manage alerts, I choose the alert that I want to work with, make whatever modifications I wish to make. Right here. While I'm waiting, a reminder that another feature of dashboards is the ability to have a conversation, to add a comment to a particular tile, so that your colleagues can have a conversation about it as well. If I want to call someone out by name, I can mention them. For example, Griffin Snow. Your thoughts on this performance? And I can post this. And close. I can tell there's a conversation here because there's an icon. And if I add a comment here as well, I'm going to address a comment to myself. And post. And close. I've received an alert. I can click here and view my unread notifications. I could also open the notifications from here by the way and it says I have an alert for this year's sales, 11 minutes ago. This year's sales is $22 million, $51 thousand in change, which is above the threshold of $21 million and from here I can click and go directly to the tile in focus mode. If I go back, I will end up back in the dashboard. How did this look in Outlook? In outlook I received an alert for this year's sales. There's the dashboard, the measure, the current value in my threshold, and I can click to go to dashboard, and again, I will be taken back to my dashboard after a little step through Azure on the way so that I can view this specific tile. From the notifications center, I was taken to tile. From the email, I was taken to the dashboard. Alerts provide a powerful way to have Power BI keep you up to date on what's going on in critical values in your dashboards.
Subscribe to a dashboard or report ::
Selecting transcript lines in this section will navigate to timestamp in the video
- [Gini] If you would like to receive a snapshot to your inbox on a periodic basis, daily, weekly, hourly, monthly, or the day after, or once a day after there's been a data refresh, you can subscribe yourself to any dashboard and in fact to any report. You need edit permissions, though, for a dashboard or a report if you want to subscribe anyone else. So this is for the retail analysis sample. I'm going to subscribe myself. I'm going to subscribe Griffin Snow. And I'd like to make sure Griffin also has access to this dashboard. I can include an optional message. I can include a subject. And the frequency here is going to be weekly. I need to choose a weekday then. Let's say Tuesdays at 9:30 a.m. Eastern Time starting on Tuesday the seventh. And you can have this go on forever if you wish. I'm asking to include a link to a dashboard in Power BI in this subscription so that when someone receives the email, they can also quickly go to this subscription. Save and close. And now tomorrow morning, I will receive the first email in the subscription, and I can add others as well. Now, it tells me that Griffin Snow does not pay for Power BI. They're a free user. And this is not a premium workspace. So we learn two requirements. You have to have a Power BI Pro license, or you have to be in a premium workspace. I'm going to remove Griffin. Save and close again. Too bad, Griffin. I'll receive an email based on the frequency that I selected. If I want to modify my subscription, I simply return to Subscribe, and I can return to this subscription, modify it if I wish, including the ability to run this right now. And so I'm getting this subscription and so would any other subscribed users that I had. So there's a way to just turn this off and to push these on a periodic basis. However, most Power BI users prefer to have the subscription running on an automated basis. When I go to Microsoft Outlook, here's my subscription. Check it out, I get a picture of the dashboard as it currently looks. Here's the picture. And if I wish, I can either go to the dashboard directly from the email, or I can manage the subscription.
Build a dashboard with Quick Insights ::
Selecting transcript lines in this section will navigate to timestamp in the video
- [Instructor] There are other ways to add a tile to a dashboard. We can pin tiles from an Excel Workbook that's stored in OneDrive for Business. There's an add in for Excel called the Power BI Publisher for Excel, which allows you to capture, for example, a pivot table and pin it as a tile. There's something called a paginated report from Power BI report server or SSRS SQL Server Reporting Services that can be pinned as a tile. But I want to show you one more way at this point to pin a tile from Quick Insights. And then a little later, I'll show you yet one more method. Dashboard tiles or goodness in Power BI. So we have a number of different ways to take data and present it to our users. With Quick Insights, we choose a data set. For example, I'm going to return to Catalog Requests. You may not remember much about Catalog Request. It was a long time ago, but it's a large Excel table that includes information about people who have requested Catalogs and their demographic information. It has 200 records or so in it. I'm going to click the More Actions button and choose Get Quick Insights. This is an invitation for Power BI to churn through my data, and tell me what it learned about it. And some of the things that learned will be mundane, and some of them will be pretty cool, but all of them will be presented as tiles that we can pin on dashboards. These Insights came quickly. And that's not so much because there's only 200 Records. It's because they also are a limited number of columns. If I had 200 columns in my data set, it would take longer because it's not going to turn all my data actually it's going to turn a sample of my data to be able to determine if there are Insights and present them to me. Remember, now you're the person in charge. So some of these will be meaningful, some meaningless and you are the meaning maker in this moment. First, there are more people with last names who ordered Catalogs from Washington. Makes no sense. So we don't want that one. There were a lot of Catalog requests from Vermont in February. Maybe that makes sense or maybe it doesn't. It might be that we ran some special kind of a promotion and we were actually looking to learn this. And if that's the case, we can pin this to a dashboard. We're going to create a new dashboard and this is Catalog Requests. And I'm going to pin it there. What else, there's a correlation between first name and State. Everybody who has a first name also lives in a state, not so much. Count of first names. Now, January accounts for the majority of Catalog Requests from the State of Washington again. If we drove this based on business decisions we made we want to know that. December has noticeably more last names for the State New Mexico. So another way to think about that is, that in December, we had a lot of folks in New Mexico Order Catalogs, because we have first and last names from everyone in here. And if we had another that we chose to pin, we can pin it as well. Now, again, if you have more columns of data, there are more Insights to be gained. But this is a method for creating the dashboard directly from your data set.
Create a mobile view ::
Selecting transcript lines in this section will navigate to timestamp in the video
- [Instructor] Each time I pin a tile to a dashboard, I'm being prompted to create a mobile view, or a mobile version of a view. And I can say yes at that point, but I can also return and do this anytime. Here I am in my dashboard that I created. You can open any dashboard you wish, and click the More button and choose Mobile View. And these are the tiles, and you can tell this just doesn't look great, so I'm going to unpin that District Managers, because it won't be helpful to somebody who's using a mobile view. I can size these other tiles to give them a little bit more space. That looks good. This one as well. No, not that little. Want to make it bigger. Not quite perfection yet, but it looks good. And then I'm going to simply switch from Phone View back to Web View. Power BI will automatically save that second view for me. So if I want to see how this will look on a mobile device, that's how the dashboard looks. This is how it looks in the web.
Set a featured (default) dashboard ::
Selecting transcript lines in this section will navigate to timestamp in the video
- [Instructor] One Power BI item you might consider creating is a dashboard that lets you see all of the information that you need to best do your job. Or to create such a dashboard for your team as a whole or for your manager. Whatever that dashboard is once you've created it, you have the ability not just to add it to Favorites, where this dashboard already appears, but to make it the default dashboard. And in doing so, each time you open Power BI, that's what you see first. Which makes it really, really easy to derive value out of this. If you want to make a particular dashboard your dashboard, you can open the dashboard and simply choose Set as featured. Before setting this as your featured dashboard, let me tell you the default behavior when there isn't a featured dashboard. When there is no featured dashboard, each time you come into Power BI, what you see is the state of the Power BI service the last time you left it. So if you were working in a particular dashboard, you would see that dashboard. If you were working in a particular report, you would see that report. But now, having set this as featured, and confirming that I want to set it as featured, each time I fire up Power BI, I will see this specific dashboard. If I want to return to my default behavior, I go back and I choose Disable featured. And in doing so, now I have my default behavior again, where each time I open Power BI I'll see what it looked like the last time I left Power BI. So, your choice. Either set a featured dashboard or allow Power BI to maintain the knowledge of your last dashboard that you used, and to load that last saved state whenever you launch Power BI.
6. Ask questions about your data ::
- Ask a question with Power BI Q ans A ::
- Tweak your dataset for Q and A
1. Ask a question with Power BI Q ans A ::
- [Instructor] Power BI dashboards have a built-in link to ask questions about our data. This is a doorway to Power BI QA. Simply click where it says Ask a question about your data and we're going to plug in to a very powerful tool that will allow us to create tiles and then add them to our dashboards if we wish. Notice that we're doing this from a dashboard, we don't have a ask a question about your day to link in a report. So here are some options that is giving us. What is the total store by city? What is the average selling area size by city? I'm going to show all the suggestions that it has. What is the average unit price last year by territory? And what I get as a response to that is I get a map with bubbles to show me the different sizes of average unit price. I'm going to ask as a column chart right here. That's more useful to me. With a small variance in sizes, it was really hard to view that in a map. Or I could say as a table. And there's the average unit price last year as a table. I like this, this looks good. I can pin this visual into my existing dashboard. It's on the web view, don't forget to pin it to your phone view and edit its placement and adds it because I have a phone view. So I'll need to remember to do that. I can go back to some of the other choices that I had as well or I could modify one of these top buyers by total units this year. Let's see what we get. That's not bad. As a pie chart. That looks good as well, I can pin that visual as well. Now note that this is the new QA experience and one of the things that's new about this QA experience is if you put in something that's close, Power BI is relying on Microsoft Graph to determine what it thinks you mean. So if I say top buyers by total units this year in North Carolina, it'll figure that out. But it will also figure out North Carolina if I actually type in North Carolina although North Carolina doesn't appear anywhere in this set of data, only the abbreviation, but it knows the abbreviation for North Carolina is NC. So this is an example of the application of artificial intelligence through Microsoft Graph that when I say NC, when I type North Carolina, it goes somewhere and says, oh, she means the abbreviation NC out of this database. I can then choose to pin my visual and drop it on our existing dashboard. If I exit Q&A, here are the new visuals that we created. In order to be most efficient with QA, it's helpful to know the names of some of the visuals that you might want to use and know why it's better to represent data using one type of visualization versus another. Remember, anytime that you like a visual that was created as a result of a Power BI Q&A session, you can simply pin it to the dashboard where you want it to appear in the future.
2. Tweak your dataset for Q and A
7 . Sharing reports with colleagues and others ::
- Overview : Sharing reports and dashboard
- Publish a report to the web
- Manage Published reports
- Embed a report in sharepoint online
- Share a dashboard or report
- Create a workspace for collaboration
- Add users to the workspace
- Use a workspace
- Publish an app
- Create a QR Code to share a tile
1. Overview : Sharing reports and dashboard ::
- [Voiceover] We have a wide variety of ways that we can share or collaborate in Power BI. So a quick overview. If I have some data that I would like a number of people to see. and by number of people I mean anyone in the world with internet access, then I can take that information, those visualizations in a report and I can publishlish them to the web. I can also take a report and rather than publishing it to the web I can imbed it in a SharePoint page. which is a grate way to be able to share information internally in my organization. I can also directly share both dashboards and reports with my colleagues. All three of these are really push methods I've created something, a report or a dashboard I'd like you to see it. But perhaps what I want to do is gather a group of people who can work collaboratively with me, who can also create data models, or work on dashboards, work on reports. Someone with a fine eye for layout for example and in that case, I'm going to create a workspace and invite my colleagues to this workspace perhaps as editors or perhaps as viewers. When we're done working on the items in that work space we can actually bundle them together and publishlish an application to share with others. A scenario might be that my sales team has people who are exited about working in Power BI and we want to show some leadership. So we create a work space and we create a couple of applications that will allows users to view our sales information reports. And now what we'd like to do is roll those out for the use of other sales units in our organization. So, we'll publish the app when it's all done. Then, something that's almost at the opposite end of the spectrum from publishing an application for everyone, which is simply to create a QR code that links to a tile in a dashboard. This is most useful to people who are using mobile devices. So I'd like someone to be able to scan a business card or scan a code on a screen at a presentation and view a particular tile on their mobile phone. And that's what I can do with a QR code. And even though there are all these different ways of sharing we still have to be concerned with how we're licensed. To be able to share, you need to have a Power BI Pro license. The free version of Power BI will only allow you to publish to the web, only do public sharing. So to share within your organization, or to share more specifically than simply publish to the web, you'll need a Power BI Pro license. And the people who consume the items that you share with them, with few exceptions, will either need their own Power BI Pro license or what you share will have to be shared under a Premium license or a Premium licensing environment. With that information about licensing as a given, a baseline in the next few movies we will take a look at these different ways of sharing in Power BI.
2. Publish a report to the web ::
Selecting transcript lines in this section will navigate to timestamp in the video
A typical way to share a report as a means of distribution is to do that by publishing. From the "More Options" menu, we'll choose "Embed," and then "Publish to web (public)." The description here is worth reading, because this is going to be shared on a publicly available website. This is not for internal sharing. Click "create embed code." You are about to create an embed code for this report. Notice this yellow background and catch your attention. Anyone on the internet will be able to access the report and the data it contains. And Microsoft occasionally chooses spectacular reports to display in a gallery, as best practices, or an illustration of how different organizations are using Power BI. So remember that you only want to publish reports if you're happy with everyone you know and everyone you don't know being able to review them. I'm going to click "Publish." It's creating an embed code for this particular report. We see two different links: the first is a link that you can send in email, and the second is HTML that you can paste into your blog or website. Notice that the HTML includes the width and the height of your frame and pixels, so if you wanted to have larger or smaller sizes, to be able to embed, both of these text boxes are made to be copied from. So if I choose this link, for example, I can right-click and copy it. And I'm going to swing over to Outlook and send myself an email. And include that link. I'll send it. And I will quickly get it back. Note that I have a new report from Power BI, here it is, I'm going to click the link. It will open a browser if I don't already have one open. Fire up Power BI. And display this report. All four pages. Now remember we have a visualization here, that does not necessarily export well. But it's still a great visualization. We're just not going to see it in circumstances like this. Note that when I publish the report, I'm publishing the entire report. The next time that I want to publish this report, I simply return to "embed," "publish to web," and the embed codes that have already been created are presented to me. If I want to put one in a blog, I would use this embed code to do that. Remember that the only security you have for this published report is what's called Security through Obscurity. Because this report is publicly discoverable by anyone who has access to Power BI, whether they have this link or not. However, if what you want to do is share an analysis with potential customers or with the public at large, publishing a report to the web is a great way to do exactly that.
Manage published reports ::
Selecting transcript lines in this section will navigate to timestamp in the video
- We previously published this report to the Web, and the result of that action is two unique embed codes, or descriptors, that can be used in an email or be used in a webpage or blog. But what if we no longer wanted these codes to be available? What if we no longer wanted to be publicly published? We control or manage our embed codes for both email and webpages in our general settings. So, if we click the settings button and choose settings, at the very bottom of the list of settings is manage embed codes. Also, by the way, this is also how you find out what kind of subscription you have to Power BI because at the top of the list it shows, for example, that I'm a Power BI Pro user. This is everything to do with my licensing and nothing to do with my skills. If I want to revoke these codes, I simply delete them here. In the future, anyone who attempts to use either the email embed code or the webpage or blog embed code that were created will go nowhere with those codes. Or the other reason I might come here is to actually have access to those codes again, to use them all over again. But if I delete these, then the published webcode for the report, both of them, will be deleted. Now, they won't be deleted immediately. They get deleted on the next scheduled update, which you can think of as some time in the next hour or so. If you accidentally publish something and you think oh no, I need to undo that, you can delete it, but it will take an hour. And the links we created will be available for that period of time and then they are nonfunctional. A couple of other items worth noting about publishing to the Web and managing our items that have been published to the Web. There are some types of things that can't be published. If you have a live data connection, for example, it doesn't work. Or, remember that this particular visualization, our Arc GIS visualization, will not work when published to the Web, it doesn't publish at all. And some other custom visualizations typically are not publishable. And because when we publish a report we publish the entire report, we don't have a way around this error, except to remove this visual from the version of the report that we intend to publish.
Embed a report in SharePoint Online ::
Selecting transcript lines in this section will navigate to timestamp in the video
- [Instructor] You can embed a report in SharePoint Online. Let me show you how to do this. First, I need to open my report in Power BI, as I have right here, and I'm going to choose Embed, SharePoint Online. This is the link that I will need, and if you click Learn More, you will go to a great article that will tell you all about all of the options for embedding links in SharePoint, but I'm simply going to select, the entire item is selected, and I'm going to copy this and then close. Now, I'm going to go to my SharePoint site, and I'm going to create a new site page. Here's my new page, and I'm going to click the plus to add a new web part in column one. And if you scroll down in alphabetical order, you will get to the point where you'll find Power BI, between Planner and Quick Chart. Click Power BI, and the part is being added. Include a Power BI report on your page. Click Add report. On the right-hand side, paste the report link here, control v to paste that link. And it asks us in a moment which page of this report we want to display. It takes a moment to load because it's actually gone out to the Power BI service to grab this information. So which page would we like to have displayed first? I'm going to choose Category and Buyer although they will all be here. This is simply the page that we will see by default. The display can be 16 by nine or four by three. You'll only change this if it doesn't look right. The navigation pane is available. If I turn it off, it won't be available any longer. That means I won't have tabs down at the bottom, and I will really only be displaying one page. So the navigation pane, allows my user to navigate in the pages of the report. If I wish to allow my users to filter, I can also show a filter pane. That's turned off by default, but if I want them to be able to filter, I'll allow that. That looks good! I'm ready to go, and now I can publish this new page that I have created. It says "Op, you're name can't be blank." Ooh, that's right. I have a new page, but the page doesn't yet have a name. I've been filling in my Power BI details, but the page itself needs a name, Sales Analysis. Now we can publish. A pane flies open to help others find my page. I can save this page as a template. No reason to do that. E-mail, Post as News that this page exists on the Sales Site piece of promotion. I can add this page to the site's navigation. I have page address information that I can copy, so that I could include it either in a links list for example, on a page in Teams, any place else that I would like to promote this particular page in SharePoint. This is a solid way to share reports internally from Power BI. It works fine and right now it works for me because I already have permission to see this report but not everyone does. So, I will need to return to my report and make sure that I'm sharing the report with all the people who would want to see it in SharePoint. Only users with Power BI Pro will have access to this report, but I can add the users who I'd like to be able to see this. The publishing part, that's very easy. The more tedious part of this is returning, sharing, and making sure that everyone who'll be able to view that report in SharePoint is also listed here so that they have access to be able to see it.
Share a dashboard or report ::
Selecting transcript lines in this section will navigate to timestamp in the video
- [Instructor] We can share from several places. For example, if I'm here in my list of dashboards reports, you'll find a share button on each of those. If I open a report or a dashboard, after it loads, one of my choices will be share. Regardless, I'm going to click share on the item that I want to share with others. This is sharing the report Retail Analysis - GVC. Note, only users with Power BI Pro will have access to this report. And then we proceed to enter email addresses. For individuals, for security groups, for distribution groups. For example, if I wanted to add Griffin Snow, could start typing Griffin Snow, or I want to grant access to Rob Molina. I can include an optional message. And then I have some options. Allow recipients to share my report. Allow users to build new content using underlined data sets. Send an email notification to recipients. And also, I can choose to share the report with the current filters and slicers in place. This is a report link that gets generated here at the bottom. If I don't send an email notification to recipients, then I'm going to need to select this link and copy it, so that I'll have a way to provide an easy way to provide access. But I'm going to include myself so that you can see the email that gets generated when I share this report. Here I am in Outlook. Gini von Courter has shared Power BI Report with you. Download some pictures. I'd like to share this Power BI Report with you. Note, that I didn't provide any text for this message, so this is the default text. And from here, our choice to open my report also a prompt to download a mobile app when I click open this report. And let's actually go back and open this report again. Note that I am delivered to my browser. New page has opened. Here comes Power BI and the report. Now, if Rob Molina clicks that link, then when he goes and looks at shared with me, that report will be listed here. If I want to know who has access to a report or to a dashboard... I can go to my workspace, click the share button again, click access to see who has been granted access. If I have a number of people, I can search here. I can also manage permissions, and choose, for example, Rob Molina's permissions, and say I actually don't want him to be able to re-share, but I'd still like him to be able to read, or I can simply remove Rob's access. And it says do you also want to remove access to related content? Yes. Dashboards, data sets, all of it. Remove access for Rob Molina.
Create a workspace for collaboration ::
Selecting transcript lines in this section will navigate to timestamp in the video
- [Instructor] Workspaces are a great way to share reports, dashboards, and other Power BI artifacts. In a workspace, this is all about collaboration. Not only can people view the dashboards and reports you're creating, they can work with you. You can build reports and dashboards together. Workspaces were originally called app workspaces and the idea was that after you had used a dataset to build reports in dashboards, you would bundle it all together to create an app that you could then distribute to other people in your organization for example. Near the end of 2019, Microsoft released a new workspace experience for Power BI. We're living in a time now where we have classic workspaces and we have new workspaces. New workspaces are the default workspace type, although you can still create classic workspaces. The difference between the two is that when you create a new workspace in Power BI, you don't automatically get an Office 365 group. The old workspaces were shared between the Office applications, Power BI, therefore, when you created a new Office 365 group in Outlook, you also were creating one for Power BI. If you created a new Power BI group, you were creating a group for Office 365 and creating a Sharepoint site. Now the new workspaces don't create Office 365 groups, but they are group centered because you can assign roles, not to just individuals, but to groups. I can take an Office 365 group and give them a specific security group within a new workspace, but I can also do that with an active directory group as well as with individuals. Another feature of the new workspace experience is that I can add a workspace contact list so that it's easy for people in my workspace to know who they should contact about particular dashboards, reports, and so on. To create a new workspace, go to workspaces, and click the create a workspace bar at the bottom. Note that you're getting an upgraded workspace. If I wanted to create a classic workspace, I would revert to classic. And again, if you'd like to know more about these options, click learn more. I'm going to name this particular workspace SunScope Team. If it were unavailable, I would notified at this point. This is a collaboration space for SunScope products. Scrolling down, I can learn more about workplace settings, I can click on advanced, I can add a contact list, either the workplace admins, right now that would be only me, but anyone else I added as an admin, or specific users in groups. I can also assign an existing One Drive, all I need to do is type in the name of the Office 365 group that this is for. And then if I wish, I can upload a workspace image. It will go to my pictures folder, but I provided you with an image in the chapter seven folder, there's a sun sprout, and I'm simply going to add that. You can add an image if you wish as well, the image needs to be small, less than 45 kilobytes. I'm going to click save and create our new workspace. Welcome to the SunScope Team workspace. You're on your way to exploring your data. Notice content, discover content, create new content, files, databases, even data flows. And if I look at my list of workspaces, there's my SunScope Team workspace.
Add users to a workspace ::
Selecting transcript lines in this section will navigate to timestamp in the video
- [narrator] After creating a workspace, it's part of your task to give some people access. There are four roles that are available in the Power BI workspaces: viewers, contributors, members, and admins. And if you have just created a new workspace, you are an admin. Viewers have the ability to view items like dashboards and reports, and interact with them. And that's really about it. Contributors can do that. They can also copy reports. They can publish reports in the workspace. They can create, edit, and delete workspace content. Members are a step up from contributors. They have all of the contributor's permissions. They also have the ability to share an item or an app, including allowing others to re-share items. They can publish and update apps. And they can add people with their permissions and below, so other members, contributors, or viewers. So you could think of someone who is a member as having a semi-administrative role. An actual admin can do everything that members, and contributors, and viewers can do, plus they can add and remove administrators. And they have the ability to delete the workspace. If you are an admin in a workspace, when the workspace is open, you'll note a command button for access. Simply click access, and then begin to add email addresses or group names. If you'd like to know more information about this, you can click learn more. So I'm going to add Rob Molina. It will find him. And I'm going to add Rob as a member. Click add. Now, everyone I add here has to have Power BI Pro. If they are contributors, members, or admins, they really are doing things that require a Pro license. If folks are added who are not members, admins, or contributors, who are simply viewers, than those individuals do not need to have a license. And they can still view and interact. When you're done adding people, simply click close. At any point, when you want to be able to see who has access, simply click the access button to open it up. It shows you the name of the workspace and the names of people and their permissions. If you want to remove someone's permissions, simply go to access, choose that person, either assign them a new role, or remove them.
Use a workspace ::
Selecting transcript lines in this section will navigate to timestamp in the video
- [Instructor] Now that we've created a workspace, what do we do with it? All of the items that we've created to this point were saved in My Workspace. My Workspace is your very own private workspace. Everything that you create in My Workspace stays there with you as the only owner and viewer unless you choose to share it. With a workspace like SunScope Team, we begin again. We create content. We upload files. We connect to different data services, all of the things we previously did in My Workspace. So, for example, I'm going to get some data. And the data that I'm going to get is stored in OneDrive for Business. A subtle difference that you might notice here is that Personal OneDrive isn't a choice now in this app workspace, only OneDrive for Business. We'll connect. Once I've connected I'm here in My Workspace, and I have a dataset, and then I will continue to create other items, to create dashboard, to create reports, to invite my colleagues if I have given them access to do the same, so that we are working together to create all of these items that have not just shared users, but also have shared contributors, shared designers. And I've actually done that. After I brought in my dataset, I took a few moments and I created a report. It's not much of a report, but it's a report. And then I took one of the tiles from that report and I pinned it to a dashboard and created a small dashboard. So now I have a dataset, a report, and a dashboard, all in my new workspace.
Publish an app ::
Selecting transcript lines in this section will navigate to timestamp in the video
- [Instructor] Collaboration is one reason to create a workspace but these were originally called app workspaces for a reason. One of the reasons that you create a workspace is to be able to create an application. I can't publish an app out of My workspace. I can only do this out of a workspace that I've created for this purpose, like I created Sunscope Team or any other specific workspace that was created other than My workspace. So classic workspaces, new experience workspaces all work but not My workspace. So when I swing over to the Sunscope Team workspace that I've created, I have the ability to publish this app, which is really pretty slick. I have two dashboards, I have one report, and I have a dataset. The dataset has to be included but the reports and the dashboards not necessarily. So imagine that you have a team of people working on this particular application and we've generated a number of dashboards and a number of reports but then we can choose the ones that we want to include in the app. So in terms of dashboards, I'm simply going to include requests, the single report, and the dataset. And I'm going to publish the app. I need to enter a summary. It should be something better than this. You have 200 characters to describe it. "A site where my users can find help." I can upload a new logo but it's already using the logo that I uploaded for my team workspace, which is nice. I can choose a theme color. And it might be that all of the applications that our team creates use the same color or that they use different colors for different purposes. I'm going to choose just a darker color of blue. Actually, I'd like to choose something green 'cause I have green in my theme. Now let's go to navigation. "Add reports and dashboards to this app," which I've already done. There's the report and dashboard and you organize them so it's easy for people to find what they're looking for. There's the dashboard and there's the report. And on the dashboard I could add the name Dashboard if I wished. And the question is what section of the dashboard? There is no specific section. If I don't want it included in navigation, I can hide it. And the same thing with requests, what section of the report? In terms of advanced settings, simply the default with the navigation. And then I have permissions. This app does not use datasets from other workspaces. But I'm going to give access to the entire organization, my organization. I'm not installing the app automatically. This is a new choice to allow all users to connect using the build permission. If you'd like to know more about it, click Learn more but what it does is it allows users really to add their own reports for their own experience and their own dashboards. And do we want users to make copies of the reports in this app and then customize them? We're allowing both of those things. If we don't want to allow either, you simply turn them off. And when you're all done, we publish the app. It says, "When you publish an app "that has a large distribution, it might take "a while to process, up to a day." They're not kidding. But in our case, it's so small that there's really nothing here. I can give people the link below or I can direct them to Get apps in the Power BI service. If I go to the app as if I were another user it'll fire up here. The app will install. Remember that it is very small so it will take almost no time. And here's my dashboard. And here's my single-page report. That's it, that's everything we have. I did not allow access to the dataset separately. Therefore it's really not here to be utilized. Users can still subscribe, do everything else that they can do with the report but this workspace and this application is separate from that. If users go to Apps, here's the app that I created right here. If a user goes to Get apps in our organization they'll find this listed under My organization's apps. This app workspace replaced a piece of functionality that was originally called Content Packs for Power BI. This is the new and continually-improving way for us not just to share information with other people in our organization but to work collaboratively with our colleagues to create and refine and then maintain a really good app that will be useful to other people in our organization.
Create a QR code to share a tile ::
Selecting transcript lines in this section will navigate to timestamp in the video
- [Instructor] A QR or a quick response code is yet another way to share information, to share a dashboard or even a specific tile. I've opened our retail analysis sample dashboard that came with the retail analysis sample. I'd like to share this year's sales, last year's sales with a group of people and I'd like them to be able to not just have a print out of it or see it on a screen during my presentation, but be able to view it on a mobile device. A QR code or quick response code is a code that can be placed anywhere. You can print it on a business card, you can put it in an email. I've seen them on posters in the subways in New York City. If someone is using a mobile device with Power BI apps they can actually go to this tile and assuming that they have proper permissions, capture that QR code on their phone or tablet and then go directly to this tile. If I want to be able to create and then share a QR code, I need to first open the tile that I want to share in focus mode. And then in focus mode, when I open the menu my only choice here besides exporting this data is generating a QR code. So I'm going to generate a QR code, my QR code is ready, this is what it looks like. Don't take a picture of it, download it, but note that the QR code is only useful after I've shared the dashboard with them. So if I want folks who are at a presentation to be able to see this particular visualization, then what I need to do is share this dashboard with the people who would be in that presentation. Often what that means is I'm going to share a dashboard with everyone in my organization so that I can do this. So let's download this and I want to save it so that I can get back to it so I'm going to just pitch it on my desktop for a moment and say I'm done. So here's my QR code and I can put this anywhere I wish. I can place it on the bottom of a handout, I can print it on a t-shirt but a very common place that I would put this is on a slide in a presentation that's going to be on the screen long enough for people to actually scan the code, not a slide that I blow past in two or three seconds, but a slide that's on the screen for six seconds, for 20 seconds, or at the start of the presentation on the initial title slide. Later in this course in the second on mobile devices we'll see how users can capture this QR code on a mobile device and view the tile that this QR code directs them to.
8 . Using Power BI Desktop ::
Get Power BI Mobile ::
Selecting transcript lines in this section will navigate to timestamp in the video
- [Instructor] I want to download Power BI to use on my mobile device, my phone or tablet, and there are two different ways to approach this. The first is to go to wherever I normally go to download applications, from the Apple App Store, Google Play, wherever I go depending on my device, and simply search for Power BI. The other possibility, easy enough to do, is to click in Power BI Service, Download, choose Power BI for Mobile, Send me a Download Link, and an email with a download link has just been sent to my email account. I'll open my email on my mobile device and download the application. In the next few movies, we're going to explore Power BI Mobile. So if you'd like to continue to work along with me, take a moment now and download and install the appropriate app on your mobile device.
Install and launch Power BI Desktop ::
Selecting transcript lines in this section will navigate to timestamp in the video
- [Instructor] Thus far we've working in the Power BI Service. PowerBI.com. This is a different creature. This is Power BI Desktop and a freestanding application of its own. In some ways, it duplicates the functionality that we already know how to use. For example, we can use Power BI Services to create reports. But we can also create reports right here. Here's our report canvas. We can use Power BI Services to get data but there's a Get Data command here as well. I'm not going to show you everything there is to know about Power BI Desktop but there are four reasons that you should download and use this tool, even if you are primarily using Power BI Services. First, while we can get data in Power BI Service, we can get more types of data in Power BI Desktop. You may remember that short list of files types that we have in the Power BI Service. Here, Excel, Power BI Data Flows, Text, CSV, Web, OData feeds, and if I click more, I get an even longer list of all the types of data that I can connect to quite easily from Power BI Desktop. Next, Power BI allows us to create data models. We've been importing datasets and when we needed to change something about them, for example, a column heading, we needed to change that in the data source in Excel or in the CSV file but with Power BI Desktop, we can model our data directly, transform our data, shape our data, merge and append datasets and add calculated or custom columns. If we need to clean up our data, or modify our data structure, and we often do, Power BI Desktop is the best tool to do it in. Another feature of Power BI Desktop is the ability to relate data. If I want to take data from two or more datasets and mash them together, then I want to use Power BI Desktop. For example, I might have one set of data that has addresses in it, including postal codes and another table that allows me to take those postal codes and look up the latitude and longitude for the center of the postal code. Two different tables. Without Power BI Desktop, my choice is to find some way to string those tables together in SQL Server or in Access or with a tool like Power Query. But with Power BI Desktop, which actually includes Power Query, I can simply bring those two tables into my data model and relate them. The fourth possibility is I want to work offline and for me, this is not insignificant. There are many times in my work life where I'm spending time in the air or on trains and even though I have access to the internet, it's not super fast and it's not rock solid. I want to be able to work offline using Power BI Desktop and then connect to my data when I have good Wi-Fi and high speed. These are the main reasons that I'm driven to Power BI Desktop. We are not going to take a deep dive into Power BI Desktop because there is an entire course on this application in the LinkedIn learning library. So if you like what you see in the next few movies, and we haven't yet spent enough time together, I highly recommend learning Microsoft Power BI Desktop. But now let's see how we would quickly install Microsoft Power BI Desktop because it's really easy from the Power BI Service. From Power BI, click the More menu, choose Download, Power BI Desktop. And you will end up in Windows 10 in the Microsoft Store with a get button. If you are not running Windows 10, if you're running an earlier version, then you may end up on a different web page with a recommendation either for the 32 or 64-bit version. Here you simply click Get, there you would simply click Run or Save in order to download and install this application.
Get data ::
Selecting transcript lines in this section will navigate to timestamp in the video
- [Instructor] Here we are signed into Power BI Desktop. The first thing that we want to do is the first thing that we do in Power BI Service, which is to get some data. I'm going to click get data, we have lots of choices. If I click more, we have categories, all, file, databases, Power BI datasets and data flows, Azure, online services, which is a very long list, and other, and so if I want to connect to data like Dynamics 365 Online, that is much easier to do here in Power BI Desktop, simply because the connection exists, but we don't yet have a template app to be able to connect to that easily from Power BI Service. I'm looking for Excel, I can always search, that will work just fine. So I'm going to connect to an Excel file and you will find this particular file in the Power BI exercise files, chapter nine. I've stored mine in One Drive so they'll refresh for me and it is called State Pops. And do this to open it and this is a data navigator. It has four types of data that we could import. At the top, we have two tables. And then we have spreadsheets and the spreadsheets have tabs on them, notice that's the difference. Tables have a header at the top. And so for example, if I click on the U.S. Pop table, it shows me information, but at the top I have column one, column two, column three, and so on. Whereas, a little bit of change with the U.S. region and state pop. But if I choose one of the tables, like the list of U.S. states by historical population, notice that somebody has taken the time to format this as a table and say that my table has a header row. So there's the difference between the two. The infrastructure of the table is more clearly identified. So I'd prefer to use the table rather than the spreadsheet because it's cleaner and there's nothing here for me to fix. What I need to do is actually click the check box and then, click the load button, and the data is being loaded into the model and here it is. Data's here, I have a fields list on the right hand side. And we actually have three different tools or views here in Power BI Desktop. The first is my report editor and we should be used to this, it's really mostly what Power BI Service looks like, but with a really nifty lookin' ribbon at the top. Then I have a data view that shows me all of the data in my dataset. And then finally I have a view of the data model itself, the tables and perhaps the relationships between them. If I wished, I could sort and filter my data here, but the choices that I make will not persist. If I want to change something about my data in a way that will affect it not just now, but in the future, then I'll need to modify the model, which we'll be doing later. So don't jump in here and clean up anything. It will let you, but that's not what you want to do. Each row of data here includes the name of a state or the District of Columbia, the year it was admitted to the United States, and the census information for years starting with 1790, the first year that Herman Hollerith ran a census for the United States. And if I scroll down to the bottom, there's actually a summary, a total row that is row 52, last column is a key, or a unique value that identifies each of the rows, simply a counter. Now that we've added data, we can improve it greatly using the tools in Power BI Desktop. We might want to save it first, or not. If you just imported this data for the first time, you should save it in Power BI Desktop, especially if you're not going directly to the next video. But this is just a matter of choosing save as, and then saving it somewhere and in my case, I'm going to save this data as a Power BI file back in the same folder that I got it from. And I'm going to give it the same name, State Pops, but it will be State Pops, PBIX.
Reduce data ::
Selecting transcript lines in this section will navigate to timestamp in the video
- [Instructor] We're continuing here with our dataset from the prior movie which came from getting data from an Excel spreadsheet called StatePops. While this is a lovely dataset, it is not yet in the shape I need it to be in to work with in Power BI and use for state-by-state analysis. For example, we have a total row at the bottom and we don't need that. We also have the District of Columbia included and if our intention is only to compare states, then we need to eliminate the District of Columbia from our dataset. One way to remove both of these rows that's very easy is to simply get rid of the rows that were never admitted to the Union. So if I look for admitted, and choose to eliminate blanks, now I have not 52 but only 50 rows left in my dataset. That was pretty slick. But those other two rows are still here. They didn't really go away. They're just hidden. This is a filter. And if I clear the filter, they're back. What I need to do is I actually need to edit the query. So I'm going to choose Edit Queries. And this is going to open the Power Query Editor. Now, if I make the same type of a choice here in the Power Query Editor, it will actually make a difference. Is I'm going to choose here the items that are null and remove them and click OK. And I'm actually now left with 50 rows if I look down at the status at the bottom. Notice that one of the steps that I applied was that I filtered rows. If I want to undo this, I simply click to undo it and my 52 rows are back. So there's the difference is that if I'm working in the Power Query Editor, the changes that I'm making are actually transforming my data. At the top, I have a great set of tools. Frequently used tools, tools to transform data, to add columns. Different views and some help. And in this Query Editor, I get lots of tips. For example, I can look and see that Name is a text column, Admitted is a number. So are the numbers that appear in each of these other columns. And when I get all the way out to the right, Key is also a numeric column. You'll notice we have lots of null values in places where they presumably didn't take a census. When we're looking at the data rather than looking at the Query Editor, they'll simply show as empty. There's one other change I'd like to make to reduce my data. This dataset does not go all the way up to present day. It actually ends just before the Civil War. So there are states that exist now that were not states then where we never took a census because they weren't states. So what I'd like to do is I'd like to remove those. So any state that is null in 1860 is a state where we never took a census because we don't have states where we started and then stopped. So I'm going to remove all of the records that have a null value for 1860. And now I'm down to 42 rows. We have successfully reduced our data but there are some other tools that I'd like to point out to. We could choose columns and choose to remove columns. Choose specific columns. Remove the columns that we didn't select. You have the ability to keep or remove rows including removing rows that have duplicates. Or remove rows that have errors in them. You could also remove alternate rows so that you could create a smaller set of data, a sample dataset so to speak. I'm all done now. So I'm simply going to go to File and I'm going to choose to Save. There's still pending changes in your query that haven't been yet applied. Do I want to apply them? I'm going to say Yes. My query's being worked on and I'm going to close it. I'm going to actually save this as as a pbix and I'm going to give it a slightly different name. I'm going to call it ReducedStates which sounds like a science fiction movie but will work for me and I'll see you in the next video.
Transform data ::
Selecting transcript lines in this section will navigate to timestamp in the video
- [Instructor] This is the file that I saved at the end of the last movie. Its name is Reduced States. If you've been working along with me, and if not, it is also in the exercise files. I'm going to drop back into the query editor, simply choose edit queries. Sometimes when you're working in a PBIX file, if it has been moved, and this could happen if we're removing it from development into production, if we downloaded it and then moved it from one location to another, but if you ever see an error message when you drop into the query editor that says that the data source could not be found, then what you need to do is you need to change the data source settings, which are right here on the home tab and look for data sources in the current file and this is what it's looking for. You'll have an error and you can change the source and locate the file that you need to use. So in other words, we're not looking for the PBIX file, in this particular case, we're actually looking for StatePops.xlsx, which you have a copy of in the chapter nine folder. So you're being asked by Power BI to point the query once again to one or more data sources and this is where you would do that, should the need arise. Now I'd like to show you some of the transformational tools that are available in Power BI Desktop. There are a subset of transformation tools that are available on the home tab, in this group over here called transform. These are frequently used transformation tools. I can split a column. For example, if I have city and state together in one column, I can split that column by a delimiter. I can modify my data type. I can choose to use the first row of a dataset as headers so if after get data, my header is being listed as row one or record one and I have column one, column two, and so on as headers, I simply choose to use my first row as headers. Or, if it's backwards, I could choose to use my headers as a first row. We also have an entire tab full of transformation tools and while drilling into these is well beyond the scope of this course, we're going to use a couple of them right now. The dataset that we have here is not unusual. It was created from a table where in every row, we had a state and information about the year and the actual count of the census. That data was summarized as a pivot table and that's what we're looking at here. So, rather than having a row where it shows me Alabama was admitted in 1819 and in 1800, there were 1250 people there in the census, instead I have this pivot table. One of the tools that is absolutely brilliant in Power BI Desktop is the choice to be able to unpivot columns. I can either unpivot all of the columns that aren't selected, or unpivot the columns that are selected. I'm going to select just the first two columns, name and admitted, because I will want those to appear in every single unpivoted row. And now I'm going to unpivot all of the rest of it. Unpivot all the other columns. And when I do, I get for Alabama, a row for each year the census was taken and I only have one problem though, after I hit 1860, I hit the key. So I'm going to undo this, unpivot other columns and let's just slide out here to the key column, remembering that we're in the query editor and we can just say that we don't need this column anymore. We can just delete it. So we've removed this column. Select the name and admitted columns, click the drop down, and unpivot all the other columns and there we are. No key columns and every single row would be a record. I love this unpivot feature, it's incredibly powerful because it allows me to take a pivot table and get the underlying rows of data instead. There are some other choices on the transform tab that I'd like to point out to you. Occasionally, I will get a set of data that's just wrong. We want our data in Power BI to be longer than it is wide. So if I have a dataset that's really wide, imagine, for example, that this came in with the names of the states across the top for example. Then, what I want to be able to do is to transpose the data and this allows me to transpose the entire table. We have a similar function in Microsoft Excel. Turn the table 90 degrees, I can reverse my rows so that the last rows are displayed first. I can transform my data type, detect data types, I can rename a column. And I'd like to do that because this is going to be the census year and rather than value, I want to rename this to population. Remember, if I want to be able to change column names and I can't affect my data source, my best bet is to bring my data into Power BI Desktop and use the query editor to do this. I have commands now for specific types of columns, text columns, number columns, date and time columns, that allow me, for example, to modify, to round, to know whether something is odd or even and so on and in a text column, to be able to parse, to extract as I might in Excel, to format in uppercase, lowercase, to trim, to clean, to get rid of leading spaces and trailing spaces. These are all examples of things that you might need to do with your data. There's a lot here and that's a good thing because much of the data that we have access to may not be clean or pristine when we open it up. It's not unusual that I need to modify data types because someone typed text in a column like any that should have only had values, and therefore, it's being treated as text. Or needing to rename columns so that I have appropriate, human readable column names that will work with Power BI's Q and A feature. Remember that you could always go back and delete a step that you carried out. I'm finished with this data set for now, so I'll go back to home, choose close and apply, My changes have just been applied. And I'm going to save as and save this as Reduced States final.
Relate tables ::
Selecting transcript lines in this section will navigate to timestamp in the video
- [Instructor] Another powerful reason to use Power BI Desktop is to work with multiple data sources and connect them through relationships. I've fired up Power BI Desktop again, and we're going to get data from an Excel workbook stored in the chapter nine folder and it's called Relationships. When I open it, you'll find that there are four possible data sources, a table and then three sheets. One for customers, one for states and one for territories. And I am going to select all three of them and load them into Power BI Desktop. These are all relatively small data sets, so they'll get here quickly. Here are our tables and fields on the right-hand side. I'm going to click the model window. Ahh, notice that our states and our territories say column one, column one, two, three, let's go fix those first. Let's edit our queries and for the territories table, simply choose to use the first row as headers, territory abbreviation and territory. And for states, we'll need to do the same thing, use the first row as headers. Those are fixed, close and apply. Here we go, that all looks better. Don't need the properties, open the model and now, what we're going to do is relate the two fields in a table that go together, let's spend a moment with the data. Here in customers, we have a state, it's an abbreviation. In the state's table, we have that same abbreviation and it returns a name. Then we have an abbreviation for the territory, and in the territories table, that's called territory abbreviation and it returns the full name of the territory. Back to our model, therefore, if I drag state from customers and drop it on abbreviation, it will create a relationship. Now, it doesn't matter what direction I drag in, what Power BI does is a quick analysis, and it looks and it sees that abbreviation is a primary key here. In other words, every value here is unique. So AL only appears once here, but AL appears many times, once for each customer in the state of Alabama, in the customer's table. The same thing is true here, remember, that in the state's table, this is the abbreviation for a territory. If I drag and drop it on territory abbreviation, Power BI does an analysis and notices that abbreviation appears uniquely in this table, but over and over again here. Having now related those three tables, I have the ability to say, I'd like to have a list of a customer's first name, last name and territory. And Power BI will use this model to be able to connect the path all the way to the name of the territory. Being able to do this is important, we have a lot of data sets that are incredibly complex and we want to be able to create visualizations in our reports and tiles in our dashboards that say, report for customers in Florida, not report for customers in FL. Here we have a model that allows us to take data from three different sources, three different tables, and then be able to retrieve that information. I don't have to include information from the table in between, I can simply select first name and last name from my customer's table, territory from my territories list and even though I'm not including anything from states, it doesn't matter because the relationship window handles that for us. I'm going to take a second and format the values so that you can see them, increase the text size, but our first two columns come from our customer's table, our last column comes from territory, and this information is absolutely correct because of the relationships we created here in the Power BI Desktop modeler.
Get data with the Power BI service ::
Selecting transcript lines in this section will navigate to timestamp in the video
- [Instructor] I created this Customers States Territories model here in Power BI Desktop and if I wanted to, I could create my report here. You and I know how to do this because we've been working with an interface very similar to this for awhile. However, I'm content to have cleaned up my data sources here in Power BI because I can only do that here in Power BI. I have to do my modeling here, but when I'm done, I can then go to the Power BI Service, click get data and pull my model in. Let's take a look at that. Remember, that when we get data with Power BI under files, our third file type was PBIX files, files created from Power BI Desktop. If I return to the folder that my exercise files are in, I've been saving files there from Power BI Desktop and here they are, Customers States and Territories. Connect. My data's being imported. If I go to my dataset, here it is and I can use my dataset to be able to create my reports. While I can create reports, either here or in Power BI Desktop, if I want to create dashboards, ultimately I have to come to the Power BI Service. So it's up to you where you would choose to create your reports, but again, if I would like to include someone's last name, their city, and their territory, I can easily do that or create any other type of visualization I wish here in Power BI Service. Remember, that Power BI Desktop's contribution to the Power BI environment is a great tool for cleaning, transforming, and modeling data so that you can use it to create reports in Power BI Desktop or the Power BI Portal, and in the Power BI Portal, use it also to create dashboards.
Export a report from Power BI service to Desktop ::
Selecting transcript lines in this section will navigate to timestamp in the video
- [Narrator] I'm in Power BI Desktop one more time, simply to create a small report. All I really want to do is add first name, last name, latitude and longitude. That's one good choice. And another choice is, I'm going to take a States by name. And I'm going to count the number of IDs we have, so that I can see how many, different requests we have from each state. And I'm going to turn that quickly into a chart. Now I'm going to resave Customers States and Territories, which now also includes a report. Now, let's return to the Power BI service. And I'm going to get data and the data that I'm going to get is a file, stored OneDrive for Business. In my Exercise Files Chapter nine, where I've stored this. And it is called Customers States Territories, I'm going to Connect to it. When I do, I should receive a prompt that says wait a second, we already have a data source with that name. But it just brought it in. And that's kind of cool that it didn't simply overwrite it, I have two of them. So now if I go to my reports, I also have two, but they are essentially different. The second report, is the report that I just created. And was imported with my PBIX file. And the reason that I wanted to show you this, is this report has an attribute that my other reports don't have and that is I have the ability to download a copy of this PBIX file. If I go look at any other report, if I go take a look at Retail Analysis and look at Download the PBIX file, unavailable grayed out. So why is this one different? The reason that this report is different is that this report was not created in Power BI service. It was created in Power BI Desktop. And for that reason, I can download this file. Any report that was created in Power BI Desktop, after November of 2016, can be modified in the Power BI service but downloaded again because it started in Power BI Desktop. This might be a compelling reason to start, all of your reports or many of you reports in Power BI Desktop, and then continue to work with them in the Power BI service, powerbi.com because these then become portable reports in a way that reports created only in the Power BI service cannot be.
To up skill your knowledge on Power BI : Learn the below courses on linkedIn
If you work in Marketing
8. Use Power BI Mobile App :
Comments
Post a Comment