How to Transform Excel Data into a Striking Visual Report with Microsoft Power BI

welcome to teachers Tech my name is Jamie and  it's great to have you here today I want to show   you how to use power bi so this is going to be a  beginner's tutorial I'm going to show you how you   can take a Microsoft Excel workbook like this  one with all this data and bring it into Power   bi to make it look like this and this doesn't  take that long at all and not only does it look   like this is interactive so I can click on  different parts to find things specifically maybe   by a country maybe buy a product or maybe buy  some time period so you can see how interactive   this is that's what I want to show you how to  create using power bi today on teachers Tech   foreign if you don't have power bi installed yet  on your computer you'll need to install the power   bi desktop I'll put a link to this page down below  in the description then you can go to it and then   go through the install of it I will also put a  link to the Excel workbook that I'll be working   through today so check that in the description  and get that on your computer then we're going   to get ready to bring that data into Power bi  so hopefully you got power bi installed on your

computer and you went ahead and opened it and it  looks like this what we're going to be doing first   is getting the data so we're going to bring that  Microsoft Excel workbook into Power bi but I just   want to point out here is the practice one that  I saved if I wanted to open that one up I could   go ahead and just click on it and it would open up  so I'm going to go ahead and click get data here   and what's going to happen is you're going to see  all the different data types that you can bring   into Power bi now we're only using a Microsoft  Excel workbook here today but you can bring in   all these different sources here so you're not  limited to this and you can bring in different   types and you can connect them but this is a  beginner's tutorial on power bi so we're going   to start with the basic just this Microsoft Excel  workbook so I'm going to go ahead double click on   this and pick the worksheet the worked book  that I want and it's going to be this one right   here this practice set and I'm going to hit open  and it's going to just connect to that and then   it's going to ask me another question I have to  pick what sheet it's coming from so you notice   you don't see any items over here well I need  to select it's going to be from the financials   tab there and then you get to see it here now  what I'm going to choose first though is load

or sorry transform the data right here so I'm just  going to select it and then it's going to bring it   into this and then we can start and you'll see it  load up and then we can start making some changes   to this before we get to bringing in all the  visuals that I showed you at the very beginning   the reason why I chose transform data rather than  just loading it right away is I want to clean the   data before I apply it to the visuals and this  will save me some time and just have things   looking better when I add them so these are the  few examples that I'm going to give here to clean

this data the first thing is going to I'm going to  point out is in this unit sold column and you can   see here in this cell it says 1618. 5 well I don't  want it to be decimals because I I'm not going to   sell 0. 5 of a product so I'm going to select  this entire column just like that by selecting   this top one and I'm going to change this so if  I go up to the transform tab up top you can see   if I drop down here there is going to be whole  number you can see all the different options   that you have but I want a whole number here  so this goes through it will ask me to replace   current and I want that I hit replace current and  it now you can see no decimal all the decimal are   gone out of there I have just whole numbers so  that's a quick way to clean your numbers if you   wanted it done that way another thing I want to be  able to change is let's say through this segment   you notice how some are all caps and some are  lowercase like Enterprise or direct except for   the front one I want to be able to make everything  uppercase so when the visuals are done it's just   easier to see if I stay under the transform here  I can go ahead and then just move over to format   I can format everything to uppercase and you can  look at the other thing ways you can format too   but I'm going to go to uppercase now you can see  I have Enterprise changed I have direct change   and everything's uppercase so just a couple more  changes I'm going to clean here I'm going to go to   the bottom and scroll over to the very end and I  want to take a look at where it says month name so   in month name I don't want to say well month name  I just wanted to be every month you can change   these column the headers here by just double  clicking in here and you can get rid of or change   them to how you want so now I've made that change  here okay one last way I'm going to show you how   you can clean your data is let's say we take a  look at products here and one of these products   is not selling anymore so I don't want this  included in my results because I know it doesn't   need to be there because it's not being sold  I can filter this so if I drop down and decide   well you know I don't think doohickey anymore is  going to be sold or I know it's not sold anymore   so I can just select this and hit OK and it's  going to take it's going to filter that out so   if I go okay you notice doohickey is no longer in  there so I can filter the results so now if I go   through and clean like this I can make sure what  I'm going to be putting in my visualize Asians   it will save me time and just look a little bit  better if you take a look over to the right you   can see all the transformation that has happened  all the different steps that I did if you wanted   to remove any of these okay so we're almost done  we're finished cleaning up this data I'm going to   go ahead up here where it says file and I'm just  going to go close and apply it just like that   so and now at this point what you can do as we're  working on this you can go ahead and save this as   we're working on here too so you can go ahead and  hit save and then just give it a name here so for   my case I'm going to go and call this practice  too because I already have one done so that's   saved on my computer so if you take a look at the  far right you'll see Fields right here maybe your

