Coding

Power BI Desktop – Our first project in 15 minutes

  • 00:00:00 welcome to our first project in power of
  • 00:00:03 your desktop in this video we will get
  • 00:00:06 data work on that data and finally
  • 00:00:09 create a visualization a world map to be
  • 00:00:12 more precise in case you are not sure
  • 00:00:14 what you can see right here just take a
  • 00:00:17 look at this video otherwise just stay
  • 00:00:20 here and take a look at our first
  • 00:00:22 project now so this is the starting
  • 00:00:26 screen that we can see when we open
  • 00:00:27 power of your desktop the important
  • 00:00:30 button is that get data button right
  • 00:00:32 here if we click that then this menu
  • 00:00:36 opens and as you can see you can connect
  • 00:00:39 part of your desktop to a lot of
  • 00:00:41 different sources well actually we don't
  • 00:00:44 have any sources at the moment right so
  • 00:00:47 a really good source could be if we
  • 00:00:49 simply go to other right here and then
  • 00:00:52 double click on web I guess now you can
  • 00:00:56 see that we only need a URL with data in
  • 00:00:59 it of course and as soon as we got that
  • 00:01:01 we can simply import the data or
  • 00:01:04 actually connect power bi desktop to
  • 00:01:06 this data on this web page now let's
  • 00:01:10 first find a web page with some data in
  • 00:01:12 it so this is the table that I would
  • 00:01:14 like to visualize in power bi desktop we
  • 00:01:17 simply have paid up for 30 cities
  • 00:01:19 regarding the passengers at the
  • 00:01:22 corresponding airports now you can find
  • 00:01:24 the link to that page of course down
  • 00:01:26 there in the video description so what
  • 00:01:28 we have to do right now is we simply
  • 00:01:30 have to copy the URL up here like this
  • 00:01:33 now go back to power bi desktop and
  • 00:01:37 simply paste the URL right here then we
  • 00:01:41 click OK and wait a few seconds of
  • 00:01:44 course and now this navigator opens this
  • 00:01:49 navigator allows us to specify what data
  • 00:01:52 we want to connect power of your desktop
  • 00:01:54 to so this means if we simply click on
  • 00:01:56 to document right here then we see that
  • 00:02:00 we probably selected the wrong data at
  • 00:02:02 the moment the good thing is we can
  • 00:02:04 simply change your web view appear and
  • 00:02:07 if you now scroll down a little bit like
  • 00:02:10 this then we can see that table 0
  • 00:02:14 is the table that we want to connect
  • 00:02:15 power of your desktop to if we now
  • 00:02:18 simply tick that box right here which is
  • 00:02:20 then also ticked in this left part and
  • 00:02:22 switch back to the table view appear
  • 00:02:25 then you can see that generally yes we
  • 00:02:29 have the right data but you can also see
  • 00:02:32 that there is a lot of information in it
  • 00:02:34 at the moment which we don't actually
  • 00:02:36 need and this is the reason why we have
  • 00:02:39 to decide between load and edit at the
  • 00:02:42 moment because the difference is as
  • 00:02:44 follows if we press loads down here now
  • 00:02:47 then this data will be automatically
  • 00:02:50 loaded into our real data model if we
  • 00:02:54 press edit then we will go to the query
  • 00:02:56 editor this is kind of a separate tool
  • 00:02:59 inside of power bi desktop that allows
  • 00:03:02 you to prepare your data so you can for
  • 00:03:05 instance delete rows which include
  • 00:03:07 information that you don't need as soon
  • 00:03:10 as you finished your work in that query
  • 00:03:11 editor then you load the data into the
  • 00:03:14 real data model and this is really
  • 00:03:17 important because if you really clean
  • 00:03:19 your data in a good way this means for
  • 00:03:22 example you get rid of all the
  • 00:03:23 unrequired information then you only
  • 00:03:26 load the important data into the data
  • 00:03:28 model and by that your data model
  • 00:03:30 becomes smaller and more efficient
  • 00:03:32 because it consumes less memory this is
  • 00:03:35 the reason why we now select edit right
  • 00:03:38 here like this and now we are in this
  • 00:03:41 query editor so in this data preparation
  • 00:03:44 tool and in the center you can see the
  • 00:03:47 information that we connected power bi
  • 00:03:49 desktop to now so if we just scroll
  • 00:03:52 around right here so we can scroll to
  • 00:03:53 the right for example to see all the
  • 00:03:55 columns we can also scroll down if we
  • 00:03:57 want to and see the last row for example
  • 00:03:59 well then we see that we have some work
  • 00:04:02 to do the first thing we have to do is
  • 00:04:04 we should get rid of those first five
  • 00:04:07 rows because these roles include
  • 00:04:10 information well that we don't need but
  • 00:04:12 if we look into row number 6 and scroll
  • 00:04:15 to the right then we can see that this
  • 00:04:17 total value right here this is something
  • 00:04:20 that we should keep and that's the
  • 00:04:21 reason why we only delete Row one to
  • 00:04:23 five so to delete rows in power of your
  • 00:04:26 desktop you
  • 00:04:27 and simply go to the home ribbon which
  • 00:04:30 you can find up here and now in this
  • 00:04:32 reduce rose area right here you can
  • 00:04:35 click on to remove rose and in our case
  • 00:04:38 we want to start removing rose from the
  • 00:04:40 top so we simply press remove top rose
  • 00:04:42 like this and now we said that we want
  • 00:04:46 to delete the first five rows so we
  • 00:04:48 simply type five and press ok and you
  • 00:04:53 can see that we immediately deleted the
  • 00:04:55 first five rows of all the columns of
  • 00:04:57 course but we now have this new first
  • 00:05:00 row and this row includes information
  • 00:05:03 that we don't want to delete because
  • 00:05:06 what we actually want is we want to have
  • 00:05:08 this total right here as the column name
  • 00:05:11 so we could of course delete that row
  • 00:05:13 and simply rename the column this is one
  • 00:05:15 way but the more easy way is to simply
  • 00:05:18 go again to the home ribbon right here
  • 00:05:20 and now select this use first row as
  • 00:05:23 headers option if we click that then you
  • 00:05:27 can see that we now renamed that column
  • 00:05:30 to total and the first row is of course
  • 00:05:32 gone which is totally correct at the
  • 00:05:34 moment for us now another thing we
  • 00:05:37 should do is we should scroll down to
  • 00:05:39 the last row because as far as I
  • 00:05:40 remember yes this information this is
  • 00:05:44 also not required and as we already know
  • 00:05:47 how this works well we can simply do it
  • 00:05:49 quickly by going to the home ribbon
  • 00:05:51 again where we still are now go to
  • 00:05:53 remove rows right here press that and
  • 00:05:56 now don't select the top rows but the
  • 00:05:59 bottom rows like this and only select
  • 00:06:03 one row now we press ok again and as you
  • 00:06:08 can see now the last row is also deleted
  • 00:06:11 this is fine now we talked about the
  • 00:06:15 roles but I think it's all the time to
  • 00:06:17 take a look at the columns now and to be
  • 00:06:20 honest I don't think we need all the
  • 00:06:23 columns we have right here if we scroll
  • 00:06:26 to the left like this column one and
  • 00:06:29 column two I don't think that's required
  • 00:06:31 the same thing is true if we scroll to
  • 00:06:34 the right like this well we don't need
  • 00:06:36 this column and that column so basically
  • 00:06:39 we only need this color
  • 00:06:40 and this total column now to delete
  • 00:06:44 these columns we have now multiple
  • 00:06:46 options but a really easy option is to
  • 00:06:49 simply go again to the home ribbon now
  • 00:06:52 go to choose columns right here press
  • 00:06:54 that select choose columns again like
  • 00:06:57 this and now let's simply select the
  • 00:07:00 columns that we want to keep well in our
  • 00:07:03 case we can first unselect all of the
  • 00:07:06 columns and now only select this one and
  • 00:07:08 the total column so the two columns that
  • 00:07:11 we want to keep now we press ok down
  • 00:07:15 here and as you can see I now cannot
  • 00:07:19 scroll to the left and to the right
  • 00:07:20 anymore because we only have these two
  • 00:07:22 columns left this was also quite easy
  • 00:07:25 right so let's now take a look at the
  • 00:07:27 column name maybe the column name in
  • 00:07:30 this case well I don't think this is a
  • 00:07:32 good name so we should rename that to
  • 00:07:34 city well to do this we simply double
  • 00:07:37 click on to the column right city
  • 00:07:40 central center now we rename that column
  • 00:07:44 this is also nice but I don't think
  • 00:07:47 you're done with the columns yet there
  • 00:07:50 are two more things that I would like to
  • 00:07:52 change the first thing is I would like
  • 00:07:55 to get rid of all the information after
  • 00:07:57 the comma because we have the city right
  • 00:07:59 here
  • 00:07:59 which is enough and I don't need that
  • 00:08:01 country information afterwards to do
  • 00:08:04 this we again as a great function in
  • 00:08:06 power bi desktop and this function can
  • 00:08:09 simply be found in the transform ribbon
  • 00:08:11 right here now we go to the extract
  • 00:08:15 option right here and if we click onto
  • 00:08:18 that we can now specify what kind of
  • 00:08:21 information we want to extract from the
  • 00:08:24 column that we selected in our case we
  • 00:08:27 would like to keep all the information
  • 00:08:29 that is left to a specific delimiter in
  • 00:08:32 our case the delimiter is this comma
  • 00:08:35 right here well so we can simply select
  • 00:08:37 the text before delimiter option like
  • 00:08:40 this now define the delimiter which is
  • 00:08:43 the common our case like that and if we
  • 00:08:47 now press ok well then you can see that
  • 00:08:51 we only have the city names now in our
  • 00:08:54 column
  • 00:08:54 so the country information is gone again
  • 00:08:58 this was really easy right now let's
  • 00:09:01 take a look at the second column this
  • 00:09:03 total column right here what I don't
  • 00:09:05 like right here is that we have this
  • 00:09:07 spaces between the different numbers I
  • 00:09:09 would also like to change that well to
  • 00:09:12 change this we simply have to go back to
  • 00:09:15 the home ribbon right here and now
  • 00:09:18 select the replace value option right
  • 00:09:20 there
  • 00:09:20 if we click onto that then we simply
  • 00:09:25 have to specify which value we want to
  • 00:09:27 find in our case we actually only want
  • 00:09:30 to find a space so I press space right
  • 00:09:33 now on the keyboard and replace it with
  • 00:09:35 well nothing because then the space is
  • 00:09:39 gone and then we have the number the way
  • 00:09:41 you'd want it to be so we type space
  • 00:09:45 right here and cannot press ok
  • 00:09:47 and again as you can see this perfectly
  • 00:09:50 worked and now we're actually done with
  • 00:09:53 our two columns so we now have the
  • 00:09:55 information about the city and the
  • 00:09:57 corresponding passengers well one less
  • 00:10:00 thing is maybe there that we should
  • 00:10:02 change this is the formatting of that
  • 00:10:05 second column right here at the moment
  • 00:10:08 this is formatted at text and you can
  • 00:10:10 see that easily by this ABC symbol up
  • 00:10:13 here and if you click onto that then you
  • 00:10:16 can see that ABC well this is text so
  • 00:10:21 maybe it makes more sense to change that
  • 00:10:23 formatting from text to a decimal number
  • 00:10:25 for example like this and as you can see
  • 00:10:29 now that's ABC changed to one two so we
  • 00:10:33 got that we now worked on the rows
  • 00:10:35 worked on the columns and change the
  • 00:10:38 formatting and we only needed this
  • 00:10:41 simple steps for that pretty awesome
  • 00:10:43 right and now we also finished our work
  • 00:10:46 in this curia deter so in this data
  • 00:10:48 preparation step this means that we
  • 00:10:51 aren't all ready to finally note the
  • 00:10:53 data we have right here into our real
  • 00:10:55 data model so we need to leave that
  • 00:10:57 query editor and we can do this by going
  • 00:11:01 to the home ribbon we still are and now
  • 00:11:03 press until close and apply right here
  • 00:11:07 now we are in the data model you can see
  • 00:11:11 the interface is different and also the
  • 00:11:13 functions are but we're going to talk
  • 00:11:15 about that in detail right now the only
  • 00:11:18 thing you have to know is that we left
  • 00:11:19 this creditor and we are now in the
  • 00:11:21 actual data model at the moment we are
  • 00:11:24 in the so called report view this is the
  • 00:11:27 view where we will create our
  • 00:11:29 visualization in a few seconds but first
  • 00:11:32 I want to show you something else
  • 00:11:33 because we can change from this report
  • 00:11:35 view that we selected right here to the
  • 00:11:38 data view which is the second option
  • 00:11:40 right there if I click onto that then
  • 00:11:44 you can see our two columns or actually
  • 00:11:47 our whole table that we imported into
  • 00:11:49 that data model and we need to adjust
  • 00:11:51 one important thing before we create our
  • 00:11:54 chart this first column the city right
  • 00:11:57 here is text at the moment so power bi
  • 00:12:01 doesn't know that we have cities in here
  • 00:12:03 and as I would like to create a world
  • 00:12:06 map it might be important that power bi
  • 00:12:09 is aware of the fact that these are
  • 00:12:11 cities to make sure that power bi knows
  • 00:12:15 this we simply have to select the column
  • 00:12:17 which we already did now go to the
  • 00:12:20 modeling ribbon up here and now change
  • 00:12:23 the data category so we simply click on
  • 00:12:26 to that and at the moment we have that
  • 00:12:29 uncharacterized category which is not
  • 00:12:31 true because if you look at the second
  • 00:12:34 option right here city then this is the
  • 00:12:37 right option for our data we have right
  • 00:12:39 here so if we click on to city then we
  • 00:12:43 well we don't see that it changed right
  • 00:12:45 the only thing we see is that symbol
  • 00:12:48 right here so apparently some kind of
  • 00:12:51 geographical information is now
  • 00:12:53 available in a data model let's see now
  • 00:12:56 if this works by creating our
  • 00:12:58 visualization and to do this we simply
  • 00:13:01 have to go to the report view right here
  • 00:13:03 and now create our visualization as I
  • 00:13:08 said I would like to create a world map
  • 00:13:10 and to create something like this you
  • 00:13:13 simply go to that visualizations column
  • 00:13:14 right here and now select that symbol
  • 00:13:17 right here map if we
  • 00:13:20 to that then we see this frame right
  • 00:13:23 here this is actually the area our chart
  • 00:13:27 will be displayed in as soon as we load
  • 00:13:29 some data into it of course at the
  • 00:13:31 moment we don't have staidum so we can
  • 00:13:33 simply change the size of our future
  • 00:13:35 chart by clicking right here and
  • 00:13:36 dragging it right there for example well
  • 00:13:40 and now we need to get the data into
  • 00:13:42 that visualization and to do this we can
  • 00:13:45 simply go to this field column right
  • 00:13:47 here now select the city for example and
  • 00:13:51 drag it to the location area right here
  • 00:13:55 because now we see the different cities
  • 00:13:59 that we have so this 40 cities on the
  • 00:14:01 world map but we didn't load the data so
  • 00:14:04 the actual passenger amount into our
  • 00:14:06 visualization well to do this we can
  • 00:14:09 simply drag and drop this total column
  • 00:14:11 right here down here into this size area
  • 00:14:15 like this and now you can see that the
  • 00:14:19 size of these bubbles changed depending
  • 00:14:21 on the passenger amount but you might
  • 00:14:24 have a problem with identifying the
  • 00:14:26 different cities you can also change
  • 00:14:28 that quickly in part of your desktop the
  • 00:14:31 only thing you have to do is you have to
  • 00:14:32 go to this formatting area right here if
  • 00:14:35 you click right there and simply add the
  • 00:14:38 category labels right here like this
  • 00:14:41 then you can see that we now have the
  • 00:14:44 names of all the cities that we have in
  • 00:14:46 our data but this is too much
  • 00:14:49 information right so maybe we should
  • 00:14:52 apply a filter as I lasted and only
  • 00:14:56 display the top five cities for example
  • 00:14:59 this can also be done quite easily we
  • 00:15:02 only need to go back to our fields area
  • 00:15:05 right here now scroll down a little bit
  • 00:15:09 and here in this filters area we can now
  • 00:15:13 simply well filter our data so we click
  • 00:15:17 on to city right here
  • 00:15:19 change the filter type from basic
  • 00:15:21 filtering to top n maybe scroll down a
  • 00:15:25 little bit and now define which items
  • 00:15:29 should be presented in our case I would
  • 00:15:31 say we select the top five cities for
  • 00:15:34 sample and now we have to decide well
  • 00:15:37 the top five cities by what and by what
  • 00:15:40 this is the passenger amount right here
  • 00:15:43 so we drag that into this field right
  • 00:15:46 here like this and press apply filter
  • 00:15:49 like that well and if we not unselected
  • 00:15:53 visualization you can see that we now
  • 00:15:56 have a world map visualization with
  • 00:15:59 different bubbles with different sizes
  • 00:16:01 depending on the passenger amount of the
  • 00:16:04 top five cities in our data and this is
  • 00:16:07 it this was our first project we
  • 00:16:10 connected how the data to data clean
  • 00:16:13 that data loaded the data into the data
  • 00:16:15 model and created that visualization
  • 00:16:17 with those few steps only I think this
  • 00:16:21 is pretty great so if you want to know
  • 00:16:23 more about power bi desktop now I would
  • 00:16:25 be really happy to see you in the next
  • 00:16:28 videos but we will talk about more
  • 00:16:29 things in power of your desktop or I
  • 00:16:32 would also be happy to welcome you in my
  • 00:16:34 power bi a complete introduction udemy
  • 00:16:37 course you can find the link to that
  • 00:16:39 course at a discount price down there in
  • 00:16:42 the video description however this is it
  • 00:16:45 for the moment thanks a lot for watching
  • 00:16:46 and see you next time