Power BI Beginner Tutorial

hi everyone i'm trish connor cato and i'd like to welcome you to microsoft power bi this extensive course encompasses 13 modules covering the basics through some advanced features in the application the first module will explore the meaning of data analytics and the different roles available in that space we'll outline the important roles and responsibilities of a data analyst as that is the role we'll be functioning in when we're working in the application we'll also explore the power bi licensing options and their implications on the landscape of the power bi portfolio of products and services in

the second module we'll get hands-on in the power bi desktop application this module will explore identifying and retrieving data from various data sources you'll also learn the options for connectivity and data storage and understand the difference and performance implications of connecting directly to data versus importing it during this module module three is where the real work begins the module will teach you the process of profiling and understanding the condition of the data you will learn how to identify anomalies look at the size and shape of the data and perform the proper data cleaning and transforming

steps to prepare the data for loading into the model the next module will teach you the fundamental concepts of designing and developing a data model for proper performance and scalability this module will also help you understand and tackle many of the common data modeling issues including relationship security and performance module 5 will introduce you to the world of dax that's data analysis expressions it's a function language that's used in power bi to create calculations you'll learn about aggregations and the concepts of measures calculated columns and tables and time intelligence functions to solve calculation and data

analysis problems we'll move on to optimizing model performance this is where you'll be introduced to steps processes concepts and data modeling best practices necessary to optimize a data model for enterprise level performance module 7 introduces you to the fundamental concepts and principles of designing and building a report including selecting the correct visuals designing a page layout and applying basic but critical functionality the important topic of designing for accessibility is also covered with the exception of the first module all other modules on this slide will be conducted in the power bi desktop application once we get

to module 8 we'll begin working in the power bi service the online component of the application in this module you'll learn how to tell a compelling story through the use of dashboards and the different navigation tools available you will be introduced to features and functionality and how to enhance dashboards for usability and insights the next module will teach you about paginated reports including what they are and how they fit into power bi you will then learn how to build and publish a report the next module helps you apply additional features to enhance the report for

analytical insights in the data equipping you with the steps to use the report for actual data analysis you will also perform advanced analytics using ai visuals on the report for even deeper and meaningful data insights since we'll be working in the power bi service module 11 will introduce you to workspaces including how to create and manage them you will also learn how to share content including reports and dashboards and then how to learn how to distribute an app module 12 focuses on managing data sets in power bi in this module you will learn the concepts

of managing power bi assets including data sets and workspaces you will also publish data sets to the power bi service then refresh and secure them the last module in this course is about row level security it will teach you the steps for implementing and configuring security in power bi to secure your power bi asset if you're enjoying these videos please like and subscribe if you want to earn certificates and digital badges please become a member of our patreon the link is in our video description if you have any questions you want answered by one of

our instructors please join our off-site community the link is in the description as well as always if this course has exercise files you'll find them in the video description below welcome to module one where we'll get started with microsoft data analytics this is the only module in the course where i'll be using a powerpoint slide presentation to give you background information on the field of data analytics the licensing options in power bi and the products and services that will be available to you all subsequent modules will have you hands-on in power bi you can access

this powerpoint presentation from the video description below so let's get started with data analytics and microsoft what is data analytics it can be described as the process of analyzing raw data to find trends and answer questions a successful data analytics initiative will provide a clear picture of where you are where you have been and where you should go the field of data analytics is very broad and expanding and as such there are many roles that fit in that area there are also four primary types of data analytics descriptive diagnostic predictive and prescriptive there are supplemental

slides in this presentation that will give you more depth on each of those four types there are also additional slides that will give you insight on the wide variety of roles that are available in this field when we get hands-on in power bi we'll be functioning in the role of a data analyst data analysts provide real-time insights across an organization in power bi that means the data analysts will connect to and transform data with advanced data preparation capabilities they'll also create interactive data visualizations and uncover important insights the data analyst is typically the person who

publishes dashboards and shares insights to drive informed action throughout your organization it is important to understand the license options for power bi as your features may vary based on the type of license that you have power bi free version includes the power bi desktop application as well as the online power bi service a user with a free license can only use the power bi service to connect to data and create reports in dashboards in the default workspace known as my workspace they cannot share content with others or publish content to any other workspaces they can

however consume content that is shared with them as of this recording the power bi pro license is estimated to be 9.99 a month with a pro license users can publish content to other workspaces in addition to my workspace they can share dashboards subscribe to dashboards and reports and share with users who have a pro license they can also distribute content to users who have free licenses power bi premium licensing has two variations the per user variation as of this recording is about twenty dollars a month can do all of the same things as the power

bi pro license but can also share with users who have a premium per user license in addition premium per user license holders can distribute content to users who have free and pro licenses the other variation is the power bi premium per capacity license can do all of the things as the premium per user licensing in addition to more things it's usually implemented at the enterprise level there is a word document in the video description named website links and more information where you can view the power bi pricing and product comparison website to see how the

feature sets differ by licensing level in this course i'm using a premiere per user license and may have features on my screen that you do not have in your version depending on your license level the landscape of products and services in power bi is amazing depending on your licensing the feature set varies but even with the free version you'll have access to a robust set of services the three main components of power bi are the power bi desktop application the power bi service which is in the cloud and the power bi report builder let's take

a deeper look as a data analyst most of the time you will be working in power bi you will be working in the power bi desktop application from there you can connect to over 80 data sources transform your data analyze it shape and model your data you can create calculations called measures and calculated columns create visualizations and reports you can publish to the power bi service and have access to the power query editor which can help you with your data transformation the power bi service is cloud-based it allows you access to some data sources you

