- 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