financials are closed you can see how we can  expand it and open it up now power bi was able   to look at each column and get an understanding  of what's in it so you can see that there would   be amounts in these and this one would include a  date so power bi is able to do that we can expand   it and see what's in each of these whenever you  see the expand Arrow here now what I want to do at

this point is actually write an expression in Dax  and now this is something more advanced but I just   want to show you a couple examples of writing  a measure and adding a table in here and then you   can go ahead and write exactly how I do this so  what we're going to go do first is go up to the   Home tab right here and I want you to go across  into the ribbon and click on new measure right   here and you should be able to see that it says  one measure equals so what we want to do is create   an expression index that will add up all the  numbers in the unit sold column okay so I'm going   to go ahead and delete this and then you can just  type exactly what I'm typing and then just hit   pause on the video to make sure you got everything  correct and I'm going to go ahead and start with   the variable here the units sold just like this so  total units sold in space and equals now I'm going   to start the next part and what is it I want to  start with well I need to sum so as I put the s in   the capital S notice that if I put the U I can go  ahead and select sum right through here so if you   used to if you're used to using Microsoft Excel  this should look familiar so I'm going to select   sum now it's going to say the column name well  this is where financials is going to come into   play so I'm going to select the financials right  there and I need to add the square bracket so I'm   just going to add a square bracket and what is it  well it's going to be units sold that I want to   add out or a sum up so it's going to be right here  and I'm just going to go ahead and add a bracket   at the end to close this so this is what I want to  write and make sure you hit pause and go through   it when you think you have it all right just go  ahead and hit this check mark right here so if you   look over at Fields again way over on the right  look down at what we just created we created Total   units sold and you can see it's a calculation  that's being performed on it based on the icon

all right let's add another expression and this  time we're going to add a new table and if we're   looking at the tabs across just go to table tools  here and we have new table go ahead and click on   new table and you should see this right now where  it says one and table equals let's put in our next   expression so this is the expression that we're  going to be typing so you can hit pause and type   this out the way I have it written so this is  going to generate a calendar table of all dates   between January 1st 2020 and December 31st 2021  and when you get this again hit the check mark   when it's completed if you hit the check mark you  can see now it generates this and if I look over   at my fields at the very end it has calendar right  here and dates now we're not quite done we need to   make sure we connect this with our other table  and we're going to connect it through a date so

I'm going to go over here to the model and what  we see here is I'm going to double make sure we   have both open here so I have two different things  open I need to build a relationship if you've ever   used Microsoft Access before this will be familiar  to you but it's very simple what we need to do we   just need to drag to the connection over here so  the relationship is going to be between these two   and you can see now if I hover over it's from the  date on the calendar to the date on the financials   so at this point now we're ready to start building  our visualizations all right so let's move to our   report tab so we can start creating our visuals  now that we have everything and if I go up you   can see we're on the model tab here we go to  if we go to our data uh this was the calendar   table that we just created if I take a look over  here if I click on financials this will show me   the financial table so I can click back and forth  to the different tables and between these tables   there's a relationship that we built in the model  now this is where we're going to create our report   is here all right let's go start by giving a title  for this report so we're going to start by adding

a title to our report and to do this we need to  add a text box so under the Home tab if we look we   can see text box right here in the ribbon I'm just  going to go ahead and select this now I can type   what I want right in here so I'm going to call  this executive summary Finance report and I can   make some modifications still to this I can change  the font size and the font so if I highlight this   I could go ahead and switch if I wanted it to a  different one like that I can make it larger I'll   make this a 20 font and I can make it bold so I  can do all these different things to it you can   see uh from alignment to inserting a link but  right now I'm just going to leave it as there   now the other thing I want to change if I click  off fit you can see how it is I want to be able   to stretch this out so I'm going to move it like  this so just so it fits and I'm going to move this   bottom of the Box up so now I have my title of the  report let's get ready to add some visuals here