can also create visualizations and reports there you can create paginated reports and that is where you go to create your dashboards there is some overlap in what you can do between the desktop and the service as you can see on the slide here the two are bundled together and as i said earlier even the free version has a very robust feature set lastly the power bi report builder allows you to create paginated reports in the power bi service we'll explore paginated reports in a later module now that we've covered the background information let's get our

feet wet in the power bi desktop yay we made it through the background information and from now on we'll be hands hands-on you may want to pause and grab the five files on the slide from the video description i would suggest you put all of them in the same folder on your computer before we load data into power bi let's review the data that we are going to put in there i've opened the sample superstore excel file that you grabbed from the video description so that we could kind of take a look at the data

this file represents typical orders sales customer and products information spread over three sheet tabs on the order sheet tab you'll notice that there is additional information that's not related directly to the order for example customer name customer id those fields really should re reside separately from the order information on this sheet we'll learn how to address that issue later on in this course then we'll take a look at the returns tab and that just has order ids and the returned status and we have a users tab which contains manager information i'm going to go ahead

and save and close this workbook and you'll see how this data comes into power bi in just a few moments now that we've explored the sample superstore excel file we're going to get into power bi there are many ways to launch it like any other application i'm going to use my start menu to launch it so i'm navigating down to my taskbar at the bottom of my screen and in the lower left hand corner i'm going to click on start once the start menu opens i'm going to click on any letter that i see so

it collapses all the applications and i'm going to click on the letter p underneath the letter p you'll see all of the applications that begin with the letter p we're going to select power bi desktop to launch this application the application is loading it really doesn't take a long time and you'll notice that the application opens in the background with a splash screen on top of it the first thing we're going to do is sign in in the middle of the splash screen you're going to click on the yellow get started button it will show

you a prompt to enter your email address and another prompt for your password go ahead and follow the prompts and log yourself in we'll do a comprehensive tour of the power bi desktop after we load the data in from sample superstore but in the meantime if you look in the upper right hand corner you'll notice your name and that indicates that you're signed into the program if you want to take a moment go ahead and click on your name and on that screen you would be able to sign out sign in as a different user

view your account and go to the power bi service which is the cloud-based component of this application the first lesson in this module is getting data from multiple sources we're going to use the sample superstore excel file as our first source of data we'll be using other data sources in this module as well on the home tab of the ribbon you'll see the arrow pointing to the excel workbook button go ahead and click on that icon and it will launch an open dialog box navigate to wherever you put the class files for this module and

you should see your sample superstores file we're going to double click it or you could click it once and choose open you'll have a navigator window appear on your screen and it shows the three tables that were in that sample superstore file as you select each table the preview pane to the right will show and it shows a truncated size of the data but you can scroll through and see some of the data that you saw in the excel workbook we're going to put a check mark in front of every table and as you select

a different table the preview pane fills with that table's information in the bottom right hand corner of the navigator screen you have three buttons cancel would be the same as doing the x in the upper right hand corner load and transform data this time around we're going to click on the load button you'll learn more about the transform data button later in this course when you click on load you'll see that it's working and you'll notice a load box that will pop up letting you know the tables that it's working on and what it's doing

you also had a yellow band briefly across the top of the screen at this point it doesn't look like anything much has changed in your file before we get into a comprehensive tour of the power bi environment it'd probably be a good idea for us to go ahead and save the file power bi has a quick access toolbar similar to what you find in the other microsoft applications it's in the upper left hand corner of the screen so there's a save icon undo and redo you can't modify the toolbar at this point in power bi

desktop like you can in the other office applications but we're going to go ahead and click that save icon so that we can save this file it should route you right into your working directory wherever that is on your computer in a save as dialog box let's name this file sample superstore the same as the excel file and you'll notice where it says save as type it's giving it a dot pbix extension there are only two extensions for power bi desktop files the default one is pbi x which is a power bi desktop file and

your only other choice would be a dot pbit which is a template file we're going to leave it on pbi x and click save now we're ready for the grand tour of the environment you'll notice that it also has a title bar like every other window now that we've named the file it's called sample superstore power bi desktop before it was just saying untitled you have a search function right at the very top center of your screen and again to your right you will see your name with all of your account information in that menu

we have a ribbon just like in the other microsoft programs the ribbon will change depending on what view you're in takes a little bit of getting used to but i'm sure you'll get comfortable with it and you'll see that play out very shortly i mentioned earlier there are three views in power bi desktop and the default view when you first log in and when you load data in is report view that's the view we're looking at right now the view buttons are on the left side of the screen like i said we're in report view

right now and this is the view button indicating report view the other two views that you have available are data view and modeling view you'll see both data and modeling view in just a few moments in the middle of your screen where it says build visuals with your data that is known as the canvas that whole blank area is the canvas if you look below and to the left of the canvas you'll see that we are on page one in report view and you have page tabs that are very similar to excel sheet tabs you

can add pages delete them duplicate them and hide them and you'll see that during the course underneath your page tabs you'll see an area it's a gray area at the bottom every microsoft program has it and it is called the status bar right now we have one page in this report and the status bar is reflecting page one of one the status bar will populate with different information depending on what view you're in in power bi desktop let's take a look at the right side of the screen it may look different on yours than it

does mine but the right side of the screen has three different panels they're actually called pains my filters pane is collapsed right here so you just see it saying filters and it has a leftward pointing arrow which i can use to expand it report view comes with these three panes so there's the filters pane we'll speak more detail on it when we start working on creating visualizations in power bi you also have a visualizations pane on the right side in report view that's the area where you go to when you want to choose what kind

of visualization you'd like to put on your report you have a host of options here and again we will cover that area very thoroughly later in this course the last remaining pane over there for right now is the fields pane well when you look in this fields pane you'll see the instances of what were on the three sheet tabs in excel so we have an orders table we have a returns table and we have a users table even though they're on sheets in excel once you bring the data into power bi by connecting to it

