How to build Power BI Dashboards - FREE Download

[Music] [Music] thank you for joining me today I'm in the Tracy and I'll be taking you through an action-packed session where I build not one but two dashboards using power bi this time I'm wearing my power bi t-shirt which you can get from Microsoft's online store in this webinar I'm going to use the free power bi desktop tool to get some data from an excel file using power query which is built into power bi desktop then I'll use the power pivot modeling tools which are also built into power bi desktop to create some additional columns and calculations before creating the dashboards that you see behind me now we've got a huge amount to cover so the pace will be too fast for you to take notes instead I'm going to email you after the webinar ends with a link where you can download the video recording of the webinar and the power bi desktop file and Excel workbook containing the data so you can give it a go yourself ok let's crack on because we've got a lot of material to get through so get comfy first of all let's take a look at the data we'll be working with here I've got some sales data for two retail chains called ready wear and belling's these chains are based in Australia and the data spans from January 2016 through to July 2017 on their dim tables sheet we have some look-up tables that will use to group our sales data for example in Australia our financial year runs from July through to June so I've classified each date into its financial year and its financial quarter and financial year month I've also got more information on post codes so that we can see what suburb and state they relate to and I've got a table that tells us the buyer for each product category and the manager for each suburb now in Excel we'd be inclined to flatten all of this data into one big table so we could analyze it in pivot tables or using formulas and we'd probably use vlookup to add columns to this fact table but in power bi we can use the power pivot modeling tools to create relationships between these separate tables and then we can analyze and visualize all of this data in the one model in power bi and this is a much more efficient way to store and work with this data now I should also point out that I've formatted all of my data into Excel tables and I've given them each a name that's meaningful and this will help us when we bring the data into power bi will be able to easily identify the different tables based on their names so now that you're familiar with the data let's bring it in to power bi desktop so here I've got an empty power bi desktop file by the way the file extension for power bi desktop is docked p bi X and on the Home tab in the external data group we've got our power query tools and this is the same power query tool you might be familiar with from Excel except in power bi the menu buttons have been collapsed down into just a few now I'm going to click on get data and notice that the first option is Excel which is what I want but while we're here I'm going to click on more so I can show you that there's actually a lot more sources we can connect to and get data from so it got all our different file types we can connect to databases as you're loads of online services and then we've got some other services like the web and SharePoint etc you can even build power bi reports using data from multiple sources and they're adding more connectors all the time so I'm going to go back to the file group and I'll choose Excel and then connect now we need to browse to our file and there it is there so click Pimpin now the navigator gives me a list of the tables and sheets in this file and if I had filtered lists or named ranges or even print ranges then they'd be listed here too remember I formatted all of my tables into Excel tables and we can see them denoted by this table icon so I'm going to select each table and you see on the right I'll get a preview we can tell you a closer look at the different data tables before we load them and if we're happy we can just click load and that will load it into the PowerPivot model in power bi desktop I'm going to edit them first and we can also take it closer look there this happens the power window I'll just bring it into view and you can see on the Left we've got our five queries one for each of our tables that we've brought in from excel and if you've used power query in Excel you'll be familiar with this view looks a little bit different here just because of the dark theme but otherwise everything is pretty much the same the only other difference is this close and apply in Excel we have close and load in power bi we have close and apply but essentially it's the same button now the first thing I want to do is make sure that all the data types are correctly set we can tell what the data type is because each column has this icon in the top left so this is a calendar icon for date we've got text whole numbers and decimal numbers here so they will look fine we can have a quick look through each of the different tables and they look fine so power query has done its job and correctly assigned the data types for me I haven't had to do that if you do want to change the data type you simply click on the icon and you can choose a different data type or with the column selected up on the Home tab we've got the data type up here as well by the way it's in this query editor that I do my data cleaning and editing and I want to reduce the data that I bring into power bi as much as possible so that I'm not bloating my file with data I'm never going to use now the other thing I need to do is add some columns to the sales table to calculate the total sales because at the moment I've got the sale price and the cost price and the total units but I don't know the value of what I've sold so I'm going to add some columns for the total sales and the costs so up on the add column tab I can go custom column I'll give my column a name and I build my formula I can simply double-click on the field so resale price times total units and I'll click OK we'll do another column for the cost and again cost price times total units and okay now we'll do one for the gross profit so custom column and the gross profit is the sales minus the costs click ok so I've got my three new columns here what I want to do is set the data type because at the moment they're set to any so on the Home tab I don't set them to decimal number okay you can see on the right power query has recorded the step so it's got my first custom column second third and then me changing the type this is great because when I add new data to that excel file I can refresh the query it will go and get the new data and it will apply these same steps to that new data and have to repeat this work over and over again ok I think we're good to go I'll click close and apply Palgrave will load the data into the tables in the power bi model which is effectively power pivot it's a reasonable amount of data about just over 70 thousand lines of data in the fact table that contains all of our sales information so it'll take a few seconds initially ok so our data is loaded and we can see in the fields on the right hand side we've got our five tables I can click on the arrow and see the actual fields for those tables as well now there's three main areas in power bi desktop currently we're in report view we can toggle through the different areas with these icons on the left so we're in report view and this is where we build our visualizations and our charts and tables and that kind of thing next we've got our data view and this shows us the tables of data we can toggle through the different tables using the lists in the fields I can see the different columns of data and we can work with the data modeling side of power bi in the data table view and the last one is relationship view and this lets us see how our tables of data are related I'll just make the sales table a bit bigger so we can see it the connector lines show how the tables are related so you can see here that the sales and the buyers tables are related through the category fields in each table we can also see the direction of the relationship by on the arrow and we can see this is a one-to-many the one at this end of the connector line and the asterisk indicates a mini relationship at the sales table end now we've got the dates table over here and it's not related to any tables at the moment we need to create a relationship between the sales table and the dates table so that we can classify our sales data into our fiscal periods that I set up so I just need to drag left click and drag the date field across to the date field in the dates table power bi creates the connector for me now I've also got this dashed line down here for the regions and sales table relationship and that indicates that this relationship is inactive now in fact I don't need this relationship because I'm I have a relationship through the managers table to the sales table for the regions so I'm going to just select the connector line and press Delete to remove it and yes I want to delete that relationship now I don't have time to go into detail on relationships now but I do cover them in my power bi course one thing I want to point out here though is even though the fields in the sales table in the dates table the date field has the same name they don't have to have the same names in order to create a relationship it's more about the data in those columns being related and the dimension table this in this case the dates table should not contain any duplicate dates in the date column so one side of the relationship must contain just a unique list and that's why we have the one at this end of the connector line versus the mini so my sales table I have many dates that are the same because I've got lots of transactions so that makes sense the fact tables tend to have the many or the duplicates in one column whereas the dimension tables have the unique list okay let's move on because we've got a lot to cover now one last thing we need to do is notice the Sigma signs beside these fields this indicates that power bi will attempt to aggregate these fields and that would be the correct treatment because these are value fields fields that we would want to add up or average or find the minimum or maximum for down here in the region's table we've got the postcode field now you don't want to summarize that and nor do we want to summarize the financial year month so we need to fix those fields so that power bi doesn't try to aggregate them when we use them in our visualizations so let's fix the region's table I'll go to date of you I'll select the table from my list select postcode and then on the modelling tab over here instead of the default summarization beans some I'm going to choose do not summarize now we have the same thing in the financial month and I'll choose that table select the column change it to do not summarize if we take a peek back at the relationship view we can now see the Sigma signs are gone from those fields so that's exactly what we want one more thing I want to do in the table view is just apply some formatting to some of these fields so for example my gross profit cost and sales I need to format them so that when I use them in my visualizations they're formatted as I'd expect so to select each column choose the format I'm going to format them with their currency and we'll make it zero decimal places because these are going to be quite large numbers I don't need to see the decimal places okay another thing we need to do is help power bi identify which fields in our model relate to places or postcodes or countries and that kind of thing so I'm going to select the postcode field and up here in the data category I'm going to choose postal code and I'm going to repeat that for the regions table so that power bi knows what to do with these different columns when I start to use them in my maps so we'll call this a place and the state will be a state or province okay now I think we're ready to start building out visualizations let's go to the report for you the first port I'm going to build is a summary aimed at the overall picture of the business units and how they're performing now the first visualization I want to look at here's a KPI and that's this icon here and I want to look at the sales figures and we'll look at them over time so the trend axis will be the date field and I can use the pull handles to resize it now the area chart that you see in the background represents the trend of sales over time for the entire period of dates that are contained within this file and the bigger that we can see 2.4 million is the sales for the last month so I'm just going to format this so on the paint roller tab I'm going to change the display to millions and now we have our first visualization it's just a high-level figure so the next thing I want to look at is a chart that shows me my sales by state broken down by chain so we'll look at a clustered column chart I just selected I can move it around I'm going to pop it up here so the fields that I want in this are my sales and I want to look at it by region so I look at it by state and I also want to see it broken down by chain you can see as I'm selecting these fields that it's making a guess at the well that it should put them in so it knows that sales are a value so it puts them in the value field to add them up it knows that state is a category so it's assuming it I want that in my access now I also want to see the chain so I'm going to drag that into the legend field and now I can see my sales by chain broken down across the different states now I want to change the color format for this so I'm going to go to the format tab and then data colors and we're going to go with a theme where Billings is in gray and ready where is in yellow okay the next visualization I want to look at is my sales and gross margin by financial year quarters we'll add another clustered column chart and I want my sales and I want my gross profit and my financial year at quarters you can see I haven't had to tell it where to put these this data I've just put it into I just selected the fields and it's me it's put them in the right place for me now I'd say I think it would look better if the gross profit was a line chart so let's change the visualization and now we have to tell it which series to put on the line so on my gross profit in my line values and I want to have data labels to this let's turn the labels on and we'll get rid of the y-axis since we've got labels we don't need the y-axis as well it's just duplication now I might also change the format for my labels so I've got one decimal place and we'll leave it at millions all right what next I want to see the breakdown of sales by chain just the total breakdown so I'm going to use a pie chart now for most people who know me I rarely ever use pie charts in fact pretty much never but when there's just two series it's it's okay to use a pie chart so we'll change the colors for this as well to be consistent with our theme okay it's got a pie chart I also want to look at the data on a map I want to get an idea of how the different states are performing visually on a map so we'll use the globe map visualization and I'll look at sales and look at state let's see how we go with that it's taking a little bit the maps are use the Bing mapping engine ok so right away I can see a problem it thinks that some of my states are up in North America and Canada so the reason it's doing that is because of my state information is ambiguous it's not classified into a country so I can fix that ambiguity I'll go back to the data tab and on my regions table which I'm on I'm going to add a column that concatenates the state and the country so we'll add a new column and here we're using the power pivot model so if you've used power pivot before this is similar so call its state country and we'll use the state field so I'm just arrowing through the fields and we example san just like in Excel we're going to concatenate some text and I'll press enter populates the column for me now I need to give it a category so that the map knows to use it so click off and click on to select it again so here we're going to go state or province we'll go back to our visualizations remove the state field and we use this new state country field okay that's much better now actually I think I'd like to see it as a field map instead of the bubbles okay so with the Phils map the higher intensity of the color indicates the higher values and you can see when I hover over it I get a nice tooltip little different state information alright next I want to look at sales by category and chain so again a bar chart would be good because the category names can be quite long so look at category and sales will break it down by chain so again we're looking at our data for the different chains so I want to be consistent with my colors so we'll format them and yellow and I'd like to sort this so that the largest category is first at the moment it's just sorted alphabetically so click the ellipsis and then sort by sales okay so we can see men's is the highest seller all right the last visualization I'm going to put in here is going to be a bubble chart that looks at our sales and gross profit by category across time so I'll click on the bubble chart which is the same icon for scatter chart we'll make it the full area so in here we'll start with sales it's going to go on our x-axis that's correct I want the category from my legend I'm going to drag them in here so I get them in the right place I want a measure here instead of a calculated column I want to use a measure so we're going to up on the modeling tab new measure and again this is like power pivot if you've used that before so look at the gross profit percentage so in order to account like this we need to know the sum of the gross profit / the some of the sales and you can see I'm just typing in the start of the table and field name and then I get this list which I can arrow up and down through and it uses the same structured reference style that we have with Excel tables ok so that's my formula I'm going to press ENTER now it's been added to the buyers table so I'll select the field and then up here a home table I'll change that to sales while I'm here I'll give it a format so that when we use it in our visualizations it doesn't look silly so I'll see how one decimal place with a percentage format lives now I want to use this field and you can actually tell before I do use it that it's a measure because it has the calculator icon beside it whereas the other value fields have the Sigma sign so this is an explicit measure and these ones down here are implicit measures so if you've done if you familiar with PowerPivot you'll know what implicit and explicit is but that's the indicator for an explicit measure one that we've written ourselves using Dax formulas and that's what this is up here a Dax formula it looks a lot like Excel and that's one of the great things about Dax it is a lot like Excel so I'm going to drag my GP into my oops I need to make sure my visualization selected they'll drag it into my y-axis and we'll use our gross profit from the size of our bubbles so now we have this bubble chart and if I hover over the different bubbles I get a tooltip which tells me all of the information related to that bubble and there's one more thing I can do here with these bubble charts in power bi we have the option to have a play axis so I can actually look at this data over time I want to look at it by financial quarter so I'm going to drag that down into the play axis and now I can click this play button and you'll see how the data changes over time pretty cool huh okay so we've built our first dashboard actually there's one last thing I want to add and that's a slicer let's add the slice of icon here I'll just drag it up into the corner I want to be able to filter by state so I'm going to add the state field to my slicer and I'm going to just format it a little bit so that it's orientation is horizontal and I'll just make it a bit smaller and make the font smaller as well so that it squeezes in there now you notice that I have a blank in my slicer now that indicates that there's a blank state field in my dataset now I know there's not I don't know why it's doing this but I have a solution for it so and that is we're going to go to the field list I'm going to add a filter at the report level for the state and I'm going to filter out that blank so I'll select all and then I'll deselect blank and now it's gone from my slicer and this filter will apply to every sheet that I add to this power bi file so I won't have to do that again all right so we can feel about getting our formatting just how we want and I won't spend too much time on that because I want to move on to the next dashboard but before we do that I want to show you some of the cool things that we can do now that we've created these visualizations you're familiar with slicers I'm sure from Excel so we can click on the different states and you'll see everything in this dashboard filters accordingly but what's even cooler is that we can click on different elements in the charts and we get this cross filtering and highlighting effect as I click on a different item the previous filter gets removed and then the next one is applied so I can look at just menswear for the chain really where if I click on it again my charts and visualizations all revert back to unfiltered when we use a slicer we can filter and those filters are additive so I can have multiple filters and I can keep filtering and filtering and filtering however with the cross filtering and highlighting that you apply by clicking on different elements in your visualizations they're not additive there you click one it cross filters or highlights and then you click the next one and it applies a different filter or highlighting so it's just something to bear in mind you can remove all of my selections in my slice that by clicking the eraser symbol okay I'm gonna give my pager name just double click on it to give it a new name I'm gonna add a new sheet and we'll crack on with the next visualization which looks at regions and chains so the first visualization I want here is sales by buyer so I use the clustered bar chart it's gonna go down in this bottom corner so on the sales and I want the buyers name okay next I'm going to look at our data on a map by chain so I'm going to use the globe map and I want the sales and the chain now for this map I want to look at it by postcode but again we have some problems because you can see those dots in New Zealand and North America so again we've got the ambiguity problem that we had previously so let's go back to our data table for our regions we're going to add another column this one's going to be our postcode our suburb and our state's so we'll give her the name that should be country not state so my regions table and I want the postcode field and I need a comma and a space so just like Excel I can concatenate using the ampersand sign and put my text inside double quotes so on the postcode and then I want the suburb so again on the regions table I'll have the suburb I just have to accept it and then I just need to add Australia and I press ENTER there's D select it and then select it again so I can categorize it and this will be postcode we'll go back to our visualizations and the reason by the way that I put the suburb name in there is so that when we look at the data and we hover our mouse over it the tooltip gives us not just the postcode and Australia it actually tells us the name of the suburb but I think it's a bit more useful so we need to format this so that our data colors are consistent I'll make it yellow okay the next visualization I want to look at is the sales and gross profit by state and manager so let's look at another bar chart and this will be quite large so I'm going to take up all this space here so I want sales I'll look at gross profit percent I'll put that in a moment I want to show you how it's going to work so we look at it by manager and we look at it by state so when I drag the state into the chart it's automatically summarized at by state now I have these icons up here that allow me to drill down let me go into focus mode and that just opens the visualization on its own page and I'll click the join to double down arrow and now we've drilled down so that we have all the states and then the manager within that state so that's more what I want so let's go back to the report make a little bit wider now one more thing I can do here is I can add some color saturation for the gross profit percent so get an idea of how each managers performing so you can see here Lillian from New South Wales her gross profit is quite high compared to John and we can hover our mouse over and we get the tooltip which tells me her actual GP and the darker the color the higher the GPA changes to those settings in here but that's the default and that actually works quite well for I want so I'm just gonna make that a little bit smaller we'll move on and add some more visualizations the next one I want is an area chart to show me sales over time for the different chains so we'll look at sales and use the date field and I want the chains for my legends and move that into legend okay I'm gonna drill down a level that's better now I've got it by month and I'll change the colors so that we're consistent with the theme whoops okay actually we're going to remove this date filter I'm going to use the date field from the fact table okay that gives me a better indication of the pattern or trend of the data over time okay next we need a column chart that shows us the breakdown actually you know what I'm going to copy this chart it's relevant so just ctrl C to copy ctrl V to paste it in because it's relevant for this view of the data as well so pop it in there now I want to add some slices for this report so I'll click on the slicer icon once we have to slice by chain I'll pop that up in the top left putting them into a location in my dashboard that makes sense for the user so it's one of the first things that they come across in the top and they start filtering by chain I also want to add some key information so I'm going to use this multi row card and that will start in here so what I want to do is just show high level sales gross profit and gross profit percent and I'll resize this so that it formats it the way I want okay and I'm also going to add a slicer for the states because this report is all about looking at the data my regions and chain so it makes sense to have a slicer by state now the other thing I want to do is use a custom visualization so I can choose from all of these visualizations but I can also get custom visuals and I'll import one I've already downloaded one I can't go into detail on how to do that in this webinar because I just don't have time but I've got some custom visuals that I've already downloaded and one is a sparkline so I'm going to use that I do cover this in detail in my course I'm just waiting for it to load the sparkline and click OK and outlook here up here there is I'm going to add the sparkline so this I want sales by date and I want to look at it by state so I'm just going to align it quite close to the slice and I'm going to try and get it very similar to the slicer list so that they almost look related well they are related but a bit more closely related ok so it can mess around trying to format everything just just as we want now when I filter by States you'll see the map over here zooms in to show me just Queensland I've selected Queensland my sparklines filtered just for Queensland I can see just the Queensland managers and all of my charts and visualizations are filtered I can remove all the filters I can also do things like select New South Wales ready where and now my map over here is zoomed in and we can see that just the ready where locations are highlighted I can hover over the dots to get an idea of which suburb that is I'll click on that again to remove the filtering if I want to see Lillian's data I can click on this bar and now I can see these are Lillian stores and I can see all of Lillian's performance in each of the visualizations and I'll click it again to remove the formatting I can also collapse down these and see it a bit better one of the things I do want to point out is if we had area managers say we had a manager for New South Wales Queensland and so on I can actually use what's called row-level security and I can set up roles in my database and assign different managers to those roles and then those managers will only see the data that they're entitled to see so I could set up a filter that only shows Queensland data so only the manager who's assigned to that role for Queensland will see the Queensland data and all of these visualizations get automatically filtered for them I don't have to create multiple reports and delete data power bi can filter that automatically based on the role and who's assigned to that role so I think that's an amazing feature particularly certain reports like this where you want to distribute it to specific users but not have to create six or seven different reports for each manager now another thing a power bi can do is get updates from your data so if I look at our excel file I've got this sheet here called org data so at the moment the data in power bi goes up to July 2017 there's the end of the table 70 1971 rows here I've got my August data so let's just pretend obviously this isn't the real world but let's just pretend this is our August data and it's just been added to this table so I'm going to paste it in we can see the tables now expanded to incorporate the new data we've got 76,000 lines includes August I'll just save the file and then we'll go back to power bi and I'm going to refresh the power bi connection so on the Home tab I'm going to click refresh now I want you to look down here because this is currently only displaying data up to July and our total sales is 54 million when I click refresh it's going to go away and get the new data and every visualization in this file is going to update to incorporate that new data I haven't had to make any changes to anything it's just going to automatically incorporate it and there we go so now the data points go to the 1st of August and that's the date I've just got all of my data on the first of each month so it says the first of all this for that includes August data if we look at the summary we can see now it's 3.18 million that's August's data that's the total for August if I press the play button it'll go through to the same quarter but we'll have new data in the file so you can see the dot to end in a slightly different position and we can see here our sales for q1 20:18 have also increased remember our financial year and Australia's from July through to June so August falls into the 2018 financial year the first quarter of that financial year so once you set up your reports in power bi all you have to do is refresh the connection to the Daytona picks up all that new information the queries have applied all the same steps that we've set up when we added our extra columns for the sales and costs and our gross profit I haven't had to do any more so it's really a low maintenance solution once you get it up and running it's easy to keep up to date so now that we've built these amazing dashboards you'll want to share them with your colleagues and one way is to simply share the power bi desktop file in the same way you might share an excel file for example you can email it or put it on a shared network drive where others can access it anyone who wants to open this file will also need power bi desktop installed now while that's one way to show your power bi reports it's not really the way it was in because when you send someone your power bi desktop file they have access to all of your data and they can make changes to your file in other words there's no security you can't even protect the pages like you can with an Excel workbook so the better way to share your reports is via the power bi service and to do this we need to be logged in to our power bi account in power bi desktop and you can see I'm logged in there's my name up there and then all we do is on the Home tab we click publish I select the workspace that I want to publish it in and click select I'll cover more on workspaces in my online power bi course I can't go into everything in this webinar there's just not enough time now this might take a few minutes depending on the size of your file and the speed of your internet connection once it's ready to go you'll get a notification and you can click on that link to open power bi in your browser so what's happening here is power bi is publishing this file and all of the data up to the cloud in the power bi online service ok so we're ready to go I can click on the link here to open it in power bi com here we are in power bi calm you can see the new data set is here and we've got yellow asterisks beside the new item so it's easy to tell which one it is if I click on the report you'll see the report looks exactly the same as it did in power bi desktop I've got my two separate pages down here I can toggle between them I can interact with the report in exactly the same way everything you see is the same I can navigate between the report view and the dataset view from the dataset view I can build further visualizations have got all my tables here in my field and I can further analyze the data however there are good reasons why you shouldn't do this when your report comes from a power bi desktop file which I cover in my course so back in the walk view from here we can print from the file tab we can print publish to web you can even export it to PowerPoint if we want to share the reports with colleagues so they can view them in their version of power bi in a secure environment then we can pin this live page which is everything on this page to a dashboard and the pinned dashboard has the same interactive functionality we had in the report view but the added bonus is we can then easily share it with our colleagues who also have a power bi account so we'll give our dashboard a name we tell analysis I'm going to pin live and that will maintain the connection between the dataset and the dashboard and that way if the data updates it will be reflected in this dashboard here so the dashboard works in this in a similar way we can still click on it and interact to filter we can use the slices everything works but the added bonus here is now I can easily share it I can click the share icon and I can grant access to my colleagues simply enter their email address and a message if I want and they will be sent an invitation to view my dashboard and if they're part of my organization then it will also appear in their list of reports and they can navigate to it from there and this means our data is secured only the person you share the dashboard with can view the dashboard and they must be logged in to power bi to see it now there are loads of rules with regards to sharing and options restricting what data people have access to I just don't have time to cover them in this webinar but I do cover them extensively in my online power bi course one thing I should point out is recipients who you share your dashboards with have access to the same data and reports and workbooks that you used to build this dashboard the difference is they can't save any changes that they might make to the filters and that type of thing when they're looking and reviewing the dashboards that you shared with them now there's another cool feature I want to show you in that's only available in the power bi service and the first one is called quick insights now quick insights runs a sophisticated algorithm against your data set to find potentially interesting insights that you might not have discovered yourself it's great if you're not familiar with the data or you want to discover trends or outliers or correlations that you might have missed to generate quick insights we click on the ellipsis beside the data set and then quick insights it'll take a little while to go through your data set and when it's ready to go a notification up there here in the top right so you can see it's searching for insights now what we'll get is a series of visualizations for outliers and trends correlations not all of them will make sense but we can help power bi do a better job by naming our fields appropriately so here we are with the insights you can see as I scroll down there's a whole load of different visualizations and it's found things like for example here the GP percent for Tasmania the state of Tasmania in Australia as outliers so I can pin this to my dashboard or I can go into focus mode and from here I can alter filters and I can get further insights on this particular dataset this filtered view of my data so that's a really quick overview of quick insights I'll go back once it's generated the insights they're there and we can access them again from the view insights menu now the next thing I want to show you is Q&A and Q&A is available from the dashboard view and basically Q&A allows you to use natural language to ask a question of your data as you type you'll see fields from your data set appear and you can select them or keep typing so I've just typed in sales by chain and you can see power bi is generated this bar chart for me I've literally typed in three words and I've got myself a bar chart I don't even need to know how to create a visualization and from here I can pin this visual I can expand the visualizations and fields panes and I can further modify what I see here or I can keep typing to zoom in further on the data so QA allows you to get quick answers to your questions without having to understand the model structure or even how to build visualizations and there's no question that power bi is an amazing tool and just to be clear everything I've shown you today has been done with the free power bi tools available it's really amazing how much we can get for nothing now before we wrap up there are some things I want to point out and reiterate this dashboard is connected to the power bi desktop file I built if i refresh the power bi desktop file let's just go and have a look at it if I click refresh and I get new data then there are a few different ways to update the power bi service view of the data but the point I want to make here is I can refresh this and then anyone I've shared my dashboard with will have the latest information I go into more detail on how all the intricacies of refreshing works in my course now the visualizations and the related data set have been published to the power bi service it's a secure service but having your data in the cloud might be an issue for some companies so I cover the alternatives in my course when you share a dashboard those you share it with have access to the same reports and underlying data but they can't save any changes unless you allow them to and this access to the underlying data allows them to dig into the data and find answers to their own questions for example you saw me type in questions in QA and I was able to dig down into the data people you share the dashboard with can also do that although you can turn Q&A off but again I'll cover that in my course if you wanted to restrict access to data based on a user's role for example you want each Regional Manager only to see their region then you need to use row level security now I cover row-level security in detail in my course as well the other thing to bear in mind for Excel users is that the same power query and power pivot tools we have in excel are used in power bi desktop so if you already know these tools then you're off to a head start but if you're one of the millions of users who have a version of excel that's not compatible with these tools then you don't have to miss out anymore because they come with this free power bi top application so now you can get power query and power pivot skills up to speed and when your company eventually upgrades your version of Excel you'll be way ahead of the curve well I hope you enjoyed my webinar in under one hour I've built two hugely sophisticated dashboards publish them to the power bi service I use quick insights to check for outliers or interesting facts about my data I use QA to ask a question and have power bi create a visualization for me without me even needing to know how to build a chart now I'm sure you'll agree it's truly amazing now I've really only touched the surface of what power bi can do and how to use it for example I didn't really need to use the power query or power pivot tools much in this example and that's because my data was already clean and ready to use for the most part but for most of us this is rarely the case I mean how often do you find yourself loading data into Excel and being able to dive straight into analyzing it in a pivot table not very often I'll bet so typically we need to clean up our data maybe merge data from multiple sources and add some calculations to it before we can even start the analysis phase so we can break power bi down into three main components that you need to master their power query to get and clean your data power pivot to model it and then finally the power bi visualizations now it's clear that power bi is a broad tool with lots of functionality to navigate and if you get some things wrong when you're setting up your model and reports then it can be really difficult to undo them there are loads of settings to control who can see what and other fine-tuning that can be applied which is why I put together a course for power bi so if you want to get up to speed with power bi quickly and follow our logical process for learning all about it including the power query and power pivot tools then I recommend you take a few minutes to check out my online power bi course so what's next well go ahead and download power bi the desktop application is free and I've included a link on where to get it in the description below and to help you get started I've also included links to we can download the power bi desktop file and data used in this video so you can go and practice yourself the download link will take you to this resource page and if you scroll down you'll see there's a link here for the power bi desktop files just click the orange icon to start the download and from our website under the pricing menu you can find more information about my power bi course now if you enjoyed this video please take a moment to give it a thumbs up and share it with your friends and colleagues and while you're here please subscribe to my channel well that's it from me and power bi for now I hope you're as excited about this amazing tool as I am if you have any questions be sure to email me at website at my online training hub com thanks to your time

Channel: MyOnlineTrainingHub

Views: 1,313,678

Keywords: power bi, microsoft power bi, power bi dashboards, power bi reports

Id: BsXliHbOFDM

Length: 52min 31sec (3151 seconds)

Published: Sun Feb 25 2018