we can add visuals a few different ways so if I  just go up to where I was before beside the text   box you can see add a visual to report just select  it and you get a placeholder here that you can   drag the fields into uh to get your graph  started now I'm just going to go ahead and delete   this so at any time if you select it you can  hit delete and it will be gone and I'm going to   go ahead and just move over to the visualizations  over here you can see I can go and select one from   here and it will appear again I'm going to just  drag this closer here so you can see the changes a   little bit easier without much movement now I need  to add some fields to this I can do this again in   a couple different ways drag or select a lot of  times I'll just select so if I just select right   here so far there's here you don't see anything  populated yet so let's go and put profit I could   just click profit if I wanted to go in or I could  drag it over and drop it so by default you're in   power bi you're going to get this graph here but  it's pretty easy to change these two so what I'm   going to do is first of all I'll just show you  you can size this here so if I wanted it to be   a little larger and if I wanted to move things  again just simply grab the top and start moving   around now I'm going to make a change to the the  x-axis here I'm going to drop down on this right   here so just drop down and I'm going to choose  date so when I choose date now all of a sudden   it puts it into this format so power bi went  through and now it's pulling it in month by month   and it's really easy to change your different  type of charts that you want so if I wanted a   line chart go ahead and select it and it gets  changed like that so you can try a few different   ones depending what you want I'm going to leave  this for this example and when we come   back to it I'm going to show you how to format it  later once we put a few more visuals in it to make   everything look nice and but that will be at  the end once we put it in our different visuals

so the next visualization that I want to place  in this report is one of the map make sure that   you're not selected on one when you go to insert  your next one or else it will just get replaced   so I'm going to go to this right here right above  slicer where it says map and I'm selecting this so   I get the map over here I'm just going to move  it over here and now at this point you might   get an error if you try to actually put things  into it if you don't have your security on your   power bi set the correct way if you do get this  that error this is what you do you go up to file   you get two options and settings you can go to  options and then at this point when the next   window opens up go to security and make sure  that this is checked off when I first did this   the first time this wasn't I had to make sure that  that was set so I'm going to hit OK so now I have   this selected right here I can drag or select what  I want I can take this country here and just bring   it in here because in my data I have a list of  one column that's full of different countries I'm   going to make this a little larger and when I pull  that in right away it identifies the countries   that are in it but I want to add profit to this  too so I'm just going to go ahead and click   Profit just like that and now you can see that the  circles have changed a little bit but watch how   interactive this is already so if I click this my  sum of profit by date is changing up here based on   what I'm selecting over here so you get to see how  interactive this data is when you start putting it   into Power bi into these reports okay so we have  two different visualizations let's add a third

I'm just gonna make a quick move of this map one  here I'll put it over here for now I'm going to   click off to make sure I'm not changing the map  one and I'm going to add this one a clustered   column to it I'm going to  put my sales I'm going to put my product and   segments in it like this  so now I'm going to it's a little hard to see   but so I'm going to just stretch it across give  it some more room here uh just like this we'll   go all the way across so now you can see from  if I look at thingamajig I can break it down   by the different segment segments whether it be  direct Enterprise business to business business   to Consumer all these different things and again  if I click on one of these are interactive   also if I click twice it turns it off so we can  make the adjustment no matter what we click on   all three visuals are connected here so now we're  going to add in a couple extra slicer to be able   to even go further to break down the dates of each  of the products and sales of everything so I moved

to some things around here I just moved it over  to the lab so I have some space right here and   this is where I'm going to put my slicer and the  slicer will help me filter things even more and I   mentioned it's going to be by date so I can select  a different order I could go ahead and just select   date up here because I know this is what I'm going  to be looking for so if I select date you can see   I have this information that comes in right away  I'm just going to drag this up now I want this to   be a slicer well I can go and click slicer now  so if I go select slicer I get this information   here now I can change What fields I want in this  too because if I drop down notice that there's   quarter one two three four I don't want all that  information so if I look under the fields here I   can go ahead and take away so if I don't want  quarter and I don't want day so now if I drop   down it should just be by month I can stretch this  out I want this to be the same length here so then   if I was look talking through different one so  I just clicked on 2021 you can see the changes   I can start clicking off on different months  I believe on the data the first sales weren't   until September here so there won't be anything up  to this point but you can see as you click on how   everything is adjusting so I'm starting to get a  snapshot I can be looking by the segment I could   country I could be looking by  the sum of the profit there's so many different   things I could be looking at the product so many  different ways you can easily get that quick   visualization so we have a number of different  visualizations from slicers to charts in here   on the map let's go ahead and make everything look  a little nicer here do some customization of this