through get data or excel workbook they are known as tables so you can expand the orders table all of the tables come in collapsed you can expand the orders table and in report view you see the fields that are in the table but not the data that's in the fields just to go over some symbols that you'll see in a field pane if you notice you have the sigma symbol in front of customer id as well as a couple of other fields that's an indication from power bi that that field contains numeric data so whenever

you see the sigma symbol it means the field contains numeric data if you notice the order date field has an expand arrow in front of it and when you expand it you'll see what's known as a date hierarchy notice the symbol in front of the hierarchy it's indicative of a hierarchy in power bi you'll learn about hierarchies later in the course but will i will say now a hierarchy is a container of sorts for fields that you would like to kind of group together that's what it does so when i expand the date hierarchy i

see that the order date has been broken down into year quarter month and day that's what's in the date hierarchy as we progress in the course and we do different actions in power bi you'll see new symbols in the fields pane if you'd like to take a moment and look at the fields that are in the returns and the users table you'll notice when you expand the users table that it has two columns and they're named column one and column two when you're working with data you're going to want to make sure that the column

names are indicative of the data that's being stored in those columns in the next module you'll learn the process of transforming and cleaning your data and that's where we will clean up that table let's go over to our view buttons now and we want to access the view that will allow us to see the data that's in the tables that we imported from sample superstore so we're going to click on the data view button on the left side of your screen it's the second view button and it will take us right into data view when

we get into data view you'll notice that you have a new tab on your ribbon column tools and as i said earlier the ribbon will change depending on what view you're in and it could change again depending on what you're doing in that particular view you'll get used to it after working in this environment for a while dataview also has the fields pane on the right and i can use it for navigation purposes so if i'm looking for a particular column i can just click on it in the fields pane and in data view it

will navigate to and select that column for me the other thing that's updated here is the status bar in the lower left corner you'll notice now that the status bar in data view is telling you what table you currently have selected in the fields pane and how many rows are in that table it also is telling me because i happen to be in the profit column right now that in that column there are over 8 900 distinct or unique values so depending on the view the status bar will show different things the last view that

we're going to explore right now is called model view so make your way over to your view buttons and it is the third and last view button in the list go ahead and click the model view button to switch to that view we're going to have a deep dive into table relationships once we get to module 4. what i will say about model view is that it will show a card for every table that you've connected to from the outside data source which in our case is an excel workbook we'll be creating table relationships in

this view again in module 4. but in the meantime this view also has the fields pane on the right side of your screen and just like in the other views you can expand or collapse the tables that are showing in the fields pane another thing is there is a search box at the top of the fields pane and if you have a lot of tables it's very handy so you can just search for the field that you're looking for you also have an additional pane that you haven't seen before in this view this is the

properties pane and again we'll do a deeper dive into this in a later module and explain all of the choices that you have on the properties pane here this view also has page tabs down at the bottom you'll notice that there's a page tab there's only one initially and it's called all tables and it will put every table in your data onto this one tab in the form of a card when you're working with data sets that have several groups of related tables you can create more pages in here and put the grouped fields on

a separate page so it's not as overwhelming to deal in this view as it could be with a lot of tables so you'll get more information about model view when we get to module 4 in this course one last thing and it's just terminology here the excel workbook called sample superstore is our data source once you bring the data into power bi desktop it is known as a data set so we're looking at the sample superstore data set in power bi desktop what we want to do next is bring in data from an access database

we don't want to mix the data together with the sample superstore data so we're going to start a new instance of power bi desktop and then bring in data from an access database the access database is in the video description it is called northwind so what i'm going to do to start a new instance of power bi desktop what i want to do is i want to go up to the top left corner and i want to click on the file tab of the ribbon when i'm on the file tab you have many options but

that's one way of starting a new instance of power bi desktop sample superstore will also remain open the new instance opens in its own separate window go ahead and click on the file tab of the ribbon and click on new so power bi will relaunch and its own separate window it will bring up an untitled power bi desktop and it's like a separate file similar to having like a separate file in word or excel because we're already signed in before we brought in the data from sample superstore our splash screen now looks a little bit

different than it did when we first launched power bi desktop let's take a few moments to review the splash screen i'll start on the left side we can start the process of bringing in data from the access database by using get data on the left we also have recent sources our sample superstore dot pbi x power bi desktop file is listed there if we're going to access that file a lot it has a push pin we can pin it to the list if you're not going to want to access that file often to clear it

from the list you can right click on it and you can choose remove from list if you had a lot of unpinned files listed here you could remove all of them at the same time you also have the ability to open other reports your desktop files are known as report files so the only one we have so far is sample superstore we don't have any others to open at this moment in the center of the splash screen you have the ability to look at many videos to get information about power bi the one thing i

will say is that they really have help everywhere that you could possibly look for it in power bi both in the desktop application and the power bi service online so this one has a lot of videos how to get started building reports they have a link down in the lower right corner saying view all videos and if they're more to show it takes you online and you have a whole host of videos that you can look at from microsoft online i'm gonna disclose the internet for that if you don't want the splash screen to show

up when you launch power bi you can uncheck the box there i find it useful i can do a lot of things right from the splash screen on the right side of the splash screen in the upper right hand corner you have the x where you can close the splash screen and then it shows your username there are more help topics on the right side power bi is constantly updating so you might want to take some time and review what's new in power bi constantly updating you have forums where you can ask questions and get

answers and also interact with other users in the power bi community you have access to the power bi blog to keep you up to date on the latest news things that are going on and a host of tutorials i go to the blog and the forums and i'm a member of the power bi community 90 of the questions that may come up for me about power bi i find the answers in those vehicles now it's time for us to bring in data from the access database entitled northwind that is in the video description we're going

