Master POWER BI in just 1 hour! From Zero to Hero 💥

what's up guys thank you for joining today I'm going to show you how to play with power bi yes if you have totally new to power bi this is the time my friend we're gonna go from Zero to Hero so please grab your cup of coffee your water and get comfortable so now let's do this [Music] real quick before we get started this is the final results that we are trying to achieve with this tutorial as you can see this is the stock performance over time for three us companies and you can play with the filters here and you can see that this report is totally dynamic check this out everything is changing here keeps changing you can also play with year 2013 for example so everything is changing and then let's say just Apple you can see the information just for apple and then if we have enough time we can also work on a customized date filter check this out this is the customized date filter that I'm talking about seven days for example here 15 days one month year to date three years five years so everything here is changing dynamically and as you can see this is really cool and now let's do this here I have the steps that I'm gonna be following to create this report and I'm going to share with you as well so you can follow along and now let's open power bi desktop this is the file that we're going to be using and then let's do something really cool here let's split this window into like I said before I have here the steps on the left and then I have a power bi desktop file on the right so let's get started if you are totally new to power bi you need to download and install the latest power bi desktop application just follow the steps here this is the link so once you use this link you're gonna have this information right here and there is an option here to download Power bi desktop go over here and then just follow the steps I'm not gonna do this because I already have the application but if you don't have it just do it and now let's get started folks so we're going to be using here historical stock data from finance Yahoo and this is the website just copy this website to Google and then you're gonna be good to go this is the website and let's look for three companies Apple Microsoft and Amazon this is going to be apple the first one right here and then here there is an option historical data hit that option so there is a filter here maximum data historical prices here are fine daily apply filters here so once you are done with these filters hit download I'm not going to do this because I already downloaded this file but that's what you need to do and then the next company here is going to be Microsoft so you're going to be doing the same thing here Microsoft here historical data again similar steps here okay maximum historical prices daily apply and then hit download the next company is going to be Amazon fetch option here and just follow the same steps historical data time period maximum historical prices daily apply the changes here and then download let's go back to Parkway desktop like I said before I already downloaded the files so the next step here is going to be to import the data into Power bi desktop so let's go over get data here and then this is gonna be actually text or CSV and I have the three files here okay so let's get started with Microsoft open this file so it's loading so let's hit low for now so you can go overview here data so we do have here the file already home again get data and let's do the same for the other two companies Apple next load perfect let's do the same for the other company Amazon load so the next step here is to transform this data and append the three CSV files so let's do that so let's go over home again and then let's hit this option this new window pops up this is the power query window our goal here is going to be to combine these three tables so we're gonna be appending these three tables in just one before we do that let's do something really cool let's go over Microsoft first and then here we're gonna add a new column a new column column for an examples check this out and we're going to call this column company this is Microsoft perfect so that's what we need we hit OK and let's see what happens now we have this new column called company and this is of course Microsoft so perfect we are good to go here and then let's move on to Apple and let's do the same columns for an example this should be company and they should be Apple perfect hit okay let's do the same for Amazon perfect hit okay The Next Step here is to append these three queries like I said before let's select Microsoft once we are here let's go over home and then this option right here says append queries as new let's hit this option since we have three tables let's select this option right here we already have Microsoft here we need to select apple and Amazon hold down control to select multiple options here and then let's hit add hit okay let's see what happens and then we can change this name let's call this Tech because these are three tech companies awesome so this is working perfectly fine when we are appending queries or tables what is key is that the headers should be the same the column header should be the same among these three tables so perfectly fine for now let's hit close and apply it's loading The Next Step here is to create a calendar table a calendar table is required if you are dealing with time intelligence calculations and we're gonna be dealing with time intelligence calculations in this report so let's do that there are a few options to create calendar tables in power bi for this particular case we're going to be using power query so let's do this so let's go over get data and then there's an option here called blank query let's get this option so the power query window pops up again and let's get started we need to create two lists first the first list is going to be like it says right here so it's right there here start date and this is the piece of code that we're gonna be using Okay Ctrl C Ctrl V and then we need to change here the table name and this is going to be Tech we're going to be referencing here the tech table and then within the tech table we have a column called date so that's perfectly fine and then this name should be start date start date we are good to go and let's do the same here to create the other list and date right click blend query and for this particular case we're going to be copying the piece of code that we have right here and this should be equal same thing here this should be just the tech table and then the date column within the tech table let's hit OK go to go let's change the name here as well this should be call end date and then the next step here is to create the calendar table itself right click again new query then query once we are here we're gonna be copying this piece of code Ctrl C Ctrl V and here we don't have to change anything because we already created the first tool list if you remember start date and end date so those names are the same let's hit a proof and let's see what happens boom now we need to transform this or convert this into a table let's do that hit okay we are good to go let's change the name for now this is going to be dates or calendar table and then the column name is going to be date and then the data type this is going to be just state bingo we are good to go my friends we can also enrich this table we can add more columns let's do this real quick add columns date here so let's select here perfectly fine let's keep adding more columns here month the same thing that's it here name of month and then let's keep adding a couple more columns quarter perhaps quarter of a year there you go and let's keep adding one more name of the day as you can see we are enriching this calendar table with just one click so we are good now home close and apply so we are good to go now with this calendar table so it's loading so the next step here is to create a relationship between the fact table and the calendar table that we just created so let's do that we can maximize this let's go over model view and here as you can see we have multiple tables here but the fact table is going to be the tech table and then the calendar table or dates is right here and we need to create a relationship between dates and Tech so let's do that and let's see what happens so it's working perfectly fine from one to many another cool thing that we can do here is to hide the tables that we're not using Apple for example we're not using it so let's hide this Amazon the same thing end date and start date we don't use this anymore in the report Microsoft so let's also hide this table and then Tech yes we care about tech and dates for now if you go back to report the report View we can only see these two tables perfectly fine let's keep moving now let's focus on canvas settings so let's go overview here format and then here let's hit combat settings The Next Step here is to customize this combo size custom here so it's going to be 30 percent bigger okay so 936 pixels here for height and then for width it's gonna be 1664. The Next Step here is to add a theme let's do this I already created this theme so what we need to do here is to select this theme from our files so let's go so let's go overview and then hit this option right here browse for themes so we have a Json file here power bi full course it's right there hit this option hit open and let's see what happens perfectly fine it says file successfully added got it The Next Step here is to insert a power bi background I already created this powerba background if you want to learn how to create power bi backgrounds there are different tools to create power bi backgrounds you can use PowerPoint you can use canva you can use figma for this particular case I used figma and like I said before I already created this power bi background so we're gonna just import it let's hide canva settings for now and then let's select canvas background once we are here let's hit this option for image and I do have it right here hit open and then here transparency zero now we can see this power bi background before we keep moving so the canvas background there is also a color here let's do that real quick and this should be basically right here perfectly fine as you can see now this is looking a little bit better now let's start creating measures we're going to be creating measures here for stock price stock volume and other type of measures as well so let's get started with stock price let's go over here right click new measure the first measure that we're going to create here is going to be called Average stock price and this is going to be just on average good to go and let's keep creating measures here the next measure that we're going to create here is going to be average stock price for Apple so let's do that right click new measure let's approve these changes perfectly fine now let's create the same measure for the other two companies this is for Amazon we are good to go copy this again Ctrl C right click new measure and this is going to be for Microsoft [Music] see what happens perfectly fine another mission that we want to create here it's gonna be previous year average stock price let's do that let's approve the changes perfectly fine let's keep moving let's do now stock price year over year growth we're gonna be using here variables if you're not familiar with variables I'm going to share with you a link stop this video and check that tutorial out foreign changes and let's see what happens perfectly fine so the next step here is to store these measures in just one folder let's do that hold down control and let's select every single measure and then let's rename this folder let's call this stock price another cool thing that we can do here is to create a table to store measures so let's do that it's called these tax measures perfect and then let's move these measures into this table they are there right now and then you can get rid of this column perfectly fine let's go back to the report View stock price is right there and now you can see the measures that we just created there so much better okay so let's keep going folks so let's keep moving let's create more measures here and this is going to be for stock volume let's do that right click here again new measure [Music] and let's call this stock volume [Music] let's approve the changes good to go let's create another measure here previous years stock volume right click let's approve these changes let's keep going here let's calculate the stock volume year over year growth right click again new measure we're going to use variables here again okay so let's approve these changes and let's see what happens it's working guys so let's do the same here let's create another folder Here For Stuff volume hold down control and select stop volume here so it's working perfectly fine let's go back to report View and now this is the time we're gonna start creating here our visuals right click add visual I'm gonna be adding here a an area chart for axis we're going to be selecting here the date column from the calendar table and then for y-axis we're going to be selecting here stock price Average stock price there you go we can also rename this if we want let's do that Average stock price over time [Music] we can also add a divider here this one right here the spacing tank we are good to go so we can also add here for the legend let's add here company uh-huh and then we can also play with this feature right here [Music] for the legend this should be Top Central so we can also remove here the grid lines we can do that and then for the x-axis we can also remove here title the same thing we're gonna do for the y-axis okay so this looks so much better perfect so let's copy this Ctrl C Ctrl V over here and let's do the same for stock volume so let's replace here the measure and let's go over stock volume start volume right here and then let's change this visual and with a cluster column chart we can rename this title so we can update here the title name as well so let's change the title here this is going to be stock volume over time volume over time so let's keep moving folks right click here add visual and we're gonna be adding here a table let's select year we don't want to add an aggregation here perfectly fine and then the next step is to keep adding more measures here stock price this is going to be stock price average and then let's do the same for the previous year Average stock price so one right there and then the other one is going to be the growth stock price this one right here and then we can keep customizing this if we want as well we want to make sure the stock price currency here two decimals and then let's do the same for the other ones as well perfect so let's keep editing this foreign [Music] so this is going to be the title [Music] let's add also here a divider spacing 10 pixels you can also keep customizing this if we want so let's call this just so we have more space there okay so that's perfectly fine so the next step here is to add conditional formatting let's do that right click conditional formatting icons and here let's select the style so let's do for now this one right here and then let's keep playing with this this is going to be minimum number zero this should be number as well if that's the case just give me this icon right here if it's greater than zero this should be number and list that probably [Music] ten percent or 0.1 just give me Amber here and then for the other ones right here this should also be number maximum number in that's it okay but before we do that real quickly should be in the middle and then write off data hit okay and let's see what happens perfectly fine my friends it's working and let's keep doing this for the other visuals you can copy this if we want Ctrl C Ctrl V over here and we're going to do the same here for stack volume so we're going to be changing here the measures this is going to be stack volume perfectly fine and then this is going to be also previous year stock volume and then this is going to be year over year start volume okay so we're gonna make a couple of changes here and I want to show you why as you can see stock volume this is really hard to read so we're gonna customize this okay we're gonna give this a better format so let's do this real quick so we're gonna create another measure here new measure and let's call this format stock volume okay check this out and then we're gonna use the format function here stock volume here because we're gonna give this a different format and here we're gonna be using the information that I'm providing here check this out right here let's approve the changes and let's see what happens it's working and then let's do the same here for previous year stock volume control C right click new measure check this out Ctrl V and then this is going to be previous year stock volume previous year let's approve the changes and we're gonna be good to go you might say now why is it not changing well we need to update the measures here instead of Stack volume we need to replace this with format stop calling check this out this looks so much better now and then for previous years of volume let's do the same have more space here and it's better so we can read this better and then for for the growth we can add here percentage and also we can make a couple adjustments here to read this better so this is going to be just stop volume okay and then previous year it's gonna be also just previous year start falling so we can delete the other piece right here so we have more space and then stock volume area growth let's do the same just year over year growth perfectly fine and then the next step here is to add conditional formatting as well so let's do this quickly right click conditional formatting icons and let's do the same it should be right of data middle the icons they're going to be basically the same here and then the rule is going to be the same number minimum zero here and then number greater than zero number and then this is going to be 0.1 number 0.1 again number here and here this is gonna be maximum number again so let's let's see what happens hit okay fingers crossed boom it is working my friends so let's keep moving The Next Step here is to add a multi-card for a year over year stock price this one right here so let's do that real quick right click add visual and let's select here multi-card it's gonna be this one right here let's select here for dates here and right here again don't summarize perfect we only care about growth here so what we need to do is we need to create a different measure and this is going to be a fun one right click here new measure so we're gonna be using here a popular tax function called unitar it's a year over year growth percentage and this is going to be green and red and you will see why okay so we're going to use variables here let's call this green circle and then here unit chart so the unit chart for green circle it's going to be 12 89.94 and I do have this information as well I want to share with you bar here let's do the same here for red circle okay unitar here as well and then this should be 12 89.92 return if stock price year-over-year growth if this is blank just give me blank okay if not we have a different condition here so let's do this real quick if this is if stock price year-over-year growth is less than or equal to zero this is going to be a red circle the variable that we just created okay and we can also customize this circle so next to the circle we're gonna have the percentage the year of year growth so we can apply here this a couple of details here okay so let's do that check this out this should be the year over year growth so this is how it works if stock price year-to-wear growth is less or equal to zero just give me red circle but next to it we should have the stock price year over year growth and we need to customize this as well this should be round okay and this should be times a hundred we need to run this to two decimals close parenthesis and then we also want to have here the percentage symbol there you go and if you want to learn more about this methodology I do have a tutorial about how to build this I'm going to share with you the link as well so you can follow along okay all right so let's keep going here if and we're gonna copy this okay I'm gonna make a couple of changes here if this is greater than zero right so give me here the green circle and then this is basically the same so I think we are good here and then else just give me a blank okay perfectly fine my friends let's approve these changes and we are good to go so let's add this measure right now it's under stock volume for now and this should be this one right here check this out so this is looking better [Music] and it's right there huh and then another thing that we can do here is get rid of here that looks so much better and then we can also add a title here stock price year over year growth so let's do that this should be Ariel 12 Center and then we can also add a divider here should be this one right here and then this should be 10 pixels facing let's keep going folks so we need to adhere the new car visual with four measures let's do that right click let's go over here again this is the new car visual and then we need to add here the measures stock price Average stock price and then here it's gonna be Microsoft first okay and then right here let's keep going Apple let's keep going here Amazon perfect we can make a couple of adjustments here real quick over here and then what we need to do here is let's go over shape run the rectangle call out thank you recall out let's select 18 and this should be Arrow okay so let's keep working on this we can also add here images for each measure let's do this real quick Average stock price image here they should be fit and then I need this on the left side for now this looks fine let's keep going here Microsoft let's do the same this one right here foreign let's start with 50 for now perhaps 40 here that looks good let's keep moving here Apple let's do the same here for Apple this is gonna be this apple right here it's lower right there left side and let's get started with 50 for now and let's see what happens perhaps 60 perhaps 70. aha let's keep going guys Amazon for this we're gonna be selecting here the Amazon logo as well to be left they have 60 or 70. let's check our notes real quick so the next step is to add a period slicer let's do that right click add visual and this is going to be a slicer and here we're now reselecting the date column from the calendar table perfect and let's make a couple of adjustments here as well for values let's select here White and then for the slider y here as well for the title let's add here period and then this should be also White and let's get and let's get rid of the slicer header here okay perfect let's keep going folks so let's select here here instead of date this is gonna be a year and then here we're gonna be selecting drop down for the background here this should be White and then the title this should be ear perfect we can also add here multiple years if we want perfect and then company let's do the same here for company here and then here we want to change also the tile should be Compton perfect let's keep working here let's add the clear all the sliders feature as well this is the picture and we can make a couple of adjustments here as well [Music] and then the Border this should also be white perfect and then we can also customize here a little bit more on Hover and then default let's make this adjustment real quick background this should be okay perfect and then I will go back to hover again perfect this looks so much better okay good so let's keep working folks the next steps here is to add a title and then our company low let's do that right click here stock performance over time four three us companies check this out we can also add a block here and then let's do the same here for our company my company here this is just as a reference as you might have noticed and then you can also do 16 for now and then let's add our logo here perfect hold down control [Music] and let's do something really cool here format align Center [Music] perfect all right so this looks so much better guys and let's play with it hold down control let's clear all slicers let's remove this aha this looks so much better let's keep playing with this real quick check this out so it's working right and then another cool thing that you can do here is let's remove the grid lines here as well we don't care about wheat lines here and the same here for the other one perfect this is awesome and let's rename this page Tech there you have it my friends let's take a quick break and then we can go over the customized date slicer or filter see you soon thank you for your patience we are back now and this is the time folks to start creating our customized date slicer or date filter let's do this this is the time folks we're gonna start creating here a custom period table I'm gonna select this information right here and once we are here let's call this table custom field once we are here Ctrl V let's delete these two columns let's add a new row here and this should be seven days and then the header should be period you can also add another column here it's called this order and then we are good to go it's loading Perfect The Next Step here is to go over the table View so let's select the table that we just created custom period select the column here the period column and then once we are here let's solve this column by order if we go back to Power query so let's go back to the custom period table source and here we need to add the order [Music] double click here and then here we need to add the order forgot to do that perfect let's hit okay we should be good to go close and apply let's go back to report View so here is our custom period table The Next Step here is to let's check our notes here to create a new date stable so let's do that scoreboard modeling here new table and let's call this table new dates we're gonna use this function called this distinct and here we're going to be referencing the date column from the color done table hit OK and let's see what happens new dates apparently this is working perfectly fine let's double check that new dates here we can also customize this this is just short date so we are fine go back to report View another important thing here is to check the mono View so we want to make sure that this new table the the new dates table does not have a relationship with the fact table and this is the case right we go over here there is no relationship so that's good the calendar table yeah there is a connection here between the calendar table and the fact table so we are good now let's go back to report View The Next Step here is let's take a look at our notes to create a video filter measure so let's do that right click new measure so it's loading let's call this measure period filter and here we're going to be using variables this is going to be selected period and here we're going to use the selected value function here you go this is our first variable the second variable here is going to be last seven days so last seven days and here we're gonna use the max function so we're going to be selecting here the date column from the calendar table and this should be then we're going to use filter here the table is going to be called the new dates table that we just created here and then for the filter we're going to use the date column from the new dates table that we just created this should be less than or equal to today and this should also be greater or equal to today minus seven days if that makes sense so let's close parenthesis here perfect we are done with this variable so let's keep working guys we're gonna copy this variable Ctrl V here and this is going to be 15 days and here as you might have guessed it should be 15 days let's do the same here for 30 days the last 30 days I'm going to replace Q7 with 30 days keep going Ctrl V again and this is gonna be six months so let's add here six months then as you know six months we have 180 days here The Next Step here is to get another variable for Year One control V let's call this Year One and then you know that we need to replace here 7 with 365. let's keep going but before year one let's do year to date okay I was forgetting this so let's call this year to date so equal here the max function again the date column from the calendar table and then this should be so let's keep working on our year-to-date measure here we need to select another function called dates year to date you might know this function and here we need to reference the date column from the new cut in that table that we just created this one right here close parenthesis so we're good so let's keep working here so let's copy this variable and here we need to close parenthesis for Max so let's do that and let's keep working here Ctrl C and this should be year three so this is going to be for year three this is 3 times 365 which is 10.95 let's do the same here for year five Ctrl C and then Ctrl V this is gonna be year five and here for year five this is gonna be 365 times 5 which is 1825 and then finally our last variable this is going to be called all in so this should be equal Max so the max function here the date column from the calendar table this should be in and then the all function here as well and we need to reference the date column from the new calendar table this one right here close parenthesis [Music] return for return we're gonna be using here this popular function called switch so this is the first variable that we just created if selected value is SQL to one just give me last seven days so you will see here the pattern and then if this is equal to 2 this should be the last 15 days perfect so let's keep working here for three this is gonna be the last 30 days thank you and then let's make this an integer so we need to use this function here close parenthesis and we are good to go let's approve these changes perfect what is next let's take a look at our notes real quick so we need to create a period slider and then apply the previous measure tool visuals so let's do that before we do that let's duplicate this tab and let's call this Tech 2. this is going to be take two perfect and let's play here in this top let's make a couple of adjustments here so we have more space scroll down okay perfect so let's copy this Ctrl C Ctrl V once we are here what we need to do is let's uncheck date and let's use period from the custom period table and then we can play with this as well you want to have here tile [Music] and then for values this should be actually black [Music] so let's keep walking here [Music] this looks so much better now we are almost done folks this looks so much better the next step here is to apply the measure to these visuals so let's do that [Music] like the visual here go over filters and here what we need to do is we need to grab the period filter measure and place it here for this visual so once we are here this should be equal to one and then apply filter so if we start applying the features here some things are working properly let's double check the code real quick video filter I think I know what's going on here we should have here for nine this should be all in and now this should work perfectly fine so let's see what happens check this out still not working and then when we start applying these filters something unexpected is happening so this is real live my friends so let's see what's going on here see details it says the calculation error in measure tax measures video filter consider using the value or format function to compare ones one of the values okay I think I know what's going on here let's close this let's go back to the measure that we just created so it's right there the measure is fine but apparently the order column from the custom period table is text so let's double check that so let's go over home here and then let's open power query that's why the message is not working so once we are here let's select the custom period table if we take a look at this column right here this is text we need to convert this to number so let's do that real quick whole number perfect lost and apply fingers crossed my friends and this should work fine now this is working my friends so let's check this out 15 days one month five years three years it's working now let's apply these changes to the other visuals period future and this should be is one apply filter boom let's do the same here for the other visual here and this should be is one apply the filter again and the same here for the other table this should be is one apply filter and let's do the same here for the multi-carb visual check this out apply filter boom perfect so the next step here is to create a day filter measure and apply it to the date and year slices so let's do that real quick right click new measure date filter equal if selected value here again if this is equal to nine just give me one if you're not zero perfect so once we do that the main goal is to inactivate this filter if we are playing with the this periods right here but then when it's Max we need to make this active so let's select this right here and then what we need to do here is the filter over here only give me for activity slicer when this is one so what does it mean if we apply filters for the other periods so this is inactive as you can see the main goal is when this is Max this should be active so what we need to do next is let's go over format edit interactions and as you can see we need to filter this boom so now if we check this out seven days inactive six months inactive but then if we select Max this is active and then you can play with this filter see right there that's how it works and then let's do the same here for the year feature perfect and then let's select year date filter here as well and then is it should be one apply filter check this out one year you cannot see anything here Max let's go over here and now you can see years so it's working perfectly fine and then you can go back to format if you want and then let's stop applying interactions and then you can also rearrange this a little bit better if you want it's up to you so the next step here and the final step here is to publish this report to the power bi service since we like this one right here tech2 so let's hide the first one right here the first page and then as you may have noticed there is a button here called publish save and then let's publish this to this workspace so it's thinking got it let's open power bi service perfect we are here in power bi service let's open the report fingers crossed awesome it's right there you can set up playing features here if you want and you will see this is changing perfectly perfectly fine then if you want to share this report The Next Step here is to share and then let's start applying here email addresses and you are good to go alright my friends there you have it I hope you found this tutorial helpful if so as always please give me a thumbs up share with your friends and of course don't forget subscribe if you don't want to miss anything thank you guys for your time and see you in my next tutorial [Music]

Channel: Nestor Adrianzen

Views: 2,712

Keywords: power bi full course, power bi from zero to hero, master power bi, power bi tips, power bi training, power bi for beginners, power bi tutorial, power bi, power bi tutorial for beginners, power bi dashboard, learn power bi, power bi course, what is power bi, power bi desktop, microsoft power bi, how to use power bi, data analytics, power bi visualization, power bi tutorial videos, microsoft power bi full course, introduction to power bi, power bi dashboard tutorial

Id: 7a-mB7Xw7ck

Length: 76min 0sec (4560 seconds)

Published: Sat Sep 09 2023