Beginner to PRO Data Analysis with Power BI - Full Length Course (with sample files!)

welcome to data analysis with power bi course i'm so glad to have you in the class in this full-length course we are going to learn all about power bi when it comes to data analysis as this is a fairly complex and comprehensive course i have provided sample workbooks that contain data sets completed example file so that you can follow along in a nice manner i highly recommend setting time aside to complete this course in one go so that you can learn understand and appreciate what power bi is all about let's get in in this particular video the skills that you will gain are power query for data cleanup data modeling introductory dax and power pivot working with various visuals within power bi and overall power bi familiarity in terms of the agenda we are gonna look at these things we'll start by talking about what is power bi and then i will explain the data cleanup situations then we will answer specific business related questions using power bi visuals and data analysis features we will then talk about trends and forecast type of analysis with built-in features of power bi you will then understand how to save publish your work with power bi and how to manage the data updates and the refresh process we will conclude by talking about resources to learn power bi so that you can take your learning forward for the purpose of this particular course i'm going to use a fictional company called awesome chocolates regular viewers of my channel are familiar with this particular company so let's go over to power bi now this is how when you open power bi it looks on your computer for your reference i am running power bi desktop software the latest version it is december 2021 version if you do not have this particular software you can download it for free from the microsoft website so here it is the website i have put a link for this in the video description as well when you go to this website there is going to be this big yellow button download free and you can download it the power bi desktop software which is what we are gonna use now is a free application that you can install on any windows computer apart from the free power bi desktop software power bi also offers a monthly subscription plan or a paid plan for you to share your work and publish your data online so that you can collaborate with your teams we'll talk more about that towards the end of the video for now i'm just gonna close this so that we can come to the main open screen of power bi and just get a quick familiarity of what is on the screen the power bi screen can be divided into four main areas you have a big ribbon on the top and this lists all the key functionalities of the application the ribbon itself is further divided into four or five different ribbons and additional ribbons that will come and go depending on what you are doing at any specific time if you are familiar with other office software such as excel or outlook you can contrast this ribbon with those tools the main ribbon that we will be using throughout the initial few minutes is the home ribbon but later on we will go into the other ribbons the next big area is the dotted line area in the middle this is the canvas area or the visual area so this is where you will build your reports or your data analysis again this is blank because we don't have anything in our power bi workbook currently you have a couple of panels that can be collapsed or expanded on the right hand side the two main panels that you will be working on are the visualizations panel and the fields panel and on the left hand side there is a tiny bar that shows options for you to switch between different views again since we don't have any data this is all going to be blank so let's load up some data so that i can explain how each of these fourier areas behave we'll start by clicking on the get data when you get into power bi the get data button is available in multiple places so i can use my fields button i can use the button options here or i can use the one there for the purpose of this exercise we will get the data from the excel file let me quickly show you what is in that excel file so this is the sample data that we shall be using it is for the fictional company or some chocolates and we have the sales information from the year 2021 in this workbook each row tells us what happened on a specific date for a specific sales person so for example here bar funny sold to the geography of new zealand product named raspberry choco on the 1st of jan 2021 for 8 414 now if i want to know a little bit more about who is bar phony or what is the type of product raspberry choco then we need to follow the thread here so this is where the other data tab comes in in the other data tab i have three additional table each table explaining one aspect of the business this is my product table and if i select raspberry choco i can see that it is a bar category product and it is a large sized product likewise new zealand is in the apac region likewise bar funny belongs to yummy's team and we have his picture as well so this is how our data is available so i'll click on import data from excel notice that while for the specific example here i'm using excel file you could use power bi to import data from sql server or paste data directly or use any of the myriad types of data sets that power bi supports locate the file on your computer if you have already downloaded it just point to the downloaded workbook and click open this will show you a navigator screen and it will give you option to pick the data that you want to work with in our excel file power bi located that there are four different tables and we need to use all the four tables to paint a full picture of the business so i'm gonna just select all these four items we don't need to select the data or the other data options and we will click on this load button to load up the data eventually power bi will load the data and now you can see that many of the screen options have quickly changed we can see all the four tables available for us on the right hand side and i can expand a table to see the available fields or columns for me to work with and if i go to a different view so from the visual or report view if i go to my data view i can browse the data as well so if i select the sales table i'm able to see what is in the sales table likewise there is also a model view through which we can connect the tables notice that power bi would have automatically connected some of the tables but not all of them let's just expand on this connection feature we see that people table and product table are connected to sales if i point on the line i can see that sales person in the people table is connected to the sales person in the sales table this is perfect because once the connection is established i am able to go and learn a little bit more about my data by using the other aspects of the people table again if this is your first time using power bi some of these words or concepts might seem unfamiliar to you but as we make progress everything becomes clear same on the product side the product column here is linked to the product column there what about the geography why is it not automatically linked this is because the default behavior within power bi is if the column names match then power bi will try to connect them but if the column names do not match because jio is what it is called here whereas it is called geography here power bi wouldn't connect them but we know that they mean the same thing so i'm gonna click on geography hold and drag and drop it on the geography here and a relationship will be established notice that when you make a relationship the relationship will have a type of cardinality here so this says it is 1 and then star here this type of relationship is called one to many relationship it simply means each geography will only appear once in the locations table but it can appear many number of times in the sales table a more common way of thinking this is you have six geographies and to each geography you can sell any number of times hence the name one to many you might be thinking what does this arrow mean this arrow indicates the direction of the filters so now that our data model is set up everything is linked up let's go and first make a visual to understand what everything looks like and then if needed we will go and clean the data so to start off what i want to do is i want to understand how we are selling to each of the geographies that we operate in we operate in six different countries as you might remember from the locations table so i want to just see how much we are selling to each of the six countries so we'll put a column chart when you click on a visual power bi will automatically add a visual for you on the screen you can move this around you can also resize this so let's make it nice and big and i want to see the locations on the axis so we'll go to the locations table select the geography and put it on the axis for the visual so that each location will show up on axis right now it is still blank because only when you add a value then only things will show up so it will add a value the value that we want to see is the total amount so i'll take the amount and put it into the values area and then we will get these values i can see our six geographies here but there is a blank geography as well this is concerning i thought we only had six countries where is this blank coming from so this is the time for us to go and examine the data a bit more carefully if you notice the geography column in the data file itself even though we have six geographies there is some inconsistent spacing this looks suspicious if i go to this cell it is usa but there is some extra additional blank spaces in the beginning this is why we are having a problem you have two options here you can change the data in the original workbook clean it up and then power bi will get the correct data and then it will be able to map the relationships and the blank will no longer appear or we can clean the data at the power bi level let's clean the data at power bi level to do this from home ribbon we need to go to the transform data option this will open up a separate screen and this screen is called power query editor power query is the data processing engine of power bi using power query we are able to tell power bi how we would like our data to be processed cleaned or reshaped as you can clearly see here my geography column has inconsistent spacing an easy fix for this is we would just like to remove any extra spaces in the beginning or in the end this operation is called trimming all we have to do is right click on the geography column transform and then trim and that will clean up the spaces on the geography column when you do these kind of changes none of the data in your original excel file changes power bi will simply add a rule at its level saying that whenever there is a space just remove it because it is a rule whenever your original data changes and you rerun the process it will automatically get the data apply that rule again so this is kind of like a very powerful automation feature built right into power bi you can just close and apply to commit these changes to the data so once we do that notice that magically our blank is gone and we again see all our geographies you can clearly see that india is our biggest geography in terms of the amount of sales followed by new zealand uk and australia is our smallest geography let's move this to the side so that we can also look at the amounts by individual sales people so for this purpose i will pick a different chart i'm gonna choose the bar chart and within this on the access i want to put my sales person name and then again we will see the amount while this shows the amount by sales person it is a little bit too much detail so i would like to take away sales person and instead look at the amounts by teams so i'll delete the sales person by clicking on the x mark here and then drag and drop salesperson team into access we see that there are four teams yummy's dallas juices and blank oh we have another data cleanup problem here before we go and fix this let's first understand another crucial feature of power bi i want to know where these blank people are selling to so if i click on the blank notice that instantly the portion that corresponds to this blank team is highlighted here we can use this kind of a thing to investigate problems in our data so we could use this feature by adding a table and in this table let's put all our sales people and these are the blank people these are the people that don't have any table any teams camilla chess jehu etc whereas if i go to juices i can see who are the members of the juices team so this is a powerful way for us to investigate where the problem areas are but the biggest takeaway for all of us is every visual within power bi is interactive so if you click on one visual an item on that then all other visuals will instantly respond and highlight or filter the values that corresponds to what you picked this creates for such a dynamic and powerful reporting setup for all of us and that is why the interactivity feature of power bi is one of my most favorite areas of power bi okay time for us to go and fix the blank problem so if i go to the data view and look at my people we can see that certain people do not have an associated team name there one way is if this is blank then i want to call them as special team so to do that again we can go to the home transform data go to the people table and notice that the blank values appear as a null within our query and here we can add a rule the rule will be a replace values rule and we would like to replace all the nulls with special one note of caution here power query is case sensitive so you will have to specify value to find as small letters null and again this will add a rule rather than manually replacing so if you get updated data and there is still some null values they will all be called special we'll close and apply this now they will all be called special if i go to the report view i no longer have a blank i have a special and i can pick that and then i will see those people this concludes part one of our class data cleanup in the next part we are gonna look at how to do business analysis with rbi before we do that let's just quickly save the file to save click on the save icon on the top or go to file and then save your power bi workbooks are saved with a dot pbix extension in your computer for the next part of our analysis we will pick the problem of analyzing the sales person performance a long way you will learn various visuals and how to use the dax or power pivot part of the power bi we shall do all of this analysis in a blank page to add a new page click on plus button at the bottom and you can add a page by default these pages will be named as page 1 page 2 like that but you can double click to give it a name within this page we would like to understand how each of our sales people are doing because we have quite a big team using a table visual for overview of everybody is a good idea within this table visual i'm gonna put our salesperson name i want to see what is the total amount for each sales person to do that we have been adding the amount column to the values area and letting power bi do the automatic calculations but now let's take control of this by defining the calculation logic ourselves so to do this right click on the sales table and use the new measure option you can trigger new measure like this or using the new measure button on the home ribbon when you do this power bi will open a formula bar for you to define the measure logic the language that you use to create these measures is called dax data analysis expressions so we'll create a very very simple measure so that you can understand how dax works the measure name will be total amount and this is nothing but the sum of [Music] my sales table amount column you don't need to type everything when you see the highlighted value in this light blue color you can press the tab key and let power bi do the typing for you so this is the definition of the measure sum of sales amount and this formula will be used wherever you apply total amount and for that scenario or the situation total amount will be calculated so we will commit this with the tick mark and you will see that total amount appears as a measure on the sales table measures are denoted with a special calculator symbol on the screen so that when you have a big field list you can easily spot the measures a measure not only has logic definition like what the measure means but it also has a visual definition to do that while you are editing the measure you can use the measure tools ribbon to set the formatting because this is a currency i want this to appear with dollars with zero decimal points and now if you add the total amount to the table for each person their total amount will be automatically calculated we can think of measures as teeny tiny calculators that are attached on top of the data while the measure doesn't physically exist in the backend cable it will be dynamically calculated and shown whenever you use it on the visuals within power bi in this table we are seeing all the sales people but let's just say you want to see one team at a time to limit the data that is going to the table we could use a filter or a slicer so let's add the slicer visual a slicer will give you choices to pick from and within this slicer i'm gonna put my team so that we will have four buttons one per team and now i can pick a specific team delish juices special or ms and then just see their sales people and the totals for them let's understand how to adjust the fonts and colors of these things so that it looks in a more report fashion rather than a bunch of things put together so we'll select the table visual and then from the visualizations panel from the build we go to the format visual options the paintbrush one and from here you can set the formatting of everything to start off i'm going to the values and from here i will change the font size from 10 points to 16 points so that everything is nice and big you can use the grid options to set the row padding right now it is one if you change this there will be more white space given so that things become easy to read let's make the header also 20 points and add a background color and change the color of the header to white and change the font to sego ui bold because we are looking at a team level i think the total is not really required so i'm just gonna turn off the total altogether so we are only looking at the values because there are multiple visualization formatting settings that are available sometimes it is hard for us to remember where everything is this is why normally when i use power bi i don't actually go and look for things i just type them in the search so for example if i want to adjust the font size i will search for size and it will list all the sizes that i can play with and from here i might make the adjustment in this case i think the font is a better option so here font and then i can adjust the 16 points to whatever i want i think that looks good but let's circle back to the concepts of dax and build few more measure to start off let's add what is the total boxes to do that we are going to define another new measure so right click new measure and this is total boxes is equal to sum of and then using the auto suggest you can just type b and tab and it will give you the whole thing we will format this as a whole number with thousands separator and zero decimals and again we can select the visual and add total boxes because this is a sales performance report i would like to sort this on the total amount so we just click on the total amount and then the best performing sales person within the currently selected team goes to the top at awesome chocolates we would like to calculate the amount per boxes as well so that we can see which sales people are bringing in more money per box given the fact that we already have total amount and total boxes calculated as two measures we can create a third composite measure that shows amount per boxes here to do this simply right click on the sales table add a new measure this measure would be amount per box and it is nothing but total amount divided by total boxes you might think it's a little bit confusing how we are referring to the measures as directly like this within square brackets but when we refer a table column for example sum of sales boxes we use the table name column name notation so when do you use this format and when do you use that format the logic is very simple when you are referring to another measure in the workbook you simply use it directly without specifying the table name this is because a measure gets same result irrespective of which table it is on whereas when you refer to an actual column in the table you must always specify the table name and column name so we'll add that and i'm just gonna set this formatting as dollar with one decimal point and now we will have amount per box as a measure available to us in the table and i can add it to my visual i can see that amount per box varies per individual but certain individuals have very high amount per box even though they are not selling as high as other people let us say from a performance perspective amount per box is the most important metric and we would like to highlight this in the report by showing a little bit more visual indication of how high this number is to do this we could use the conditional formatting feature of power bi select the amount per box and click on that little down arrow and from here i can add conditional formatting let's select the data bars you can see that there are many other types of conditional formatting available but data bar is what we will work with for now i'm just going to click ok so that we can get the default data bar as you could clearly see the data bar will print a bar inside the cell and it will be as long as the value is so the highest value takes up the biggest bar and everything else is scaled down automatically and everything is interactive so if i select a different team i'll get the values like that if you want you can adjust the color or the sizing of these things so let's just say i want to adjust so we can go conditional formatting data bar and from this blue color let's just change this to this color amount per box and then the maximum value let's just set instead of highest value i'm going to set it to a custom value of 25 what this will do is it will assume that the whole cell is equal to 25 and based on the value it will scale things down so this will make things a bit more readable for us our sales performance report is looking pretty good and interactive let's learn a few more tricks to make this a bit more visual and interesting if you go to the table view you will need notice that in the people table we have a picture of our employees so let's add a little bit of color and personality to our reports by bringing in the picture of our employees to do this select the table and then locate the picture and add it right after the sales person by default the picture will show up as the url but we know that this is not the url we need to get the actual picture itself so to do this we will go back to the table view select the picture column and from the column tools ribbon that appears only when you select a column set the data category from uncategorized to image url this is a one time step and when you do this henceforth whenever you use the picture column in any of your visuals it will be treated as an image and the actual image will be used instead of the text value of the url now when you go back you will see the people's pictures along with their data it looks a little bit more presentable and interesting and when you see the picture you can immediately relate to the team members you can move the picture to the front if you think the picture is too big you can also adjust the size by going to the format and looking for the height argument of the image height i'm going to make my images 40 pixels height so that they are there but they're not taking up too much space let's add few more details to our sales performance report whenever i select an individual sales person in the report i want to see how well they sell to various geographies as well as which categories they are selling so let's go and build these graphs so i'm going to add a column chart and in this column chart from the product we'll pick the category onto axis and total amount into the values so this is right now showing all the special teams values but if i pick any individual jehu i can see that jehu is selling bars bytes and other like this while this is good the problem with this approach is it is showing me the proportion of jehu as against her entire team what i instead want to see is when i click on jehu i want this graph to reflect just jehu's data alone so this kind of thing requires a little bit more customization on the interactive behavior of power bi as i mentioned earlier everything in power bi visual is interactive so if you click on one item the other items respond but how they respond is by default decided by power bi but we can customize this so when we click on the table we want this to change so i'm gonna select my table and then go to format and click on edit interactions because this table can interact with that slicer as well as this graph power bi will show some additional icons on top of the graph we don't need to touch this side we only need to adjust this and right now the behavior that we have is highlighted behavior so the middle icon is enabled there are three kinds of interactions that you can have on the visuals you can highlight you can filter or you can have no interaction so from highlight i'm gonna switch to filter what this will immediately do is when you select something only that value will be shown here so i can select mallory jehu but if i unselect that then i'm seeing all the team uncheck the edit interaction so that those icons go away and we have set the interaction so now i can click on jehu or mallory or chess and then just see how they are doing by category let's add one more graph this one needs to be total amount that goes to individual geographies you can add it from here or if you already have a graph that you like you can just ctrl c ctrl v resize it and then from access just change it to what you want so this is how the special team's geographical performance looks like and if i pick someone i will see that for now i will leave this interaction here as highlight but if you want you can also change this to filter option so far our report looks good but it needs a proper title and our awesome chocolates logo on the top so that when you publish it people can read it and understand what is going on to add the title you can go to the home ribbon and click on text box and type the title you can move the text box anywhere on the screen resize it and adjust the font sizes as well to add the logo go to the insert ribbon and click on image i have provided you a copy of the awesome chocolates logo png file but feel free to use any file from your computer the logo will be added again this is just a box so you can resize it and position it wherever you want on the report okay everything looks good but i'm just feeling a little bit uncomfortable with our slicer because we only have four teams we don't need four boxes i thought we could cut down on the space that is used you can select the slicer and click on that little arrow and from here instead of a list you can change this to a drop down so you can pick a team from this or if you want to clear you can use the clear selections option if you want to rename a particular chart so for example instead of total amount by category you want to call this as category performance to do that just go to general and then from there there is a title option you can type the title yourself and that is what it will say here you can even add a background color on the title change the font options if you want so that is our category performance feel free to make similar adjustments to the geographical part as well as we make these adjustments you might notice that there is actually quite a bit of white space here i deliberately left out this white space so that you can fill it up with your imagination do let me know in the comments what awesome things you're building in that white space and that concludes the second part of our tutorial in part 3 we are going to learn how to do trend analysis and forecasting using power bi let's go to do the trend and forecast analysis you obviously need some sort of data that has a time or date component in this situation our sales data has a date and an amount attached to it likewise we also have customers and boxes for the purpose of this exercise i would like to understand how many customers we are serving on daily basis in the year 2021 and then see the trend and if possible do some forecasting for the year of 2022 to understand the trend you can use a line or an area chart let's insert a line chart and on the access i'm gonna put my date whenever you have a date within power bi power bi automatically builds a date hierarchy for us for these dates i would like to see how many customers we are serving we don't have any measure on the customer so i'm gonna quickly build a measure you can do the analysis without building the measure but as a good practice you should always build the measures and then use them this way you will have better control of what is going where and how to display them so when you add this we will only see a single dot instead of a line this is because our graph has already built in the hierarchy and it is showing me the data at the highest level which is for the entire year of 2021. to see the trend at a lower level by quarter or month we need to use the pitch fork icon or the icon that looks like this so i'm gonna click on it once it will show me the quarterly trend let's go to the monthly trend and explore what is going on so this is how our pattern is from jan we trend down all the way up until june and then we pick up in july august and then we go down then we are picking up back again at the end of the year just keep in the mind that while awesome chocolates does sell chocolates it is a made-up company and all this data is randomly generated so what we are seeing is not really a trend but more of a random noise this is our overall trend we can add a level of filtering to it to see the trend by for example individual product category or a geography let's see whether the trend looks like this for all categories or not so i'm gonna select this chart and in the legend i'm gonna add my product category this will split the line into three different lines one per category so this is my trend and atom at monthly level if i want i can go one level down to the daily level but because at a daily level there could be a lot of fluctuation it creates more noise than meaning so to go up to the higher level we can use the up arrow icon now let's just forecast this for the next three months you can add forecasts and trend lines and other things through the analytics option here from here we are currently not able to add the forecast in order to be able to forecast you need to have only one series on the chart not three different ones so i will go here i will remove the category so that we are looking at the overall customers by month and now if i go back here i will find that i have a forecast option i can enable that and power bi will add a forecast for the next few months so i'll change the units from points to months and then i want to forecast for next three months with 95 percent confidence interval and when you apply it will give you the forecast so the forecast says your say your number of customers could go down in january and then they'll stay like that in february march just a note of caution when you use the built-in trend analysis or the forecasting features of power bi they are purely built on the statistical techniques so when power bi says your forecast is looking down or up it has no idea about your business or your complex real world situation so this is my total customers forecast i would like to build a similar forecast for total amount so i'll control c this chart control v and within this chart i'm gonna change from total customers to total amount we'll go here enable the forecast now that we have built the forecasting it in power bi let's conclude this part of the tutorial and then move on to the next part where we are going to talk about how to save and publish our power bi reports so that other people can read them let's go if you want to share this workbook with others in the organization then you need to use the publish option of power bi in order to use the publish features you need an online account with power bi and this is how power bi makes money let's see what happens when you click on the publish as i have already logged into my power bi account it opens up my workspace and asks you is this where you want to save let's select this workspace and put our report there our report is now published to the online workspace and i can view it on the cloud platform it will ask me to login if i have not already logged in and eventually opens the report on the online platform your pages will be available to you on the left hand side and you can quickly select that this view is still assuming that chendu is using the report so this is how i can see it but i can still edit it if i need it everything is still interactive so i can click on this or i can pick a different team and then see the results how does this report look to someone who is not chandu for that we need to use the share options so here i have opened the power bi report on a different browser so that it has doesn't have my login setting and now i am seeing the pages as navigation at the bottom and i can go to different pages of the report to see everything is still interactive but i'm not able to change the report or change the data set so this is the power or beauty of power bi it gives you a two-tier system where at one level as a data analyst you can build and control the report manage the data and everything and at other level or other tier the persons who are viewing the report they can only just view what you have built so now let us see what happens when your data changes for the purpose of this i have given you an extra data file as well the link for this is also available in the video description i've already copied the extra data and i'm just going to paste it here now notice that this data is for 2022 january and i will save this file now go to power bi and to get the new data all you have to do is click on the refresh button here this will update the data recalculate all the totals and measures and everything and your numbers will quickly change it will also add the january values and then the forecast will be moved further up if you remember our forecast was actually predicting that the values will go down but we had more values in january for both customers and amount and this is what i mean when i said the forecasting within power bi is simply statistical and it has no idea about your business to update the online view of your report you just have to hit the publish again it will ask you to save and it will give you a warning that says i have already found a data set online do you want to replace it we are just going to replace this and it will update the online version of your report to see the updated values on the online report you just have to refresh your browser window and then it will show you the updated values on all the pages so now that you have developed a solid understanding of power bi let's talk briefly about how to take your learning forward one of the biggest challenges when it comes to learning power bi is many people think power bi as one big piece of software but as you have clearly seen power bi has separate features or tools to deal with individual problems if you have data connectivity or cleanup issues then we use the power query to clean the data if you want to calculate things on your data then we use the dax language and powerpivot to do that and finally to tell the stories or build reports we use the visual layer so when you are learning power bi you should also align yourself against these three layers i have prepared a detailed one-page pdf of various power bi resources and channels and books that i personally recommend and use so that you can also learn power bi if you would like to get a copy of that please use the link in the video description below and download that pdf thank you so much for watching this lesson i hope you really enjoyed it if you enjoyed this video please share it with one of your friends or colleagues so that they can also learn power bi and improve their career prospects thank you so much i'll catch you again somewhere else bye [Music] [Applause] [Music] you

Channel: Chandoo

Views: 428,854

Keywords: chandoo, chandoo.org, how to use Power BI?, getting started with power bi desktop, getting started with power bi desktop course, Data analysis with power bi, beginner to pro power bi course, power bi, power bi tutorial for beginners, power bi tutorial, power bi dashboard, power bi interview questions, power bi course, power bi dashboard design, power bi desktop, power bi data modeling, data analytics, forecasting in power bi, trend analysis in power bi, data modeling

Id: uwe8C7K8fXY

Length: 42min 50sec (2570 seconds)

Published: Tue Feb 15 2022