to click on the get data button on the left side of the splash screen and it's going to open up a window for us and in this window at this time as of the recording of this video there are over 80 different data sources that you can bring into power bi so on the left side of the get data screen it defaults to all and if you want to you can take a look you can scroll down the left side and look at all the different data sources now one thing i will say some of

the ones on the right side excuse me it's not the left side it's the right side all of the ones on the right side that have the word beta after them i know i passed one so here's one app figures and in parentheses afterwards it has the word beta that means that power bi is testing out this particular data source and it may or may not work out sometimes you'll come in here and things are no longer on the list and new things are on the list so it's always looking to allow you the ability

to bring in data from more data sources than it currently does so what we're looking for on the left side i'm going to click on other and i'm going to scroll back up to the top and you can see this is where you would find web and all kinds of stuff if you want to use categories on the left i'm going to now click on database and access database on my screen is the second one from the top on the right hand side i can double click it or click it once and then at the

bottom click the connect button it should bring up your working directory and there's only one database file in that directory called northwind i'm going to go ahead and double click it so it's going to go through the process of connecting to the database and it will ultimately open a navigator window similar to the one that we saw when we brought in the excel workbook sample superstore there are differences however databases usually have four different objects in them the tables are the only objects that hold the data but there could also be queries forms and reports

there can be macros and modules as database objects so what it brings in here is it will bring in any queries and notice the icon for query it looks kind of like a double table icon so when you you'll get used to the different icons that you'll see in here but that icon represents a query in the database beneath all the queries you will see tables and that icon looks pretty much like a table so sometimes a database will have temporary tables in them we don't want to bring in the queries we don't want to

bring in the temporary tables i'm going to scroll down and underneath the temporary tables there are seven other tables i'd like you to click the check mark in front of customers and you'll see that the preview is evaluating and you'll be able to see the data that's in that customers table in the northwind database we're going to check the box in front of employees order details we're going to skip orders for a moment check the boxes in front of products shippers and suppliers what's different about this navigator window from the one we saw with excel

is the button in the lower left corner that says select related tables because databases already have table relationships in them and because power bi can automatically detect those relationships if you forget to select a related table power bi has the capacity to do that for you so we're gonna get a demo of this right now actually not a demo you're gonna do it with me so that select related tables button we didn't select the orders table on purpose go ahead and click select related tables and it'll take a moment because it's looking through the relationships

to see what other tables may be related to the ones that we've selected and when it gets done it will automatically select the orders table for us so the northwind database file that we just brought in that we're bringing into power bi already has table relationships in it and power bi is able to detect those relationships my preview is still evaluating but that's fine we're gonna go down and we're gonna use the load button again to bring the data from the database into our power bi desktop so you'll see that it's evaluating all of the

tables that we selected it's creating a connection in the model loading data to the model and you have the yellow band at the top of the screen that was telling you that you had unsaved changes but once it's done with the load process that yellow band disappears if you take a look to the right you'll see the seven tables that we imported in the fields pane let's go ahead and save this file so we're going to go back up to the left to the quick access toolbar first icon is save and we're going to call

it northwind it's still in your working directory and you can see the sample superstore power bi desktop icon so get used to that icon in front of sample superstore that's indicative of a power bi desktop file so i typed in northwind as my file name and i'm going to click save so your title bar at the top of your screen will update with the name of the file because the database that we just brought into power bi has relationships in it we're going to start by looking at model view so on the left side click

your last view button and we'll go into model view so you'll see the relationships between tables here power bi has the ability to auto detect relationships and it is a setting that you can change again we will do a deep dive into relationships once we get to module four but i just wanted you to see mostly that power bi has the ability to detect them when you import a database file or any other file where table relationships have been created i'm going to show you where to find a setting that allows power bi to do

this we're gonna access the file tab on the ribbon and when you get there you're gonna go down to options and settings you will learn more detail about options and settings throughout this course i'm specifically bringing us here now to talk about how to make sure that the ability to auto detect relationships is on or off when you click on options and settings you get an options link and a data source settings link we're going to click on the options gear so what i will say about options in power bi desktop is they come in

two variety there are global options which are applicable to every file that you would have open in power bi desktop and some of the global options are only in that category you also have another option down here another category and it's current file so you have global options and current file options which only apply to the file that you're working in in that moment the option we're looking for is under current file so we're going to go to data load under current file and on the right side of your screen you'll see a relationships heading

under that heading you have three different check boxes two of which are always selected by default the one that controls its ability to bring in relationships upon data load is the first checkbox the third one which is also a default is auto detect new relationships after data is loaded and you'll see an example of when that happens later in this class i normally am in the habit of for every file that i'm working on i also check the middle one to update or delete relationships when refreshing data and you'll learn more about refreshing data later

in the course but for now my advice is to just have all three of those checked for every file that you're going to be working for working on if that is your intent to have power bi help with detecting relationships i'm going to go ahead and click the ok button to get out of options and once we're out of options we're going to go ahead and save this northwind file again by using the save icon on the quick access toolbar and we're going to close the file a couple of ways of doing it i just

usually close the whole window so i'm going to travel all the way to the other side of the screen pass my name and i'm going to click the x button if you attempt to close the file and you haven't saved it it will prompt you to save the changes just like any other application because we did file new sample superstore is still open the file for northwind opened in its own separate window our next task is going to be bringing in data from the internet we're going to go ahead and close the sample superstore file

i'm going to use the x in the upper right hand corner of the window to close it if prompted save the changes there's a word document that you grabbed from the video description called websites and more information and i'm going to bring that document up now before bringing the data into power bi let's follow the link and go to the website so i'm going to hover my mouse over the link hold down my control key click on the link and it will open the website for me so this is just off of wikipedia most websites