the first thing I want to do is make sure  everything's lined up the way I want and if   you click on any of these and start dragging them  around you can see that we get the red line allows   me to know if things are lined up so I if I want  to make sure everything's lined up just see that   it connect just like that I know that one's  lined up I want to bring this one down to match   that line at the top so right there and some of  these maybe I'll move over after I get more done   so I think that is lined up on that side and this  this side now you can go ahead and   move things the way you want I can see this one's  a little bit off down here so I'm just going to   drag that okay so now we've aligned things just  by dragging them around if you want it to look a   little different go ahead and make things if you  wanted to flip where you put things go ahead and   do that now what we're going to do is starting by  changing uh we're going to add a color to the back   here behind here so I'm going to put kind of a  bar you know be a different color here so what   I'm going to do is go up to insert here and I'm  going to insert a shape so I'm just going to move   over into the ribbon you can see there's shape  here and I'm just going to put a rectangle in so   here's the rectangle and I'm not worried about  colors yet I can modify these I can change the   shape I can drag it so I want this is going to  go across the top just like that so I'm dragging   across I want it to go the whole way just to kind  of add a border up top now with this I want to   make sure that I it's blocking the title so I want  to send this to back so how do I do this well I'm   going to go up to format right here and when  I go to format you can see I have some choices   bring forwards and back if I drop down you can  see send backwards send back I can click on it   and now if I click off of it because if I just  and click somewhere else you can   see now that how this is not transparent this  white so this is in the back that's what I want   well how can I change this so at this point right  here what I want to do is go ahead and change the   background on there so I'm going to go ahead and  find that under effects and see I have background   and I'm going to go and bring transparency to a  hundred percent so I'm just going to bring this   over and now this becomes see-through I also want  to turn this to a white font so I'm just going to   go ahead drop we're going to select everything  drop down here and select white so I've made a   couple of changes here I'm just going to click  off see what I have uh maybe I want this to be   a little larger font here too so I'm going  to drop down bring it up to 28 now I have to   bring this a little larger so that's starting  to look how I might want to create this report   so I'm going to go around and make changes to  each of these visuals so what I'm going to do   with the map is I want to change the title here  and I want it to be a different color of the map   actually too so I'm going to make sure I have this  selected and as I look over in visualizations this   time I'm going to change the format your visual  here and what do I want to have change well let's   start with the title is going to be  under General and you can see uh different all   the different things that you can modify here but  I'm going to go to title here and I don't want the   sum of I'm going to delete this just like that and  right away I have profit by title and I want all   my size my titles to be the same I'm going to go  and just change the font here to each one we'll   go with the same one that I had up here which was  Tahoma so as I go back here I want to find Tahoma   here right there and I'm going gonna make this a  little larger and I want this bold so there's my   first one now I'm going to go to my visual here  and go to map settings and I don't want the style   to be rode I'm going to drop down and I want it  to be grayscale just like that so it changes I   like it because the countries kind of pop out  a little bit more like that now again I'm not   changing everything on all the different visuals  I just wanted to point out where you would change   so if you look through the different drop downs  even under General how you might want to be uh you   know even do where you want things I want to keep  this to the left here but take a look at different   effects that you can have and everything so let's  move on to our next one right here oh actually   you know what I want to add one more here too so  I'm going to go to Shadow since I have it open I   like the look of Shadow and I'm going to select  Shadow and you can see how it kind of gives that   visual of that shadow around and I'm going to do  the same thing to each of these visuals here all   right so I'm going to select sum or profit now  on this next one and I want to make sure that I   I'm going to start with my title I don't want to  be some profit I'm just going to get rid of that   sum profit by date and again what was my font  going to be well it's going to be Tahoma and   there it is and I'm going to increase the size I  want it to be bold so that I get things similar   I'm going to leave the color for this now the same  and then if I go through I want to add the shadow   to this I'm going to select Shadow I'm going to  just size things I need to give it a little bit   more space in between just so it looks a little  bit better here again you do what works well for   you so I have a couple of visuals I'm fine with  date and some of profit on the side I don't want   that and if I wanted to change this y-axis maybe I  just want to say profit I would need to go back to   here and I'm just going to select this and take  a look where it says sum of profit that's what   showing up there so if I double click in here  I can edit it through this point and I can just   say profit so if I click off now it makes those  changes so remember we do have different labels   here if I don't want you can see it where it says  date on it like I'm going to leave it as there but   if you wanted as something else you could make  that change there let's go down to this one make   some quick changes of those to make them all the  same so I select this graphic here I'm going to go   back to format visual I'm going to go to General  and make sure get rid of this sum of and change my   font that I have back to Tahoma in 16 and bold so  that's looking the same here and quickly add the   shadow I'll scroll down turn the Shadow on and  things are looking okay lined up here again you   change it how you want different ones to look play  with all the different ways so now I'm going to   add another shape to the background Behind These  two rate here so I'm going to go back to insert   and go to my shapes like I showed you before and  we're going to do Square again and I'm going to   be putting this in the background there's going  to be a little bit of room between the different   ones and it's going to be a different color so I'm  going to stretch it to the size just to hear and I   can manipulate it more after and what I want you  to do is under uh shape if I wanted a different   color drop down and look for style so under style  you can see where this blue is I'm going to turn   this to more of a gray here so I'm going to  choose maybe this one right here and again   I can still change it after and if you wanted it  to have rounded Corners you could do that if you   wanted to if you want a rounded rectangle so you  go and pick what again what looks best for you   I need to send this to the back now and do you  remember how we did that before that was under   format up top in the tabs if I go to format I'm  going to drop down and go send to back now when   I do that I can click off somewhere and you can  see it's kind of Gray in it now I'm going   to line things up a little bit better oops make  sure you grab the top here and I'm going to bring   this in a little bit I'm going to size this one  here turned off all the data there I'll turn that   one off and I'm going to move this one just like  that so I'm going to stretch this one up aligned   it up and I kind of like how things are on that  gray here now you can change things keep adding   things more I just wanted to show you how to add  some shapes and how to adjust each of the visuals   that you did but a really easy way to make some  changes would be to do this after go up to view

