Introduction to Power BI Desktop

hello I'm Dustin Ryan and I'm a Microsoft bi consultant with pragmatic works and an all-around regular dude I just wanted to take a few minutes to walk you through the new power bi desktop application that Microsoft released this past week it's pretty cool and it does a lot of really neat things and thought it'd be worthwhile sharing with you so if you imagine that in some weird universe powerpivot power query and Power View had a baby you'd end up with the power bi desktop application and the power bi desktop application is Microsoft's new end in analytics tool so it allows you to connect to all kinds of really interesting data sources traditional data sources non-traditional data sources load it up into a almost like a PowerPivot workbook apply transformations to the data and then build all kinds of really cool reports and dashboards on top of the data and so if you are interested in doing this kind of thing you can just go to power bi comm you'll end up on this website here you just go up to products go to power bi desktop it will take you to the downloads page and then all you have to do is click the download button here and you'll go download the tool and play along for free now when you download it you're initially just the free user like I am here if you purchase the year-long subscription it's $9.99 a month here on power bi coms website and you can see there's some differences between the free free version and the power bi pro version obviously data capacity limits one of them and there's a few other things that I won't go through here but I wanted to point that out to you here and so let's get started so I'm going to open up the power bi desktop app here got it down here tend to my taskbar and so let's just take a few moments to open up here somebody's tweeting me okay and so what you'll see when you open this up the very first time is the power bi desktop welcome screen here I've got a lot of free a lot of cool things here that you can watch if you want to watch them videos on building reports querying concepts uploading reports all kinds of different things here got lots of helpful videos here but you don't need that because you're watching this video obviously I'm just kidding this lots of really good material out there you should go check it out you can click the get Data button here this is going to be the first thing that you'll do in order to start connecting to data sources so we can load some data up into our model so I'm going to click the get Data button and we'll see the get data screen here and we've got all kinds of really neat data sources that we can connect to so we've got Excel CSV files Tech 5 text files sequel server databases access everybody's favorite database technology Sybase you know you can scrape data off web pages as your if you've got a sequel server databases up in Azure you can connect to azure marketplace Microsoft Exchange Act of to wreak Wyck books these are some new data sources that they just added in the in the most recent release Salesforce Google I know there's a lot some really cool stuff out here I don't have access to all those really cool data sources right at this moment but I do have some data in a CSV file that I'm going to upload into here so I'm going to select the CSV option and I'm going to click connect ok and I'm just going to navigate to where I've got some CSV files that we're going to use and I've got I've downloaded some really interesting movie data here that I pulled up from this website called opus datacom you can sign up for a free trial account and download some really interesting data that has to do with them movies and their performance in the box office and production amounts and all kinds of different things it's really neat to look at in a dashboard so I figured I'd download that so we could play around with it but you get that from opus datacom pretty cool so I've got my movie data CSV file here so I'm going to select that and click open okay and so what it's doing actually right here is is behind the scenes it's connecting to the CSV file and it's actually loading it up here into getting it what's getting preview over here and we're going to load it up into our data model and behind the scenes this is a version of the PowerPivot analysis services engine so it's loading it up into our basically like a powerpivot model behind the scenes so I'm going to click the load button here and it's going to create a connection to the model and load up our data into the model and it's completed here so maximize this here and so when you first load up your data you're going to see this kind of this blank screen here and there's three basic views that I want to point you towards you've got the report view over here oops got the report view over here on the left this is where we'll actually create our dashboards we'll create different types of charts we want to have in our dashboard then you've also got the data view this is where you can kind of look at the data similar to how you could look at it in PowerPivot you could look at it in the indeed in the table view and then you've also got the relationship for you here where you can look at the relationships between the different objects that you have in your model and so I'll just kind of show you what that looks like if you go here to the data view you can see kind of a preview of what the data looks like which is pretty cool kind of looks like PowerPivot and then you've also got the relationships view so of course not any relationships here to check out because we only have the one object in here but if we did have more than one object you would be able to see the relationships between those objects now but I'm going to go back over here to the data view of our model because I want to point out a few things here that you can do to model your your tables here so this is where you're going to do things like change column names change data types you know control sorting if you want to delete columns from the model or hide them from the report view this is where you're going to be able to do all of that this is also where you're going to where you're going to come if you want to create a new calculated column like you could empower pivot or if you want to create a new calculate measure and this is we're going to come to do all that so first thing I'm going to do here is I'm just going to come in here and rename some of the columns in here so this is the display name of the movies so we want to make this as user-friendly as possible because everything all the naming conventions here all the data formats all the data types those are all going to be surfaced to the users once we start building our dashboards and everything so we want this to be as as friendly an experience as possible for our users so I'm going to make sure that I set up all of the formatting here correctly all of the naming conventions I'm going to hide any columns that they shouldn't see I'm going to delete any columns from the model that aren't necessary and that's one best practice that I'll point out here you want to delete columns from your model and not include them in the model if they're not necessary because those are going to contribute to the size of the model is going to take up more system resources during processing of the model and loading the model with data and interacting with it so anything that's not absolutely necessary in the model we want to make sure we remove those things so things like I don't know let's see something like sequel right if either this is the sequel flag one if it was a sequel 0 if it was not a sequel running time you know maybe my users don't really care about the running time of the film so I would come in here and right-click on that field and delete it because we don't we want to make sure that we don't include anything unnecessary in the model now this is my key column on the table not exactly a useful value for our user so I would want to hide this from the user so I'm going to right-click on this column and select hide in report for you so it's still a part of our model we can still use this in calculations in and building relationships between this and other objects so I'm going to hide this from our users because we still want to be part of the table and you can also see that after I've hidden it from the users it's now grayed out so indicating that this is hidden in the report for you all right production budget maybe I come in here and rename this we call this production budget amount and I'm going to leave that column highlighted will name this domestic we got our box-office amount here domestic box-office amount so I'm going to give that a friendly here too because we're all about having a good experience for our end users so international box let's give this give our write names here so that way it looks nice in our reports forgot the call that box office and I won't go through and rename all of these here but I did want to go through and rename some of the more important ones that we're going to use in our report so that way it looks as as nice as possible in the actual dashboards we're going to build here in a few minutes okay one other thing that I'm going to do here as well as I'm going to select the formatting correctly so these are all dollar amounts so I'm just going to highlight the column format this as a currency so that way when we use our measures are our calculations and our measures in the reports that they show up and they look nice and neat with little dollar signs the commas and things like that so we want to make sure we set our formatting here correctly and you'll notice here that every time I make a change there's a little bit of a delay before you see the change show up and that's because it's actually making the changes to the model on the fly for us as we're as we're modeling here alright so that looks good I don't think I want to do anything else here to the model I'm not going to spend too much time doing this because if we did that it would take too long but I just wanted to kind of show you the gist of some of the changes here some of the modifications here that you can make to the model one other thing that I'll also point out here is you've got this field up here at the top called data category this is where you can set categories for your fields so if I have like some kind of geographical data in here like if a latitude longitude or zip code data or States or province I could come in here and set this as the right category so that way power bi would know how to map that to our map report so when we start building any types of maps the data will show up correctly in the map um so I didn't want to point that out to you as well but I think that's pretty much it okay so we've done a little bit of formatting here and if I was building this model for a customer I'd go through and make sure this was all formatted correctly but I won't bore you with doing all of that if I want to bring in some other data into our model I can come up here to the get Data button on the home ribbon and select the model that I select the data source that I want to access so I'm going to pull in some data from another CSV file here and I've got some date data here that we're going to use kind of our date dimension so I'm going to select the date table and click open and in a few seconds here to give me a little preview of what the date looks data looks like that looks good so I'm going to click the load button here if I wanted to make some modifications here to this query I could click this edit button here and this is where things will get very powered query like so if you're familiar with power query this is going to look at really familiar to you we can come in here and we can do things like remove columns we can remove rows we can apply different types of transformations if we have a a row that should be our header row we can set that up here we can merge queries together we can apply all kinds of transformations here I'm not going to go through all of this here but this is where you can actually make modifications to your query if you want to pivot or n pivot data apply different types of date functions all kinds of different things here I don't really have any modifications here to make to our model to this date table except maybe I'll just go ahead and remove the month column here since our movie data is at the Year level I don't really need the month column in here so I can just right click on here and select where is it remove yeah I was looking for delete and I can remove that column here and once I've finished making all the different types of transformations to this query I can just click the close and load button up here and that will close the query editor here and it will reload that table now all of the different tables that are a part of our model you'll see those over here on the right side in the field section so the is our movie data table and then I've also got the date table here okay so I'm going to hide my date key here from the report view because it's not really useful for our users and this is our year so I'm going to rename this with a capital y keeping this user friendly okay so we've got our two our two tables here now if you import tables from like a database that if you import multiple tables at one time from a database power bi desktop we'll try to create the relationships based on the names of the columns the data types things like that but if it's not sure it will it will automatically create those relationships for you but if it doesn't at least have a high level of certainty that that is the correct relationship it won't create the relationship for you so chances are you're probably going to have to create the relationships yourself to do that at this point you can't drag and drop and create the relationships that way you're going to have to come up here to the home ribbon and click on the manage relationships button okay and you'll see that there's no relationships in here I could click the auto detect button here and it will try to figure that out for me or I could just click the new button here okay now this is where we can create our relationship manually so I'm going to select date table up here at the top and all I have to do is to create the relationship between these two tables is just select the columns that I want to use in the relationship so for the date key here in the date table I'm going to select date key and in the movie data table I'm going to select the production year date and in the Advanced Options here you can see that we have an option here for the cardinality is this a minute to one relationship a one-to-one or one-to-many you can see it's correctly identified this as a one-to-many relationship if I were to choose an incorrect cardinality option here one to one let me move this up a little bit you'll see here that it's going to yell at me and tell you hey this isn't valid for this relationship because it's identified that this is actually a one-to-many so it's going to tell you hey you can't do that so I'm going to change this back to one-to-many and you can have the option here for the cross filtering direction I want this to fill - both ways not just in one direction so I'm going to select both here and this is probably the option that you're going to use 90% of the time so I'm going to click OK here and there's our relationship here so if I need to come back in here and edit it I can do that too but that looks good so I'm going to click close and you'll see here that it's created the relationship for us and we can also notice here that we've got these little eye little indicators here indicating that this is a one-to-many type relationship here so that looks good if I wanted to make it edit to this relationship I can double click on the relationship between the two tables here and it will open the edit button so I can make a change here if I needed to but it looks good so I'm going to move on here okay so we've looked at how we can import our data we've looked at how we can model our data a little bit by hiding columns and renaming columns and deleting columns and formatting data types and and all those different things that we can do there we've looked at creating relationships between items now let's take a look at creating some calculations now if I want to go to create calculations in my model I'm going to go back to the data view here and I'm going to navigate over to my movie data table here I'm going to rename this table here first because it's just called date table and that's kind of bugging me so I'm just going to call it date now let's go back over here to the movie data table and I want to create some new calculated measures we're going to write a little bit of Dax and by no means at my attacks master so I'm going to try and struggle through this and to create a new calculated measure you just go over here to the modeling button or the modeling ribbon you just click the new measure button here if you're familiar with PowerPivot you'll recognize this as where you can create your calculated columns calculated measures things like that so we can do the same thing here so I want to create a new calculated measure that call that is called total box office sales so I'm just going to type total box office sales use the equal sign here and to come up with a total box office sales I want to sum the domestic box office amount and if you're new to Dax you'll notice here that the intellisense is very good it'll almost write the whole expression for you call this domestic box-office amount plus the sum of the international box office amount close parentheses and I'll hit enter here okay you'll see here that I've got a new measure down here in the in the fields list here now just point out a couple things here you notice here that the I've got these fields that have the Sigma icon next to them then I've also got these fields here that have the calculator icon icon here now the difference between these two is that this is an implicit measure this is a a field that the model or that the power bi app has determined is a is a number field and it could be aggregated and so that's why it's showing us a little Sigma icon there and we can use those in our reports and we can specify in the reports whether we want to sum and count them do anything like that min or max I'm average whatever the case may be these are implicit measures and the user has the ability to control how they want to aggregate those the measures down here these are explicit measures these are calculated measures where we're explicitly specifying how we want these to aggregate so if you have a measure that you want to have control over how it a great sin instead of leaving it up to the users you want to create an explicit measure like we've done here and so that's kind of the difference between the two here okay so I've got my total box off the sales amount now let's create another calculated measure here now one other thing I'll do here is I want to control the formatting of this so I'm going to highlight this measure here and click the dollar icon here to change this to format as a currency okay so we want this to show up formatted nice and neat as a currency all right I'm going to create a new measure here and what we're going to call this our previous year total box office sales okay and so we want to calculate our total box office sales for the previous year and I've got a date table so I'll be able to use some of the powerful daksa date calculation functions so we'll use our total box office sales amount and to filter this for the previous year I'm going to use the date add function okay and my date key is actually date date key from our date table and I want to go back one year and so the third argument in the date add function is the year and so that looks about what we want to think I got that right and so I'll hit enter there and we'll be able to test these in the report to make sure that we've got them correctly and so we may have to come back and troubleshoot but I think that looks correct if I had some kind of syntax error here it would tell me so if I have like an extra parenthesis see that I get the little red squiggly there I'd do something like that you know it'll tell you or if I write a function that doesn't work you know to give you a little warning and kind of tell you what the problem is but I think I've got this right so I got my previous year total box office sales amount where I'm getting my total box office sales amount and filtering it using the date add function to get one year back from the current date key okay so that looks good and I'm going to format this correctly as well and a couple other calculated measures I want to use I'm going to create our our total box office sales growth so we'll create calculate our growth which is really just going to be our previous year sales actually we'll do this it's the total sales minus the previous year sales okay - the previous year sale stuff so we figure out what is our growth look like okay so that looks good we got our total box office sales minus the previous year and I'll hit enter there and we want to format this and I'm going to click away and highlight it again so I get access to the formatting here and we're going to format this here as a currency okay great so we've created some calculated columns in here or calculated measures rather okay so I think now that we're ready to start the fun part and that's where we're going to create our reports so to go to start creating your dashboards and reports we're going to go back to the report of view over here which you can see right there I'm going to go over here to the report view and if you're familiar with Power View this is going to be pretty intuitive although I think initially when I looked at it I thought Power View was a little more intuitive but maybe that's because I was I was more familiar with Power View and less familiar with this obviously this is a new tool kind of played with a whole lot so I'm still kind of getting familiar with it but it's still very very easy to work with so if I want to start creating reports all I have to do is just select a field that I want to add to the report so for instance I'm going to use one of the new report types that we have in this release the combo chart so I'm going to click this here and right now I don't have any fields that are a part of the chart so that's why it's kind of all gray and ugly looking but let's put the total box office sales amount here let me expand this a little so I can see everything so people put total box office sales so I'm just going to select that and you can see that we've got a pretty little column chart here with the box office sales total box office sales and we could put the growth over here to side-by-side but of course we don't have a year on here so that we have no way to calculate the growth so we need to add the year so I'm going to go out to my date table I'm going to get year and we're going to put the dates here on the columns I'm sorry noted on the shared axis here okay so now we can kind of see what our what our total box office sales for each year looks like and we've got our total box office sales growth our total box office sales growth amount here now if I wanted to move my box office sales down onto the line values I could do that could drop it down here and display that as a line pretty neat can do it that way if I want to if I want to change chart types maybe I decide you know what I don't really like the combo chart here I just want to go to a straight regular bar chart or column chart rather can click that switch back over here to the clustered column chart or if you want to change it to the the sideways bar chart you can click that too and and change it this way so you've got some options there so I'm going to get rid of mine let's go with the clustered bar chart here so I'm going to get rid of my growth measure here I'm going to add the previous year here drop that right into here you can do that if you want to get rid of that let's put the production budget amount there on the values too so I can look at the budgeted the production budget amount side-by-side with my total box office sales okay so I'm just going to resize this a little bit all right looking good I'm going to move this over here to the right okay format this a little bit now one of the really cool things that you give the latest version of the power bi desktop app is you have the ability to apply all kinds of formatting to this so you can control the colors you control the titles and background and all kinds of different things so if I want to change any type of formatting with this chart I'm going to make sure I've got the chart selected like I do here and I'm going to click on the little paintbrush icon here that's where we can control the formatting so if I want to change the position of the legend if I want to move it down to the bottom I can do that if I want to change anything about the y-axis you can do that as well x-axis data colors this is the cool part where we can change the colors of our chart so maybe for total box office sales I want to change this to this pink color here can do that and then for our production budget can change that here to the to the yellow yellow color or let's go with the let's do the green the greens pretty cool so can do that well maybe that's not such a good choice for our color Brian colorblind friends so let's do the the pink and the yellow or the blue rather you can also select a custom color so if you don't if you decide you don't like any of that maybe want to go with the pink in the blue or you want to pick like an orange color or green or you know whatever you want to do you can pick pretty much any color that you can think of there okay so let's do the the blue color that we had here earlier so that looks good okay now if I want to add another report or another chart to this dashboard here all I have to do is just kind of click away from this chart and let's let's add one of the new chart types let's let's add the funnel in here so I want to click the funnel chart and on here we want to look at our budget our production budgets by genre so I'm going to select production budget amount I'm going to click on budget I'm sorry genre you see here it's building the chart for me so not a lot of work required there to get it correct to get the charts built just a couple of clicks if I want to change the sorting of this you can see right now it's sorted alphabetically by genre maybe I want to sort it by my production budget amount I can click on the little ellipsis up here at the top right of the chart area and select the sort by option and select production budget amount okay pretty cool all right so I'm going to move this over here kind of slide this around a little bit format it a little bit so that looks good we can see our budget amounts by genre so those adventure films man they're expensive all right so we've got our funnel chart here we've got our clustered bar chart here let's add another new chart type let's use the tree map so the tree map is this guy here it's basically think of it as it's sort of like a pie chart kind of a souped up pie chart but I think it's a little probably a little more useful than a pie chart because you can see the different areas a little differently or a little better in my opinion so on the tree map let's put our total box office sales and we want to look at it by genre as well okay so we've got our total box office sales by genre down here on the bottom we've got our production budget amount by genre and then we've got our our box office sales compared to the budget amounts by at the year level okay so that looks pretty cool now one of the really neat things that you get here that you know you're familiar with this if you are used to power view is you have the cross filtering action so if I want to filter all of our charts by genre I can just come down here to the tree map and you know say hey let's look at the what are our metrics look like by for our drama movies or dramatic movies our action movies adventure movies whatever so you've got this cross filtering functionality built in here natively so pretty cool all right got a couple other things here that I want to add let's add a slicer in here for our our movie types so I'm going to click away from any of the other charts I'm going to click the slicer visualization here to drop this on here let's shrink this down a little bit to fit into our report area and I'm going to go to the date fields over here and select a year okay so we want to slice this by year there we go drop it in there was let me click on it for some reason okay so we've got our slicer over here now so now I can filter the whole report by year so I could do something like that I have the different years in here so pretty cool now we've got a built in slicer here so dashboard is coming along nicely starting to dig it let's add a couple other things in here let's add a text box for our report type or for our report title we'll call this our movie dashboard click in here so I can type it movie dashboard can format our text a little bit bump up the size here give us a dramatic looking font here there we go looking good and our movie dashboard almost complete here see got a image I want to use here there's our little movie reel icon so let's shrink this down here to put in our dashboard move some things around here great okay cool so we've got this really awesome dashboard that we built looks awesome now if I wanted to develop other dashboards to be a part of this or other reports to be a part of this dashboard set I could click the little plus button here to add other pages here don't really have anything else to build at this point so I'm going to click on this to rename it come on let me double click it there we go and we'll call this our movie dashboard here all right looks great I'm going to save this there's our movie dashboard create another one earlier so I'm just going to call this one movie dashboard - okay and so now we've created our our first power bi desktop report and now we're ready to deploy this or publish this to our power bi site now I'm going to click the publish button here and for you to be able to create the order to publish the power bi dashboard you're going to need to sign in to your power bi account which I have one so I'm going to click the sign in button here have one with pragmatic works this is my work account so I'm going to log in here to our power bi site and so we can publish this puppy okay we are publishing now and this will just take a few moments alright great success alright our power bi desktop report is now deployed so let's open this so we can view it in our on the power bi site that's just the pragmatic work power bi kind of sandbox here so you can see that I've deployed a few of these movie dashboards out here but now we actually have access to our power bi report out here so I can interact with it just like I did before I can use my slicers I can filter you can do all kinds of really cool things out here um in our report okay so that's pretty much it so we looked at how we can use the power bi desktop app to import data into our model now we can model it a little bit apply formatting edit our query and then we've looked at how we can create calculations and then use the use all of our our model in a dashboard type report I hope you found this little video beneficial I hope you learned a little bit if you did click the like button or share it with a friend or you can follow me on Twitter and send me a message on Twitter tell me that you thought it was useful or or give me whatever feedback you've got appreciate any feedback from you guys but you can follow me on twitter at sequel dusty and if you're interested in this type of thing and like to get other updates from me you can also check me out on my blog sequel dusty comm where i blog about all kinds of things power bi SSAS and just basically anything microsoft bi related you can come check out here on sequel dusty calm so thank you for tuning in and I hope you learned a little bit and I'll talk to you later

Channel: DG

Views: 377,042

Keywords: power bi, microsoft, power bi desktop, Power Pivot, Business Intelligence (Industry), msbi, powerbi

Id: TMMOByVrgzY

Length: 34min 0sec (2040 seconds)

Published: Sun Jul 26 2015