are designed with tables on them any website designed this way you can typically bring in the data from the website since we're here there's no need to go back to the word document to get the url i'm going to click at the top of the screen in the address bar and select the url and i'm going to do control c on my keyboard to copy it now i'm going to minimize or even close i'm going to go ahead and close that website and i'm going to get the word document off of my screen since we

closed all of the files in power bi desktop we're going to have to launch it again this time i'm going to launch it because i have it as a button on my taskbar i'm going to click on that button and launch the application so that we can import the data from the wikipedia site into power bi we can do it from the splash screen or we can use the ribbon this time i'm going to go to the upper right hand corner of the splash screen and close it we're going to go back to the get

data button on the ribbon just like we did for the access database and we're going to use it to access web content go ahead and click the button and when the get data dialog box opens on the left side you're going to click the last category other on the right side under other web is the first choice go ahead and double click that it'll bring up the from web dialog box now you want to switch over to the word document that has website and additional information in it and i'm going to bring that up on

my screen right now in this file we have a link for median household income by state 2021 and let's take a look at the information on the internet so i'm going to just hold down my control key and click on the hyperlink it's going to open a web page and you see it has some sort of a visualization on it but most web pages are built using tables so if we scroll down we'll eventually run into a table and that's probably what we're going to want to import into power bi desktop since we're here we

don't have to go back to the word document to get the url we're going to just click up at the top of the screen in the address bar and select the url press ctrl c to copy it and then you're going to switch back over to power bi desktop we're going to paste the url into the box by using control v and then we're going to click the ok button on the right so it flashes on the screen that it's establishing a connection and then you may see a flash of a box that says it

is connecting and when that process is over the navigator window will open this navigator is different than the one we saw for the excel workbook and the one we saw for the access database after clicking ok when you paste it in the url you may be directed to an access web content screen that's asking you for authentication some websites the first time you try to bring in data from their site into power bi it wants to know the authentication level so if you get this box you're going to leave it on anonymous which means there's

no credentials required to view the content on that site when the navigator window displays you'll see the tables that it was able to pull from the site and you'll also see in the preview pane that you have another tab at the top this enables you to see the data as it appears on the web so right next to your table view tab which we're used to seeing you have another tab that says web view as you click on the tables in here you can either see the data in table view or you can see what

it looks like on the internet so we're going to start selecting tables and i want to preview them in table view just to view the data i do not have to check the check box in front of the table i can simply click on the table and see the data so table 1 looks like it has part of the data from this website when i click on table 2 it has more parts of the data and the same goes for table 3. so i'm going to go back and select each table and if you'd like

you can take a look at webview to see how the data displays on that website so we're scrolling down we were just on the site and it looks exactly as it does on the internet so it looks like it's bringing in the information from the table medium household income by state 2021 i'm going to go ahead and do the load button in the lower right hand corner you'll see your load dialog box pop up letting you know it's evaluating the information it pretty much goes through the same process regardless of the data source in most

cases while it's evaluating in the load box you'll notice the yellow band at the top of the screen as you've seen before that will disappear once the data is loaded into power bi desktop you will see the three tables on the right side of your screen in the fields pane let's go ahead and save this file and call it median household income this time i'm going to go to the file tab of the ribbon to perform the save still in my working directory so i'm going to just name it and it defaults again to the

power bi desktop file you can press enter or click on save let's expand the tables that we brought in over on the right in your fields pane and you'll notice some inconsistencies with the data earlier we talked about how important column headings are and that they need to be descriptive of the data that's contained in the columns well table 1 is looking good it has great column headings table 2 and table 3 are both going to need to be fixed let's take a look at the data in data view so again that's the second view

button on your ribbon and we're going to go over there and click it now so we saw this in the preview window that these three tables are really one table on the internet but it came in as three separate tables later on in the course you'll learn how to merge those into one table so you just have one table with all of the data as it is on the internet so far in this getting data from multiple sources lesson we have brought in data from an excel workbook and access database and a website the next

lesson that we're going to be getting into is optimizing performance and you'll see another way during that lesson of how to get data into power bi desktop for right now why don't we go ahead and click on file new so that we launch a new instance of power bi in its own window we'll come back around to the median household income file later and we'll do some data transformation in it so that we can clean up these messy column headings and the other thing that we'll probably want to do is rename the tables table 1

2 and 3 are not very descriptive table names we're going to go ahead and close the splash screen just so we're in the desktop interface before we bring in data from another file i'd like to open a file and review its contents so in the video description there is a customer data excel file i'd like you to go ahead and open the file when you look through the sheet tabs in this file you'll notice that each sheet has a pivot table or a pivot chart on it and the underlying data doesn't appear to be on

a sheet in this file this is an example of a file using a powerpivot data model to capture all the underlying data so the cool thing about this is even if you don't have the powerpivot add-in power bi will be able to read that data and the decision that you're going to need to make in order to optimize performance is basically this one do you want to bring in the whole data set into power bi or do you just want to bring in the underlying data from the pivot tables and pivot chart that's the question

so far we've been connecting to data sources through get data or excel workbook if you connect to a data source it's only going to bring in the data from an excel file that resides in the excel application if you want to bring in the other data you're going to have to import the file into power bi so i have the power pivot add-in and even if you don't it's still fine if you inherit a file with a power pivot data model in it and you don't have power pivot power bi can access that data model

so i'm going to go up to the ribbon and click on power pivot and then the first button is manage which takes me into the data model again if you don't have access to the power pivot add-in you'll be fine when we bring this into power bi what you're seeing right now is the power pivot data model it is the underlying data that's providing subsets of itself for the pivot tables in the excel application you'll notice at the bottom of the screen there are different sheet tabs just like in cell in excel so the data