and now you can make some really quick pages so  uh if I drop down you can see all the different   ones but if I select different ones look how fast  that change was made uh through it so just click   on different ones you might find a different color  that you might like uh on this so find something   that you like drop down try some different ones  try the dark one like this um I don't like the   way the color I'm not going to change the color  on it but I don't mind the dark background so   find one just keep changing it around until you  like one now what do you do with this data how do   you share this with uh people let me show some  different ways so you've done all this work on

power bi you've created this stunning report how  do you share it with people uh well first of all   make sure you've kept saving this as you went  along you don't want anything to crash and lose   all this work that you've put into this report but  if we go up to file up here I want to point out a   few different things now going to export here you  notice so we have power bi template and Export as   a PDF power bi template will export this to a file  with the data with this and then you could give   that data to somebody else that'll be a folder  with the data and then they could open it up   it'll be interactive so you'd have to share that  file with them somehow and they'd have to have   power bi to open that up you could export this as  a PDF but it's not going to be interactive it'll   just be something maybe you want to print off to  show your report on it that way a different way   you could do this too is go to publish here so if  you go to publish and publish to power bi you need   to make sure you have a login for this you can  actually sign up for a free trial but let's say   if you're within an organization that's using this  you could share it with your boss or co-workers   and then it would be completely interactive  too as they open it up here too so depending on   which way that you want to try it test out a few  different ways maybe try that free trial account   to see how works for you so I hope you like this  walkthrough of power bi this beginner one just   kind of showing you the basics of how it works  from an Excel a workbook let me know what else   you might want to learn in power bi and maybe  I can create some more tutorials on it thanks   for watching this week on teachers Tech I'll see  you next time with more Tech tips and tutorials