Week-1 Dynamic Power BI dashboard with Copilot | How to create Power BI Dashboard

thanks for clicking on this particular channel  so if this is the first time you might want   to subscribe to this particular YouTube channel  because what we do here basically is 100 practical   you know teaching you how to create report in  dashboard in a very unique way that you might   have not even thought of before so I'm going to  show you how to create everything you see right   here today so now you look at this Dynamic tooltip  we have right in this one of the amazing part of   this Dash but is this particular part right here  where you can select top or bottom whatever you   really want so what am I talking about right  here we have buy product name and by product   group and by sales person now we are seeing it  by product group you can click on salesperson   and that shows you the bottom salesperson and  if I click on the top sales person here that   gives me the top four sales person what if I  want to increase it and go beyond four we can   have it on six or seven eight nine ten of the  twelve I presume or fifteen now can you see that   so we can reduce it down to three and we're seeing  just the top three sales person very Dynamic very   interesting to look at okay one other part you  might want to be interested in looking at is this   particular part right here where we have this  sales person and this is exactly what we have   right here we can scroll down and see more of  them so now you can choose a particular manager   by clicking here that gives you the manager  so under this manager we have some supervisor   under them so which supervisor you want to see  if there's this one just go ahead and click it   and there you go click back and click on this  one then you have it back to the default state so right here this view is different because  we have it on power bi service where we don't   have to click on our control key to navigate  through the navigation we have right here and   it's very very interesting so you might  be interested to look at how to get this   built if you want to do that subscribe  like leave a comment let me know what   you feel about this because I'm gonna  help you to build this from the scratch let's go ahead and bring in data set you know  creating the dashboard you've just seen click   on this particular part where it says get data  and I'm not sure if my data is Excel War book   or CSV I'm going to click it out of it and  it to surely give me let me go to the file so I'm gonna have this one open okay yeah I got  it so it's Excel so I'm gonna click on this one   and get a sales data just click on open and have  it in so here we have it you can click on it to   just take a preview of what the data is like  and this is what it is I can just go ahead and   click on this part and check it for me to have  access to this particular part so when you click   on load it's going to load this straight up to  power bi for you we don't want that so what we   want is to clean our data right and transform  it so go ahead and click on transform data if you look at it the tie is nothing  anywhere as clean as what we want to   use so what do we do for the first time  so there are several ways to actually you   know clean up null and all of that so the  first thing I do when I receive data from   clients and stuff like that you submit to  check around to see if this particular you   know right here I know for sure this one is  going to get tough I don't really want this um this is the header here so this data is not  really important so it's just a date that someone   kept in the file so for this null I want to see  if this null cut across all the columns okay I'm   really sure that they all cut across all the  columns so I just have to remove them right   so uh in case I have null within the data within  the rows of the data in all columns I don't affect   them so what I do to get this off is for me to  just okay I want to use um remove rows here and I   will go to remove top rows so select to remove top  rows you are going to see this so what you need   to do is to count how many rows you want to remove  now this rows would be removed across every single   column right is one two and three so I'm gonna  impute three right here and quickly I click on OK   and that gives me this so the next thing  nice for me to promote the header I can   click here to actually go ahead and say  use first row as header can you see it now   so that promoted header for me you can click  it right here as well so there are several   ways to do one single thing in power query so  it depends on how much you know about bar query   so quickly after you are done doing that so the  next thing you have to do is to check around to   see if the data types are some kind of right so  in case you want to actually do automatic check   just do Ctrl a and have all of the columns  highlighted so go to transform here you're   going to see where it says detect date type so  just click on it it's going to help you to find   and give you a good match but sometimes I always  love to do it myself because it's my get it wrong   you know I just believe in what I do so not what  the machine is doing for me okay let's say we are   called and we're good to go that is our first data  I am going to call this one my fact table facts   table so I believe you know what fact  table is all about fact table is where   you actually record your daily transaction  into so it has a lot of duplicates and all   the keys here are foreign keys so let's  get this down to our power bi Dexter so it's loading just give it some time all right we have our first table here if I open  it right now those are the columns contains and   then we can come here and preview the data that  we have here can you see it now just give it some   time all right sure there we go so this is what  it is so we're going to be dealing with Revenue   like analyzing and trying to show visualization  about Revenue how our company is doing so um if   you look at this part here we have the quantity  and we have the unit price so let me go here uh   we are going to have um an aid should I call it a  net something that's going to support us the right   Decks that is the co-pilot in power bi so if I  click here on the quick measure I have it I have a   video about co-pilot please check the video about  copilot in this particular platform and watch it   and see how to turn on your co-pilot right in  power bi desktop so now we have a co-pilot here   with what uh it's just general copilot and this  is where you can actually type in whatever I want   to type in but one thing about copilot is that you  must understand the analytic jargon before you can   use it not just the analytic jargon this tool  you're using specifically you know um Power bi   right now so so you must at least know the jargons  so that you can actually give the autopilot the   right language that it will understand so what am  I talking about so what we want to do now is to   calculate the total revenue and we have a column  here that actually supports total revenue one   column here is for quantity and that one here is  for unit price so if I come back here now what I   want to do is just to come here and say okay you  know what I want to calculate my total revenue   how do you think it confront this can I just say  calculator for me so if I just go ahead and do   that it will not it's not going to work let's  try that and see so I'm gonna say calculate um okay what is the total revenue so it doesn't  know anything about what is total revenue if   I go ahead and say generate for me now there  is no way I could see anything no suggestion   it can generate any code for me because I  don't give it to what will definitely you   know make it work let's go down see can you see  it now it just went into the fact table and sum   up my order number and that is crazy right so  let us just give it what will definitely work   so right here I'm gonna have this removed  and what I'm going to do now I'm going to say   um we have what we call iterator iterator is a  function in power bi that has to do with the X   function some X average x max X and the  rank X so I'm going to say iterate true or iterate true the fact table and multiply units price so give a space price oh okay just give me some time so  unit price buy what by quantity order   so quantity so this time around  we have given it to what it is   that is looking for we can go ahead  and generate a new you know decks okay fine let's scroll down and click on ADD  so if you know about your Dax before once you   see a correct that the copilot has generated you  would like wow this is what I'm looking for to say   okay let's kind of Zoom closely to the screen and  see what it is that we have so can you see it now   uh let me just do some adjustments here now this  is what it is so we have some kind of iterating   through this particular fact table right here and  we are doing this multiplication by unit price and   the quantity and it's exactly what it is so  you don't need to write this all by yourself   so I told you we have um what we definitely  Aid us to do things with is so go ahead and   change the name in case you don't want to use  measure so measure is not the name we should   use so I'm going to go ahead and change this to  Total cells or I'm going to use Theta Revenue so to type Revenue here so now we have just got  in our first measure using the copilot I told you   if you don't know how to turn on your co-pilot  I have a video or watch search for video for   co-pilots in then you're gonna find it okay now  that we have achieved our calculation using the   copilot let us break in our budget and do more  complex you know calculation with the copilot   so right here we have what I  call human friendly you know   um they call it human friendly data so it's about  our budget and what you can see we have our budget   for our our transaction so it it I think we have  12 columns here I think overdrive column like uh   that are like 14 columns here we have to transform  it into a column so it's in a row and it's human   friendly so the image doesn't understand it let's  see how we can transform this inside Park area   so did it taste still in Excel just go ahead and  click on this and I'm gonna click on budget here all right so here we are we have three  years of data 2019 to 2022 or 2021 right   here rather so I'm just going to deal with  my two 19 here so click on it and have it   highlighted so we have lots of blends of  course so we go ahead and transform these okay um there you go so if you look at it  now there are some couple of blanks here   so we have blanks here and that we have the year  which is this particular year we have right here   so if we remove this particular null it's going  to affect this here but how we're going to solve   this situation don't worry about it we're going  to fix it later so now the next thing is the   grand total here so we don't need to keep this  grand total because when we actually you know   turn this and have a single column for our values  we can always sum that up and get our grand total   which means we have to remove this as well so how  do we go about all this the first thing to do now   is just for you to say okay fine or for this null  here I'm gonna click here and say for null I don't   want to see the null I can just scroll down and  for my grand total I can click on this one and   if I click here I have that cleaned up for me can  you see that so we need to promote the header we   can promote our header using this use first row  as header here and this is exactly what we have   oh let's scroll a little bit to this end here and  if you look at this you have you know this column   I think let me just remove this first I'm  gonna remove this we should have done this   before now then you understand what I'm doing  um let's do this so here we go so remove filter   so we are back to the previous View I just want  you to understand what this column here stands for   this column you see right here is actually under  total for each rows do you see that now so on the   total when you close this plus this plus this and  other columns in the same line that gives you this   one so we don't actually need it so we need to get  it off as well so the last thing we can do is to   actually highlight it and click on remove and that  remove this one for you we can just search okay uh   just make sure you click on this one here let us  remove this one then now you can remove this one   remove it so if you are done removing that one so  we can now scroll all the way back here now and   deal with the null and the grand total just like  what we have done before click on this and have   this one removed scroll the way down and move this  one off click on OK then you just have to go here   now and make sure you use first row as header  and that has given you exactly what you want   so we're going to actually make sure we transform  this into one into two columns like um okay I   think it's going to be three columns into three  columns so one column for the um for the sales   ID sales uh personality SP for salesperson  ID under column is going to be for our month almonds and we're gonna have column for the  value so one thing you have to look at here   is this particular nose don't actually bother  about this nose here null is close to zero if it   is number so you don't have to replace it except  you know the actual value that should be in the   null so we are not going to bother about that  so all we have to do right now to do this with   is to select this particular current column here  and on pivot it and all of that so let's do that   so remember you have two options you  can do this sender just come to the last   you know column here then hold your shift key  and click on this right click and you click on   on pivot column and it's gonna give you this so if  I have this remove and do it the other way around   I'm still going to have the same thing from in  order for me to just scroll to that other end   I can just select this one single column called  sysid I want to keep my sales ID I don't want to   actually you know transform it to the other level  so I can just right click here and you can now   see on pivot order columns right so can you see  it now we have the same thing so right here I'm   gonna just some kind of come here and double click  on this and just say this one is my our budget   so can you see that so let's go ahead and change  the attribute here you can double click on it and   change this one to month and that is cool so we  change it to mount so um one thing we can do to   bring other we don't we have three years of data  19 20 or 21. so we can do the same thing we have   done right now to actually bring in our you know  our 20 and our 21 you know data right here and   after we have done that the next thing we can  do uh would be to some kind of append right so   I think you know about appending data so I'm  talking about this append query here so we   can use the append query to actually bring our  you know data into one single table but do we   really have to do that I guess no we have a better  option so let me show you the option we can use   so in this option let us go to this particular  navigation here so if I click on navigation   now I can click on this particular icon or this  particular settings icon here so what this will   give us is the preview of all the years we have  can you see now 2019 20 and 2021 right so what I'm   gonna do is just for me to have this particular  one removed from here I'm gonna click on delete   so I have just gotten it deleted so let  me click on my source now and uh inside   my cells as you can see this is the data for  me to get my ears so I'm gonna keep the name   right here and those are my three years of data  if I click on this you can see the reflection   here the data for this you know very one this  table is for 2020 and 2021. so for this item   the sheath and this one here we don't actually  need them so I'm gonna select the tool I need   so Ctrl and select this one I don't want  to select this one as part of it I'm going   to right click and see remove other columns so  that will definitely keep these two columns for   me so now instead of me to just you know um open  this table I can go ahead now and click on this   and once you have done that let's go down  and look at this part here where it says use   original column name as prefix so I want to just  uncheck it I don't want that I can now click on OK   so now because we we have some kind of left  the last you know step to the above steps uh   is actually asking us if we really want to do  this yes of course go ahead and insert this   okay now we have all our data here  to prove that let's click on this   one as you can see 2019 down  to 2021 and that is beautiful all right let's scroll down and  look at this if this is working   no something happened let's check it  out so the query is have been broken   down to this particular change type right here so  what do I need to do right now I actually need to   start removing you know those steps we can fix  it but it's going to take us a lot of time let   me just do what you would understand I'm gonna  have this one removed remove this one now remove   this just keep removing them remove remove and  remove and stop at expand data so now finally   we're here there are Taxman data so the very first  now I'd like you to do is for you to scroll to the   right hand corner at the last column here and just  go ahead and remove this column 14 where we have   the total for every single years right we don't  want that so delete key on your keyboard will   definitely get that off for you so let us go to  the left hand corner right here okay now it's of   not difference it's just a little bit different  from the previous one let's see this so for now   we have budget we have salesperson ID we have  grand total and budget so if I click here so I'm   gonna say for null just like what we have done  previously I would have that removed so scroll   down right now if I get my grant my budget off  yeah we don't need the budget we don't actually   need a grand total for the sales person ID because  we want to promote the first sales personality uh   we don't want to remove it for now let's just  do okay here now we have this right so for we   to use this cell header so what we're going to do  right now is let's go with this one click on this   particular useful straw as header here and that is  cool right so we can now come here and have this   one removed by clicking here so we go all the way  down and click then go ahead and remove that so   what about this this is awesome right okay now it  is time for me to actually you know do the neutral so we're going to transform the 12 columns just  like what I've done before you can hold this   or you can decide to like okay those are the  visible one right now just Ctrl click on this   one and transform all that 12 columns right  and just go ahead and on pivot other columns   there you go we're gonna have this so the  very first I'm gonna do now is to like okay   I'm gonna name this one to month month here  and right here I'm gonna just name this one   to total budget I'm just going to name this one  to budget um jet here we go nicely done right   all right oh the next thing now is to just  go ahead and close and apply and that we   have to get this to our public stuff once  more okay we're right here and we have new   table with uh new data here so if I click on it  we have this uh we forgot to name it to budget   we just have 2019. I'm sorry but we can do it  right here I just want to say budgets foreign for that okay here we go so budget or you can name it budget table or whatever  you want so here we have our budget right here   okay nicely done we don't have the total  budget right now what we have right here   is just under implicit measure for our budget  which is the uh budget here so let us tell   copilot to do something for us again um I'm  gonna say sum so let's just do some d budgets color okay generate the code here and if we  scroll down let's see what it is that we have   so now can you see it now some and that is  exactly what we want we can just click on ADD so here we go we name this to our  total budget so everything looks great   so we've got no second measure okay before we go  further let's do this uh if I go back here now   I have my measure here and I have another  measure right here so this is not something   I always loved to do I don't want to keep  my measures inside you know fact table or   dimensional tables whatever so what I need to do  is just me to create a disconnected table to store   all my measures right in so let's do that so the  first thing you do is go to home and you click on   enter data if you watch my video videos often  time you'll see that I always love to do this   I am going to call this one my table measure  oh I'm gonna do this I'm gonna say uh measure measure table so we hit the enter key   so as you can see we have our emergency table  here so it just has one single column so this   is what it is so try to remember the name so  go ahead and click on the measure you want   to move so once you click on it just please pay  attention to this particular part here where it   says name this is the name of the measure we've  created and the way it says home table this is   the table where the measure is sitting right  now so we want to switch it to measure table   and that will just go into our measure table  and we have one single measure there so let's   just go ahead and move this particular one so I  just want to go in from budget so move it into   the measure table and that is great so we've just  moved them and this is exactly what it is so you   say it's connected table it has the relationship  with any other table we're going to bring in now all right let us look at what we have done so I'm  going to bring in my total revenue [Music] so I   want to create a table for my total revenue it's  a table so I'm just gonna do this so here is it   um so let's look for a dimension here to  break it down by so let's go for supervisor   so we need so some kind of swap this move this up  or we can just deselect this and move this down   or no so just move the revenue down here we  go it has been swept um I want to go ahead   and format this is the table we have to go  to view and from view we can click on formats   so on the format we just have to crank up  the value let's put the value on 20 now   that will be nice and for the header we can  trade the header to be like uh 17 okay fine   we can see this right way all right so we want to  compare this with our budget let's just look at   if we add our budget to this what is going to  happen so go to the measure quickly and bring   in it okay can you see this now something  happened right here so we can compare this   because there is no relationship between  what between our budget and they you know   the fact table so can we just create that  for the time being there so we click here   and here we go so you can just go ahead and close  this to pay way for visibility so now we have our   budget here so we have the fact table so there is  no relationship right so how can we do this now   okay we have the salesperson ID here and if I  come here we can find the sales person [Music]   uh we couldn't find the pit this is personal  ID right here let us go ahead and check   it out and see where we can find this is  personal idea so click on your fact table   and okay since pressing key we have the same  spacing key here in the [ __ ] table so let's   go back here and look for that sales person  key okay just pressing key link this to our   salesperson ID here let's see what we're gonna  have so now it's telling us that you're about to   create many too many relationship so for the time  being let us just agree with this for now it's not   the best way to do this but we can just use this  for now all right let's go ahead and check this   out I think this has fixed this first okay we  get to do something very much you know amazing   but the problem we're gonna have is when we try to  create something different like if I decide to use   um this metrics here for example and I bring in my  ear here if I slice by ear there will be problems   so it's gonna break in the year so for that we  need to create another table which is a table   for either table and um instead of me to create  many-to-man relationship just like as I have right   now we have to do what we call normalization  normalization is for you to make sure you know   what can stand on their own should stand and leave  the table so what I'm talking about right now   um is this so in this table we have these  sales person right here in this particular   table so we have salesperson we  have six person ID so we have   manager so we have manager and we have supervisors  right so they can all be in the same table so   which means we are going to create a table  for this and we're going to play it all from   here and what will only link them is the key the  foreign key from this table and the primary key   from this particular table right here now after  that now for it to actually slice by a year you   know and all of that so we need to create a date  table so we're gonna have on that table here where   we're gonna have our date right here remember  our date contains foreign keys right sorry   um primary keys because because they are unique  while from here the date will definitely have   some couple of duplicates so if you agree with  me let me take you straight to this step we're   going to follow to actually have this created  first of all let us normalize or fact table so go ahead and click on transform here okay we're here in the fact table the very first  thing I'm gonna do is to some kind of duplicate   this um some people would do reference let's  do reference so you can do reference you can   do duplicates it depends on which one you want  to use so when we do reference now and I want to   for example uh delete this I wouldn't be able to  delete it because it has connection to this one   so with our reference there are so many ways to do  it so we can decide to manually get what we want   from here like the sales person key we want to get  six person key uh we want to get a salesperson we   want to get a supervisor as well and we want to  get manager so another way to have the same thing   done is for you to actually use your home and from  home you go to where it says choose columns uh we   have two things here if I go to go to column here  now so what I have on go to column is to actually   navigate to a particular column so let me just  show feel why that is gonna happen instead of   me to start searching for a particular column from  here you know I have to start scrolling slowly and   sometimes I'll even get past it so the only thing  you can do to get to that column with ease is to   go here and use this go to column here and ASAP  you want to go to quantity click on that and click   on OK and it jumps you to what it jumps you to  where that particular column is that is what that   is used for all right if I go to choose column  here now I click on choose columns so I have all   the columns available inside my table here now  can you see it now so I can deselect everything   and I'll be so I'll be like okay I want to keep  I want to keep what I want to keep this sales   person okay I want to keep sales person I want to  keep supervisor and I want to keep manager you get   it so for other ones I don't want to keep them I  can click on OK here and they have it you get it so now that we have actually gotten this  table duplicated the very first thing we   need to do is forward to some kind of make  this particular table to be some kind of   unique so we need to have a unique value in  this particular table so we need to have one   our salesperson one supervisor and one manager  then one single ID so we shouldn't have anything   that has to do with the duplicate right here  so we have to remove all the duplicates so   let's see how we can do that so the very first  thing you have to do is to actually you know uh   go to this column here or eight of the column  right click and you just say remove duplicates so now we have the unique list of ours this  uh version but where is this particular null   coming from so we have to remove it so if we  must remove the null from here we have to go   as well into that table and have its null  removed so click on OK to remove the null   so we don't actually need it so we're going  to head up to our main fact table right now   and have the node right there removed as well  so let us some kind of look at any column we   can click on to give us that particular  view of null and here we have a lot more   so let us load more and I love to check it if  this particular null cut across every single   column then I'm going to have it removed so  as you can see we have this node here so to   do the check click on the knowledge loan and  click on OK let's see what we are going to have all right as you can see we have the null  everywhere there are no values in between   all the columns so what we can do now  let's just go ahead and remove this   and we can click here and just some kind of load  more again and we just go ahead and uncheck the   null and click on OK and that will be applicable  to every single column that has the null so here we go click on OK so that will definitely  get the null off so now we are back here   so we have achieved this so what we need to do  now is to just double click on this and name   this one as dim for Dimension I mean I always  love to use underscore or Dimension or sales   awesome hit enter okay let me tell you we  call this particular table um a lookup table   so we can call it a lookup table like look up  like this or we can call it Dimension table Dimension table so both lookup table  and dimension table they are they have   the same name they have the same  name we are actually referring to   this particular table right here so in  this table we have what we call primary so primary key [Music] so primarily while in a  table like the fact table and the budget table   they are actually with our foreign keys right  let's find out what that is let's go right to   this particular you know um that's what I call  it let's go to our fact table so right now we   are inside our fact table you can see the key here  we have reputation of five six five five six five   but when we get to our dimensional sales browsing  table now this 565 wholly repeat itself once okay   and one thing you need to do once you some kind of  normalize your table is for you to go to the table   and remove those columns that I repeat in inside  the flag table so we have salesperson supervisor   and manager I will go here now and uh just click  on this well let's go here click on choose columns   so this time around I don't want to see supervisor  any longer I don't want to see manager I don't   want to see salesperson right but I want to see  my sales person key it's very important I need to   keep that just to connect to this table so I am  going to go ahead and click on OK and that would   actually make that uh those ones to disappear  okay um what just happened to this uh let's   let's find out let's find out okay I think this  happened because I used reference so let us use   under means to do this we are going to have this  problem again so I'm sorry for that let's quickly   go back here now and click on this and quickly  return back all of this now so you have returned   them back and if I click on here all I need to do  now is going to return everything back everything   is going to look fine but we don't need to do this  we are using reference we can definitely duplicate   uh yeah it's fine but I'm gonna tell you first  hand that no we don't need to use you I'm sorry   if you're going to give us that habit of giving us  an error when we remove some of those columns we   don't want from here so instead of first to use  reference let's just go with the duplicate here   so we have the duplicate quickly I'm gonna  just go ahead and say dim underscore sales   person okay fine and quickly we know those  columns that we want to keep is very simple   we can just uncheck all of it and we need a key  we need this we need this we need this we click   on OK and quickly we right click and just say I  want to just keep what uh the let's unique list   of it and that will be done for me let's give it  some time okay we have it already that is correct   so we can now go here now and say okay because  we have gotten those columns in that particular   um in this particular theme sales process right  here what we need to do right now is just to   remove those columns that appeared in that list  so oh sorry click click everything I don't want   to see you you and you and if I click on OK right  now what happened to the reference tool on top of   the disk so it's looking very fun right now so um  here we have this key to be on a whole number so   we have to come here and check our sales pressing  key and make sure it's on the whole number as well   and that is fine so this is our if I click on this  now let's see so here we have the fact table to be   um the table that has to do with  some foreign keys offering keys so foreign keys so the foreign Keys means we have  duplicate of this key just let's just let us have   explained to you so if this will be our you  know fat table what do we call this this is an   effective way as well so what I need to do right  now I can just differentiate this table so it   means I have two fact tables I'm gonna come here  now and say okay I have this one to be fat table so fact table budgets so now budget differentiates   this one and this one is just my  fact table and that is beautiful   so we have just gotten this the next thing  we need to do now is to just close and apply so as you can see automatically power  bi was able to detect that we have   relationship between those two tables and uh  it has been created for us so let's find out   if this is correct how can you find out let  me move this down so your fact table should   be beneath the uh dimensional table so to  find out go ahead and double click on this   so it's going to open the properties for you as  you can see sales person key to sales person key   so one many many one and here we can see Mark  this relationship as active is active right now   and you have many to one relationship so I can  go ahead and cancel that is nice so uh in this   case we don't actually need to link many to many  relationship here all I can do is to right click   and have it deleted so we have a normalized  table right here that we can actually connect   our fact table budget to so all we have to do is  to click on the salesperson ID and take it up to   the sales pressing queue right here and that is  going to give us one too many can you see that   this is awesome so we have one more table to bring  right in here and that is the date table for you   so let's just go back to our view here so it has  been broken we can just click on this and fix this   so all we need to do now is to bring the how I  want to slice this we bring it in and it will   be okay so let's go with the gear so let's go  let's go let's go now we are going to use our   salesperson Dimension table let's go to the  manager or let's go to the supervisors here   and that is on the wrong place so drag it here so  now we have it and everything is looking very okay   if I choose to go to my fact table and bring in  the dates right now let's see what is going to   happen so we have the date here so before  we drill down look at the value we have   they look very correct but by the time  I click on this particular person here   and draw it down at this level the budget is  not more correct so that is where we need to   create another table called the date table so edit  table World definitely handle this let's find out we are right here on the datavio  all you just have to do is just   to actually go ahead to table two  right here and you click on new table so inside the new table so we are going to  create a as in a calendar so I just need to   call this one my calendar so with my calendar I'm  gonna do shifts enter and I want to use calendar   Auto calendar Auto is here so calendar Auto will  definitely go inside my fat table and find that   column and retrieve all the data we have right  there from this tab to the uh the last date so   you can just go ahead and make sure I click  on this short format and do this so the next   thing I can do right now is to add more columns  to this very simple and easy to create so shift   enter at the top of you know calculator Auto you  use the add columns feature oh sorry our function   come right here and put your comma here shift  enter now the First Column we want to add is   the year so we have to close this comma and inside  is we use the ear function and with year function   with the square bracket you're going to see this  particular date inside the square bracket just   like this this is the date here we need to  fade to this and we go ahead and close this   comma again we need to add for month so for month  we can just say months here so month close this   and for month we are going to use what I call  format so format is what can actually help you   to transform you know number into a strange so we  can do the same thing here we bring this and once   I hit my comma How do I want to format my month  so I want the short month close this and I have   this close so I need to format um my month using  the month number to have it from January down to   December so we go ahead now and say months uh  num here we go so this time around we use the   main mod function here all we just apply to it  is a date column close this comma again shift   enter in case we want to use the weekday what  we can do now is just to actually add the week   uh day here close this comma so we use the format  function the format is looking for the date   comma How do we want to format this we use DDD  then we close this and finally we close this   comma so we need to as well format the weekday to  start from the hierarchy without we trying to do   it alphabetically shift enter now we need to get  what weak number so we do this comma and for the   week number we use the weekday function we just  grab the date column and we close this okay the   next one now is going to be our quarter we want  to add quarter that is fine so I wouldn't actually   use qtr qtr for quarter close this hit your comma  key and we can use the format and we use this   comma here so I think this is for slash I presume  so we use the two kill here and we go ahead and   close this so let us find out how this looks  shift enter we need to click uh close our add   column and once we hit enter we're going to  have some couple of columns inserted for us   yeah just give it some time so now we have them  right here I can go ahead and have this closed   so all I have to do to format this one  now is to click on it and come to this   by the glass sort by column here I'll go  to what two months number and I'll do this   so after I'm done with that I'll go to this  particular part here where I have to select   my column so we go here we select the week number  we have it formatted so there is no point in we   showing this particular week number and the month  number so all I have to do is to click on this   week number here click on this I icon here to have  it hidden from the report View so I'm going to do   the same thing to my mod number I will definitely  click on this here I don't want to show my month   number I'm going to have it hidden and that is  exactly what it is that we can do okay one thing   I might want to add is if I want to add some  kind of uh what I call it weekend right weekend   on weekday how do I do that so we can add a new  column to do that if we don't want to add it here   just go to new column here so new column will  be added to this particular place in the inside   a new column what you can do is to name this oh  let's bring it down so let us name this um week and all week day here so I put my  equal sign so I'm gonna use the   if condition to get this done so I'm  going to say if so if what if months so if one number here okay before I do this  I think I'm sorry I should have showed you   something so let us not rush this I'm gonna  just cancel this now so inside here [Music]   so okay we inside here let's do this so  inside here Saturday is seven Sunday is one   so that is under the week number so if we now  want to create for our weekday and weekend now   all we have to do is to follow seven and one  to be uh weekday a week weekend rather weekend   I think I can try a particular function to  do this let me just name this week and all   weekday here so I'm gonna do shifts  enter so um if I say okay let's say   if I think we have to put the equal sign  here look I think the if and the switch   can do it but I'm gonna use this the  if here I'm gonna say if uh week number   here is equal to or we can say if it's if is  in can we use in let me try that in operator if   in so we do this so one and seven  so we go ahead and close this so   if that is true what you really want so  that means is actually weak and else is weekday so let's close this and hit  enter key let's see if this works   okay it does can you see it now so we have  seven weekend one weekend two week day I   think I made a mistake here you can  put my key here and hit my Enter key week day oh sorry for that weekday so can you see it now so what is this  particular in doing so this in operator is going   into this particular week number and search for  one so if it finds one it's going to be true if   it finds 7 it's going to be true if it doesn't  find one or seven it's gonna make it false and   it will return weak you know uh week day yeah so  if it finds both one and seven they're going to   be in weekend and that is exactly what it is  so we are just you know we have successfully   created our date you know table right now all  we need to do right now is to actually come   here and I'll click on these table tools  here and click here and Mark as the table   so we select yes select date so it validated it  successfully we click on OK and that is all we   need to do so right now we have a new date table  we can now go to here under the relationship View   so let's go ahead and close this one all  right so we are going to bring in our calendar   so our calendar is here let's move this down move  this here and now for calendar here we're going to   connect our calendar to our dates which is all the  date here but that doesn't solve one particular   problem that doesn't solve the problem that we are  still going to have the correct value here because   right now we do not have a date column here and  we need to go ahead into Power query to see how we   can create a normal or proper date column inside  our fact table budget let's check this out so so so so click on this and let's extend this  here so it doesn't solve the problem so we   still have this easy right here so we need  to go ahead and fix this right let's do that so to get our budget date let us go  straight up to transform data here so now we can go to this fact table budget here  and now as you can see we have our month make   sure you select a month and hold your control to  select your I think this should be here so I'm   gonna name this one to here so not that of 2019  whatever so change this to here so now select the   month first select this and we have two options  to do this make sure the mount is selected please   so we have two options to do this we can go under  transform and we use the merge uh match here or we   can right click and scroll down you're gonna see  where we have the match column so let's use this so and we need a delimiter here a separator  so I am going to click on this and let me   just use the custom so I wanted the dash okay  now we come here and name this one our [Music]   budgets underscore dates all right  so let's go ahead and click on OK   so let's just click on this particular a b and c  and dates and this one works that is nice so this   is exactly what it is so I think it's gonna work  for you if it doesn't work for you let me know   um after this now what we can do is just to  go ahead and click on this go close and apply   so let's see now how we can actually create  our relationship if it doesn't detect the   relationship Force automatically  we should create it ourselves so it doesn't I can see we are still  having the same value here right what   can we do in that matter so let's go back  here now and run the relationship between   this and our date so drag this  to the date here in our calendar   and that will definitely correct to what we are  looking forward to seeing right so let's do that let's see let's see okay um okay I think I got it now so the reason why  it's not working was because we are still using   the date from our fact table as you can see  uh if I scroll down now we are using the order   date right here so let us change the other dates  and I will click on the calendar table here now   so we can now choose to use these dates and if you  look at it now we only have for this particular   day here so we can decide to remove this and let  us use it on a monthly level if that is gonna be   cool uh let's do monthly level so we click on  demands and as you can see this is very very   correct so any of this supervisor you click  on you're gonna see it on a monthly basis how   they are doing and let us give glory to our custom  calendar table so I believe with this particular   step we have taken right now you will definitely  understand all we have done and apply it on your   own personal project that is just my goal right  here making you to think like an analyst in how   you can actually take data and make it seem so for  the first time what we need to do is to make sure   we get what data clean transformed and create  in a relationship normalize or the normalized   tables so we have normalized our table let's see  in the next spread let's see how we can actually   um denormalize table and still  get what we want from it foreign

Channel: Data with Decision

Views: 7,665

Keywords: data with decision, excel dashboard in nigeria, data analyst in nigeria, excel in nigeria, Power bi tutorial for beginners, Power bi tutorial, Power bi dashboard design, Power bi projects, power bi dashboard tutorial, dashboard in power bi, Sales dashboard in power bi, dax in power bi

Id: hZSCZ7Hz01U

Length: 55min 26sec (3326 seconds)

Published: Mon Jun 12 2023