has been broken up into categories onto different tables and the relationships have been created between those tables as well so we're going to just take a brief look at the data this is the customer's data we have some calculations on the sheet at the bottom and a calculations area you have an order detail sheet where we have some more calculations at the bottom so on and so forth those are the sheets that contain the underlying data that's feeding the pivot tables in the excel application if i want to look at the relationships in here up

on the ribbon i can go to diagram view and this is very similar to model view and power bi which we'll be covering in module 4. so relationships have already been created in the data model for this excel file now i'm going to go ahead and get out of powerpivot i'm going to save my file switch back over to excel and we can all close the excel file now if you've gone into powerpivot make sure that closes as well now that we're back in our untitled power bi desktop file we're going to bring the data

from customer data excel file into power bi but we're going to use two different techniques and you'll see the difference as this plays out the first technique we're going to use is this simply on the home tab we're going to click excel workbook and we're going to double click customer data it'll do its connections and then you'll have your navigator window what i'd like you to notice here is the navigator window these look like sheet tabs so these are the sheets from within the excel file that have the pivot tables and or pivot charts on

them if i click on customers by country it's only showing me the underlying data that's populating that particular pivot table what we're going to do is we're going to select all of the check boxes and we'll bring all of these tables in or sheets in and we'll load so it's not showing the data model at all by using get data or excel workbook it's just showing the stuff that's in the excel application we can go over the data view and look at the data and each of these tables and you'll see it's a small subset

of the greater data set that's in powerpivot we're not going to save this file so i'm going to just do file new and it's going to launch another instance of power bi desktop go ahead and close the splash screen when it opens whenever you use get data you're connecting to the data in order for us to bring in the powerpivot data model into power bi we're going to have to import the data we can do that from the file tab of the ribbon so click on file and you'll see the import option notice the four

different types of imports if we wanted to bring in a power bi template or a power bi visual from another file or a power bi visual from app source we would use the import feature the last choice is what we're going to ultimately use power query power pivot and power view three add-ins to excel even if you don't have those add-ins it doesn't matter if you inherit an excel file with power query power pivot data models or power view reports you can still bring that underlying data into power bi because it's able to recognize it

so this is a power pivot file an excel power pivot file we're going to select that fourth option and it's going to take you to your directory and you're going to double click customer data the screen that shows up can be kind of alarming when you first read it import excel workbook contents we don't work directly with excel workbooks but we know how to extract the useful content so you can work with it in power bi desktop that's great for us we're going to go ahead and click start and it will go through the import

process at some point you'll get the migration completed dialog and if you do the scroll bar on the right you'll see that it brought in seven items which are queries known as queries it brought in seven data model tables it brought in some kpis and measures which are calculations 22 of those and there were no power view sheets in the file so zero items there we're going to go ahead and click on close so when you look on the right side of your screen in your fields pane these table names are mimicking the sheet tabs

in the power pivot data model not the excel workbook if you go to data view you can see the underlying data in all of the tables a much broader data set than what we use to build those pivot tables and pivot charts knowing when to import data versus knowing when to connect to data will optimize your performance in power bi if we didn't import the data model data we would have to take the data tables that we did import from excel which is a subset of the over all data set and we would have to

kind of merge them together to get a complete picture of the data this way we don't have to do that we'll go ahead and save this file and name it customer data before we get into the last lesson of this module let's go ahead and do some lighthouse keeping and close any power bi desktop files that you have open our last and final lesson in this module is resolving data errors there are two errors that i'd like to show you we're going to force one to happen to get started so i'm just on my desktop

and i have my windows explorer set into my working directory where i have the class files for this module what i'm going to have us do is you'll see your sample superstore both the power bi and the excel spreadsheet in the same directory what i'm going to do is i'm going to grab the sample superstore spreadsheet and i'm going to just move it out to my desktop so it's no longer in the same directory while the directory is still open i'm going to double click my sample superstore power bi file to launch the application and

open that file so right now when we open the file we're not seeing any errors we still have our tables in the fields pane you can go to data view and still see the data in the table so when you connect to data it connects to the location of the data as well so you can be working in the file and it appears that everything is fine until you do one of two things i'm going to go back to the home tab here and in the queries group i'm going to click on the refresh button

so all of a sudden i get errors and it's telling me that it can't find the data source file the actual excel file so when we move that file out of the working directory power bi does not update a change like that so right now we're kind of stuck we wouldn't be able to get much done on this file without resolving that error so the way to resolve the error is like this we're going to close that refresh box and then we're going to go to the file tab and we're going to click on options

and settings so we were in options earlier when we looked at the check boxes that enable power bi to be able to load relationships in to itself when bringing them in from a database file or in our case a power pivot file as well but we're going to click on data source settings this time so when the data source setting dialogs box opens you'll notice that it's pointing to the path that it was originally in on your computer before we moved it out into the desktop that stays with the power bi file so in this

situation we have to tell it where the actual source data file resides the sample superstore excel file doesn't reside in that path anymore what you're going to do this time is in the lower left hand corner you're going to select the change source button and when we click on that button it opens an excel workbook and this is particular to excel right at this point we're only dealing with that excel file and notice that it has defaulting to excel workbook in open file as you have other file types down there so depending on what kind

of file it is it normally will select the right type but always verify that it's the right one selected so where it has that path i'm going to click on browse and i'm going to browse to where i put my sample superstore file which is just on my desktop now and i'm going to double click it so once it has the path it will be able to continue now go ahead and click ok and it updates on the data source settings dialog so you can send a power bi desktop file that you create to someone

else you can email it you can get it to them but if you do that and you're the one that created the file also send them the source data file because otherwise they'll be limited and what they can do in power bi it will let them load the data but as soon as they refresh or do a few other things you'll start seeing the errors we're going to close data source settings and it tells me there are pending changes in my queries that haven't been applied that yellow band at the top in this case this

is different from loading data we want to tell it to apply the changes that we just made so i'm going to click on the apply changes button and it's kind of actually reloading the data into the file so when you open a power bi desktop file it loads the last data set that it had in it but if the data source has been changed to a different location and you point to it it's going to have to reload the data if you'd already progressed to the point where you made visualizations and report view and things

of that nature they would all update with the reloaded data so let's see if we got rid of this error on the home tab we're going to click the refresh button again and we shouldn't get the errors this time because it knows the path to the data source at this point if we move the excel file back into our working directory we would have to update our data source again so we're going to do that right now i've minimized my power bi desktop and i'm going to grab my sample superstore excel file and drag it

back into my class file working directory folder and then i'm going to bring my power bi desktop back up i am going to click the refresh button on the home tab of the ribbon and we get the same error some other errors might say load this one is saying refresh because we click the refresh button i'm going to close that error i'm going to go to the file tab and back to options and settings data source settings and at the bottom i'm going to click the change source button and use the browse button to get

back to my class files working directory and double-click sample superstore i'm going to ok and close and remember you're going to have to apply the changes in the yellow band at the top of your screen so it reloads the data in from the path that you just pointed it to now when i click on refresh i won't get the error and we will definitely be doing a deeper dive into refresh in just a little bit another type of load error that you may get is when you import a file that has power view sheets power

view is an excel add-in and it allows you to create visualizations in an excel file this is very similar to when we imported the power pivot excel file even if you don't have power pivot or power view power bi is able to read that data so we do have a power view file called it spending analysis in the video description and it it's an excel file with the power view sheets in it i'm going to take a moment to open that file i don't have power view so you'll see what it looks like when you

open it and you don't have power view i pulled this file from the microsoft site they have several sample files out there that you can grab and use in power bi some of them this one maybe when you see the visualizations in power bi you might get some ideas for those same types of things on your data so i don't have power view like i mentioned and this file contains multiple sheets just three of them that have the framework for the power view report that i can't see without having to add in i'm going to

go ahead and close this file and then we'll switch back over to power bi in power bi i want to go to file new don't want to bring that data from [Music] the power view reports file into sample super store data set so we're launching a new instance we've done this before i'm going to go ahead and close the splash screen because we can't use get data to import this data and we're gonna go to file import we're gonna select the fourth choice like we did before power query power pivot power view and you're gonna

double click on that it spend analysis sample file we get the same import excel workbook contents dialog and we're going to click start so the migration is completed there are nine queries that were imported nine data model tables and 15 kpis and measures as well as three power view sheets the ones that we saw in the excel interface i'm going to go ahead and close this is the error that i'm referencing so sometimes when you bring in something from power view power be i may not support that visualization anymore as shown here it either has

a visualization and power view that is not yet supported in power bi or is no longer supported in power bi so we haven't gotten to the chapter where you're going to be creating reports and putting visualizations on the report pages but this is another pretty common era so the fix for it even though you don't know yet through watching these videos how to create these you can learn how to get rid of them before you learn how to create them so i'm going to click on the framework of where visualization would be where the error

message is and i'm going to simply press delete on my keyboard and once you know how to build visualizations you can replace it with something that is supported within the power bi the last thing we're going to go over in this module are the implications of where your excel source data files are stored and their performance in power bi if your excel files are stored locally on your computer versus in the cloud via onedrive or sharepoint power bi will treat those as different source files when it comes to refreshing the source data i've made a

copy of the sample superstore desktop file and renamed it sample superstore local just to make a distinction between my locally stored excel file and the one that will get to put into power bi that's stored on one drive if you have access to a onedrive for business account go ahead and upload the sample superstore excel file that we used earlier right now i'd like to point out a few things so that you can see the impact when we refresh the data from a local excel file the first thing i'd like to point out since we're

in data view is i'd like you to take a look at the top two sales figures in the orders table so the first one is 8617 and the second is 1527. i'd also like to point out that both of these orders are from a city in utah named kearns now in this file i've already set up one small visualization and i'm going to show you what it looks like by going to report view you'll learn how to create report visualizations when we get to module 7. but right now as i hover my mouse over the

bar on the report it shows me the sales are 2 428 and 25 cents for the city of kearns i've opened the sample superstore excel file that's stored locally on my computer and i'm going to change the values of those two cells that we saw in power bi desktop i'm going to change the 8618 sale to 386.17 and i'm going to change the 1527 sale to 215.27 and then i'm going to save the file i've switched back over to the power bi desktop file and as you'll notice on my screen even though i made the

change in the excel source file it hasn't updated those sales values as of yet the reason why is i have to tell it to the way i tell it to do it is by going to the home tab of the ribbon and almost in the center of the home tab there's a refresh button so notice when i hover over that button it says get the latest data by refreshing all visuals in this report i'm going to click the button to refresh you'll notice that it has the pop-up on the window and when it's done and

i go back over and i look at those sales figures they have indeed updated to what i put in the locally stored excel file now that is in the power bi desktop if you make a change to a locally stored file excel file in particular and you refresh in power bi desktop it will update the information but something different occurs in the power bi online service and i'm going to show you that now i've already published that sample superstore local file to the power bi service and it published both the report and the data set

so when i come into the service and i look at the report it looks exactly as it does in the desktop with the same sales value of 2428.25 when i hover over the data set i see a refresh button refresh now i'm going to click that button and then i'm going to go back to the report the report is the same same sales value i can also refresh at the report level by clicking this refresh button over here again nothing changes this is because the excel file is stored locally and the online service cannot refresh

from a locally stored excel file at this point if i wanted to get the updated data set into the cloud in power bi service i would have to republish it from the desktop now we're going to bring in an excel file that's stored in a onedrive for business account it's the same excel file as the sample superstore file we've worked with previously i've just renamed it dash od to represent onedrive the tricky wicket with bringing in something from sharepoint or onedrive is you can't just copy the link that's up here you actually have to open

the file in the excel desktop application so i'm going to go to the more actions button hover over open and choose open an app this is the only way to get a link that you can use to bring it into power bi desktop just like you would bring in a web file once i have it opened i can then go to the file tab and click on info and i'll see the copy path button that's the way you have to get the path to the file where its location is in one drive i'm going to

click on copy path and then i'm going to just close this file i don't need to be on one drive in this moment so i'm going to switch back over to power bi desktop because i want to bring this into a new instance we're going to go back to file new and launch a new instance of power bi desktop so we can just keep these separate local versus one drive i'm going to access get data from the splash screen and when the get data dialog box opens on the left side i'm going to click on

other and then double click web at the top of the list so the path is on our clipboard i'm going to do control v to paste it in and the second tricky wicket you're going to run into here is at the end of the path you'll have a question mark web equals one you need to delete that part of the path or this will not work so i've deleted it and i'm going to click ok the navigator window will open it's going to be the same three tables orders returns and users that we used before

so i am going to put a check mark in front of each one and click the load button the normal evaluation process will take place you'll have your yellow band at the top it's loading the data to the model and if i look over to the right in my fields pane i have the same three tables i'm going to switch to data view and just as a navigational point i'm going to expand the orders table and click on the sales field and you'll notice that those top two sales values are the same as in the

original data before we made any change in the onedrive file we hadn't made any changes to the data so you have your 8617 and 1527 in terms of sales we're going to go to report view and i'm gonna show you how to build the report the same one that i had in the locally stored desktop file so what i'm gonna have you do is over to your right in the visualizations pane you're going to click on that very first visualization it is a stacked bar chart and then over to the right in your fields list

you're going to expand your orders table and i'm going to just drag the cities field over to the framework of the visualization and again you'll go into deeper detail on how to design reports in module 7. and then i'm going to go and grab the sales field and drag it into the framework of the visualization as well the last thing i have to do is filter it for just that one city which was cairns so in my filters pane where it says city is all i'm going to do the drop down arrow and i'm going

to just in the search box i'm going to type kearns kea r n s and i'll just check the box when it comes up so there are eight sales that happened in the city of kearns and that's how i developed the visualization you saw in the other file if you point to the bar it will tell you the sales value for that city is two thousand four hundred and twenty eight dollars and twenty five cents just like in our regular file i'm gonna save this file and i'm gonna call it sample super store dash od

for onedrive now that we've saved our file we're going to publish it to the power bi service the last icon on the home tab at a ribbon is publish go ahead and click it it will ask you sometimes if you want to save your changes even if you've saved sometimes that prompt will come up not always and then you'll get a box that says publish to power bi select a destination i have multiple workspaces the one that we all have in common is my workspace and i'm going to go ahead and put this in my

power bi video workspace but feel free to put it in your workspace we'll do a deeper dive into publishing and what workspaces are and how to use them when we get to module eight so for right now select your workspace and it will let you know that it's publishing and it lets you know when it has successfully published we could get to the power bi service from this box but for right now we're going to just click got it now at this point we want to make a change in the excel file that's stored on

one drive so i'm going to go back over to onedrive and i'll need to open the file in the excel desktop app again so i'm going to hover over open and open an app we're going to make the same change in this file the same two changes that we made in the other file so i'm in cell w 330 and i'm going to change that to 3 86 17 and i'm going to go to cell w 332 and change that to 215 27 and then i'm going to save now at this point it is uploading

and if you attempt to do the x to close the file you'll get a message on your screen that it's still uploading i'm going to wait till it's totally saved and then proceed so at this point i'm going to go back over to power bi desktop once i'm in the desktop i can go ahead and refresh this power bi file by clicking the refresh button on the home tab of the ribbon when i refresh it's gonna go through the dialog boxes where it's evaluating it's looking at all of the data and when i hover over

the bar on the chart i see the sales value for that city has gone up by five hundred dollars it's now two thousand nine hundred and twenty eight dollars and twenty five cents if i go to data view i will see that those sales values updated as well and this is the best part it will also update in the service since the excel file is stored in the cloud on one drive another way to get to the service is by going up to your login information in the upper right hand corner and clicking on power

bi service now when you get there over on the left side the next to the last button is workspaces and i'm going to just navigate to my workspace where i saved and published this data once i'm there i'll see that i have both the one drive report and data set and the locally stored report and data set for the onedrive data set i'm going to go ahead and click the refresh now button and then i'm going to click on the link to take me to the report when i get to the report it's now updated

to 2928 so if a file is locally stored it will update in power bi desktop when you refresh it but it will not update in the service and you would have to republish it to the service in conclusion for module 2 getting data into power bi you brought in data from multiple sources you learned about the implication of locally stored versus cloud stored excel files how to import from power pivot and power view excel files versus using get data the difference between a data source and a data set you learned how to import from an

access database and from a website we went over a few performance optimization issues and you'll learn more throughout the rest of the course and you learned about common load errors and how to resolve them the next module module 3 is about cleaning and transforming your data in power bi thanks for watching don't forget we also offer live classes and office applications professional development and private training visit learned.com for more details please remember to like and subscribe and let us know your thoughts in the comments thank you for choosing learn it [Music]