Coding

Power BI Desktop – Preview Features and Weekday Sorting

  • 00:00:01 welcome to another power bi desktop
  • 00:00:03 video in this video we will take a look
  • 00:00:06 at the preview features in power bi
  • 00:00:07 desktop
  • 00:00:08 this means features you can already use
  • 00:00:10 but which are not final yet so I will
  • 00:00:13 show you where you can find this preview
  • 00:00:15 features and how you can activate them
  • 00:00:17 and additionally we will take a look at
  • 00:00:19 one specific preview feature in a little
  • 00:00:21 example and throughout this example I
  • 00:00:24 will also show you how you can sort your
  • 00:00:26 weekdays correctly in power bi desktop
  • 00:00:28 and this is it actually so let's start
  • 00:00:31 now
  • 00:00:34 so before we can really start with that
  • 00:00:37 preview feature we should make sure that
  • 00:00:39 we had installed the latest version of
  • 00:00:42 power bi desktop so if you open power bi
  • 00:00:45 desktop and find the following message
  • 00:00:47 in the right lower part of the screen so
  • 00:00:49 updates available well then you should
  • 00:00:51 click that first then you arrive right
  • 00:00:55 here this is the official Microsoft
  • 00:00:57 power bi block and by the way a really
  • 00:01:00 great page because right here you can
  • 00:01:03 make sure that you're always up to date
  • 00:01:04 regarding the latest features of power
  • 00:01:07 bi so if you want to take a closer look
  • 00:01:09 at that so you can scroll down right
  • 00:01:11 here and check out the different
  • 00:01:13 articles for the different months for
  • 00:01:14 example but for the moment we only want
  • 00:01:17 to make sure that we install the latest
  • 00:01:19 version of power bi desktop so to do
  • 00:01:22 this we can scroll up again and click
  • 00:01:24 right here now if you do this and follow
  • 00:01:27 the installation guides and then update
  • 00:01:29 power of your desktop well then you can
  • 00:01:31 reopen power of your desktop after this
  • 00:01:36 then you will arrive right here in an
  • 00:01:39 empty power bi desktop project now where
  • 00:01:42 can we find these preview features now
  • 00:01:43 in here well that's quite easy because
  • 00:01:46 you simply have to go to file up here
  • 00:01:49 now go to options and settings right
  • 00:01:51 there so you don't have to click it just
  • 00:01:53 move the mouse right here and then
  • 00:01:56 select the options right there and if
  • 00:01:59 you look at the global options now and
  • 00:02:01 go down to preview features here well
  • 00:02:04 then you can see that you have a lot of
  • 00:02:06 different preview features that you can
  • 00:02:09 activate in power of your desktop of
  • 00:02:10 course the type of preview features
  • 00:02:12 available changes throughout the months
  • 00:02:14 for example these are the preview
  • 00:02:16 features available in June 2017 however
  • 00:02:19 in this video I want to show you one
  • 00:02:21 specific preview function and this is
  • 00:02:24 the one I already activated right here
  • 00:02:25 the new table at metrics visual function
  • 00:02:28 so the only thing is right here is if
  • 00:02:30 it's not ticked on ticket right now pick
  • 00:02:32 this right here like that
  • 00:02:33 and press ok like this now you might
  • 00:02:38 have to restart power of your desktop as
  • 00:02:40 I said in my case it's already activated
  • 00:02:42 and if you just activated it right now
  • 00:02:44 just restart power of your desktop one
  • 00:02:46 more time and as soon as we are right
  • 00:02:48 here we can now finally start well and
  • 00:02:51 to start we need some data right this is
  • 00:02:55 true so let's quickly find some data
  • 00:02:57 that we can use to take a look at this
  • 00:02:59 preview function and for this example we
  • 00:03:02 will just use some New York City Taxi
  • 00:03:03 data you can find the data right here
  • 00:03:05 and the link is of course also provided
  • 00:03:07 down there in the video description
  • 00:03:09 however if you scroll down right here
  • 00:03:12 then you can see different trip sheet
  • 00:03:14 data in CSV format for this example I
  • 00:03:18 simply downloaded the yellow cab right
  • 00:03:20 here for January 2014 so make sure to
  • 00:03:24 download this file right now it's quite
  • 00:03:26 big it's about 2 gigabytes of things so
  • 00:03:28 it might take a few seconds but as soon
  • 00:03:30 as you downloaded that file you can go
  • 00:03:33 back to power of your desktop and then
  • 00:03:35 simply go to get data right here and
  • 00:03:37 select text CSV by double clicking it
  • 00:03:40 and now simply select the file that we
  • 00:03:43 just downloaded as you can see yellow
  • 00:03:45 trip data January 2014 so we click on to
  • 00:03:48 open right here this
  • 00:03:50 and this is fine actually but now we
  • 00:03:55 don't want to load the data into the
  • 00:03:57 data model we want to go to the query
  • 00:03:59 editor first so to prepare the data so
  • 00:04:02 this means to make sure that we only
  • 00:04:04 load the data into the data model that
  • 00:04:06 we really want to analyze so this means
  • 00:04:08 you should press edit right here down
  • 00:04:10 there and now we are in the query editor
  • 00:04:14 and right here well we can see that the
  • 00:04:17 first row is kind of well not correct so
  • 00:04:20 let's quickly get rid of that right now
  • 00:04:22 by simply going to the home ribbon right
  • 00:04:24 here now go to remove rows right there
  • 00:04:27 and now remove the top row if you click
  • 00:04:30 right here and simply type one because
  • 00:04:33 you only want to remove the first row
  • 00:04:34 and press ok so now the rows are fine so
  • 00:04:38 let's take a quick look at the columns
  • 00:04:40 because if you scroll to the right right
  • 00:04:42 here well you can see we have a lot of
  • 00:04:45 columns and for a little preview example
  • 00:04:47 right here I don't think we need all
  • 00:04:48 that information that we can see right
  • 00:04:50 here so let's go to the home ribbon
  • 00:04:52 again and now present to choose columns
  • 00:04:55 in the lower part and select choose
  • 00:04:57 columns once again now we click right
  • 00:05:00 here on to select all columns too well
  • 00:05:02 unselect all columns first and now we
  • 00:05:05 can specify which columns we want to
  • 00:05:07 keep in our power bi desktop project so
  • 00:05:10 this could be for example well I don't
  • 00:05:12 know you pick update I may be the
  • 00:05:15 passenger count and you tryptophans
  • 00:05:17 may be the raid code and I don't know
  • 00:05:20 the tip and the total amount I think
  • 00:05:22 this is fine so this means we will only
  • 00:05:25 keep these selected columns right here
  • 00:05:27 so if you press ok right now this then
  • 00:05:31 you can see that the amount of columns
  • 00:05:33 reduced and this looks fine right now
  • 00:05:35 actually
  • 00:05:36 there is only one less thing that we
  • 00:05:38 could take a quick look at right now so
  • 00:05:40 if you go to the passenger count column
  • 00:05:41 right here and press included arrow to
  • 00:05:43 open the filter and then press load more
  • 00:05:48 this might take a few seconds because
  • 00:05:50 well it's kind of a big file data set
  • 00:05:53 yes you can see that we have 0
  • 00:05:55 passengers in here I don't think it
  • 00:05:58 makes a lot of sense so maybe let's
  • 00:05:59 unselect that to exclude that data from
  • 00:06:02 our beta model in the
  • 00:06:03 stab and press okay right here and then
  • 00:06:07 let's maybe ought to take a look at the
  • 00:06:09 trip distance right here yes and you can
  • 00:06:12 see if we also have zero right here so
  • 00:06:14 let's maybe also unselect this right now
  • 00:06:16 so we did that
  • 00:06:18 so we uncheck that box and press ok and
  • 00:06:21 we won't take a closer look at the data
  • 00:06:23 right now because it's not about the
  • 00:06:25 data it's more about the preview feature
  • 00:06:27 so let's press closing apply right now
  • 00:06:29 right here to load that data now into
  • 00:06:32 our data model so let's press that and
  • 00:06:34 again this will take a few seconds
  • 00:06:37 because as I said the file size is quite
  • 00:06:40 big but it shouldn't take too long
  • 00:06:42 actually and here we are now we now
  • 00:06:46 loaded the data into our data model
  • 00:06:47 right here we under report you so this
  • 00:06:51 is the area where we will create our
  • 00:06:53 table in a few minutes but before we do
  • 00:06:55 that let's take a look at the underlying
  • 00:06:57 data now in the data model so we can
  • 00:07:00 simply click right here to open the data
  • 00:07:02 view and right here the columns right
  • 00:07:05 there are alright but the first column
  • 00:07:07 is not because what I actually would
  • 00:07:10 like to have as we only have data for
  • 00:07:12 January I would like to have the
  • 00:07:14 different weekdays in here and if you
  • 00:07:17 look right here well we have a date and
  • 00:07:18 a time but we cannot see the week there
  • 00:07:20 right now and if you select that column
  • 00:07:23 now and go to the modeling ribbon then
  • 00:07:26 you can change the formatting so we can
  • 00:07:28 change the data type by clicking right
  • 00:07:30 here and change it to date for example
  • 00:07:32 like this yeah we can see the weekday
  • 00:07:36 this is fine but not only the weekday so
  • 00:07:39 let's maybe see the format right here
  • 00:07:41 like this and select daytime and again
  • 00:07:45 yeah we have kind of what we want but
  • 00:07:48 not the weekday only so we cannot select
  • 00:07:51 it by a predefined function the great
  • 00:07:55 thing is we can change the quickly in
  • 00:07:57 power of your desktop by adding a new
  • 00:07:59 column so let's unselect that by
  • 00:08:01 clicking right here and now select well
  • 00:08:04 new column as I just said right here and
  • 00:08:07 now you can see this formula bar and if
  • 00:08:10 we select the first part right here so
  • 00:08:12 that column and name it week they may be
  • 00:08:14 like that and
  • 00:08:16 now we have to write a formula and this
  • 00:08:19 formula is called formats like that and
  • 00:08:22 now we press tab to complete the formula
  • 00:08:25 now we need to write the square bracket
  • 00:08:27 like this and now we can select the
  • 00:08:30 different columns right here as you can
  • 00:08:32 see I'm moving it with the keyboard and
  • 00:08:34 the up and down arrow keys and we need
  • 00:08:36 to pick up daytime so our first column
  • 00:08:39 right here so we can press tab again and
  • 00:08:41 now it's important to write a comma and
  • 00:08:44 now specify the format that should be
  • 00:08:47 retrieved
  • 00:08:48 out of that information in that pickup
  • 00:08:50 date and column and that format should
  • 00:08:53 be well a quotation mark and for example
  • 00:08:55 3 these like that and now write the
  • 00:08:58 second quotation mark and if you now
  • 00:09:00 press ENTER like this well then you can
  • 00:09:03 see in that new column right here that
  • 00:09:06 we now have the weekdays or the first
  • 00:09:08 three letters of each weekday in that
  • 00:09:11 new column so this is almost what we
  • 00:09:14 want to have but there is one problem if
  • 00:09:17 you would now create our table in the
  • 00:09:19 report view then the weekdays would be
  • 00:09:21 sorted alphabetically and actually I
  • 00:09:24 don't want that I would like them to be
  • 00:09:26 sorted like Monday Tuesday Wednesday and
  • 00:09:28 so on and the good thing is that we can
  • 00:09:31 also change the quickly in power bi
  • 00:09:33 desktop to do this we simply have to add
  • 00:09:36 another column by clicking new column
  • 00:09:38 right here
  • 00:09:39 now maybe name that weekday again but
  • 00:09:43 this time week a number like this and
  • 00:09:46 now simply write well weekday like that
  • 00:09:49 press tab again again the square
  • 00:09:53 brackets and again refer to the pickup
  • 00:09:55 daytime so the first column right here
  • 00:09:57 press tab now write another comma and
  • 00:10:01 now we can specify how the weekday or
  • 00:10:04 the week the number should be returned
  • 00:10:06 this means here we would see that the
  • 00:10:08 Sunday is still number one and Saturday
  • 00:10:10 is number seven but I don't like that I
  • 00:10:12 would like to have the second option
  • 00:10:14 this means Monday is the first day and
  • 00:10:16 so on Sunday is the seventh day this is
  • 00:10:19 fine so we can press tab once again and
  • 00:10:22 press ENTER and now we see that we have
  • 00:10:25 a number for the corresponding weekday
  • 00:10:27 so as we defined it Thursdays for
  • 00:10:30 five and so on so now we got that week
  • 00:10:34 the number column but actually we wanted
  • 00:10:36 to sort the weekdays right so we want to
  • 00:10:39 sort it from Monday Tuesday Wednesday
  • 00:10:40 and so on well and if you now select the
  • 00:10:44 weekdays for example like this well and
  • 00:10:46 then go to the modeling ribbon right
  • 00:10:48 there and then go to sort by column then
  • 00:10:52 we can see that the weekday is selected
  • 00:10:54 at the moment and this is not the way
  • 00:10:57 the data should be sorted because it
  • 00:10:58 should be sorted according to the
  • 00:10:59 weekday number because then it would
  • 00:11:01 start with one which would be Monday
  • 00:11:02 continue with two Tuesday and so on so
  • 00:11:06 if we change it now to week the number
  • 00:11:08 here then this should work correctly so
  • 00:11:12 we have to try that now
  • 00:11:13 by going back to report view to finally
  • 00:11:15 create our table so let's go to report
  • 00:11:18 view right here and if we go to the
  • 00:11:20 visualizations column right now well
  • 00:11:22 then we see that we have the table
  • 00:11:23 visual right here and the matrix visual
  • 00:11:25 but we have the same symbols down there
  • 00:11:28 yes because if we look at it you can see
  • 00:11:32 that this is the table preview and this
  • 00:11:34 is the metrics preview so these are the
  • 00:11:37 preview functions that we just activated
  • 00:11:39 so let's not select maybe the table
  • 00:11:42 preview right here and maybe make it big
  • 00:11:44 for you so I clicked right here sorry
  • 00:11:46 okay well and now we can actually start
  • 00:11:49 by dragging and dropping our weekday
  • 00:11:53 here well and as you can see the weekday
  • 00:11:57 is automatically sorted as we find it
  • 00:12:00 you remember this is just due to the
  • 00:12:02 fact that our weekdays are sorted
  • 00:12:05 according to the week ten numbers that
  • 00:12:06 we find so one two seven four one is
  • 00:12:09 Monday Tuesday's two and so on so this
  • 00:12:12 worked fine this means we can now
  • 00:12:14 finally focus onto the functions of that
  • 00:12:17 preview feature now we have the weekdays
  • 00:12:19 right here but what else could we add
  • 00:12:21 well maybe the passenger count so I
  • 00:12:23 simply selected right here in the fields
  • 00:12:25 column and drag it right there and we
  • 00:12:28 could also add the total amount sort of
  • 00:12:31 total money that was paid you can see
  • 00:12:33 I'm simply dragging it again right here
  • 00:12:35 now this is nice
  • 00:12:38 but this doesn't look really good so we
  • 00:12:40 need to a great our data well and we can
  • 00:12:43 also do the easy
  • 00:12:43 leave by simply going back to the
  • 00:12:45 visualization column now click on to
  • 00:12:48 this arrow next to the passenger count
  • 00:12:49 right here and change it from don't
  • 00:12:52 summarize to well the average maybe this
  • 00:12:54 makes sense so we have the average
  • 00:12:56 passenger count of each day so if you go
  • 00:12:59 to average for you yeah it looks better
  • 00:13:02 but still we also have to adjust the
  • 00:13:05 total amount so let's also take them to
  • 00:13:07 the arrow right there and now change
  • 00:13:10 that
  • 00:13:10 well not three average but to the sum
  • 00:13:12 maybe so we can see how much money was
  • 00:13:14 paid in total like this and now you see
  • 00:13:18 that we have our new table created with
  • 00:13:21 the different weekdays the average
  • 00:13:23 passenger count and the total amount
  • 00:13:25 paid on that days in January so this is
  • 00:13:29 all fine actually but that's not the
  • 00:13:30 preview feature that I want to show you
  • 00:13:32 because the preview feature has the
  • 00:13:34 great ability to kind of visualize that
  • 00:13:37 in a better way what does this mean well
  • 00:13:40 if you now go to the format area right
  • 00:13:42 here so click into the symbol now scroll
  • 00:13:45 down a little bit to open the
  • 00:13:47 conditional formatting there let's go
  • 00:13:49 down again so that you can see
  • 00:13:50 everything well then you can see that if
  • 00:13:53 you click right here you can select the
  • 00:13:55 two different columns that we created so
  • 00:13:57 the passenger count and the total amount
  • 00:13:59 now let's select the passenger count
  • 00:14:01 right here and now this data bars this
  • 00:14:05 is the new preview feature that we
  • 00:14:07 activated because if we click on two on
  • 00:14:09 right here well then you can see that
  • 00:14:13 the average passenger count is now
  • 00:14:15 visualized by that bars right here
  • 00:14:17 which is really great and if you're not
  • 00:14:19 happy with the way the data is this cut
  • 00:14:21 right here then you can simply change
  • 00:14:22 that by going back to the format area
  • 00:14:24 and going to the advanced controls and
  • 00:14:27 as you can see you can now change the
  • 00:14:29 name for example or only show the bar so
  • 00:14:32 if click right here and press ok then
  • 00:14:34 you see that the value is gone but I
  • 00:14:36 think we want the value so we can go
  • 00:14:38 back to the advanced controls and
  • 00:14:40 unselect that then you can define the
  • 00:14:44 minimum and the maximum the color of the
  • 00:14:46 bar and the bar direction so you can
  • 00:14:48 play around with that if you want you
  • 00:14:50 but the great thing is actually that you
  • 00:14:52 have that visual impact now of your
  • 00:14:55 value so we can press ok right here
  • 00:14:57 and now we can simply change that from
  • 00:15:00 the average passenger right here to the
  • 00:15:02 total amount there and if you know also
  • 00:15:04 take the data bars from off to on like
  • 00:15:07 that well then we can see that we have
  • 00:15:10 the same visual or the same indication
  • 00:15:12 by the bar chart for the total amount
  • 00:15:14 and this is actually the preview feature
  • 00:15:17 for the table visual but of course
  • 00:15:20 that's not only available for the table
  • 00:15:22 it's also available for a matrix so
  • 00:15:25 let's also take a look at that quickly
  • 00:15:27 so we can simply press back to report
  • 00:15:29 and here and now unselect that visual we
  • 00:15:32 created and now select the matrix right
  • 00:15:35 there and we can position it right here
  • 00:15:37 it's fine because we will press again
  • 00:15:40 that maximized window right here to make
  • 00:15:42 it bigger and now we can define what
  • 00:15:45 should be displayed in the rows the
  • 00:15:47 columns and the values before we only
  • 00:15:49 had the values to remember in that table
  • 00:15:51 visual however we can again use the week
  • 00:15:54 they may be as the rows I think this is
  • 00:15:57 fine like this you can see again the
  • 00:15:59 sorting is correct and then we can use
  • 00:16:02 for the columns maybe the I don't know
  • 00:16:05 the rate code for example that select
  • 00:16:08 that and then we can select maybe TTIP
  • 00:16:11 as the value like that as you can see
  • 00:16:15 you now create that metrics and we will
  • 00:16:18 talk about the details of that metrics
  • 00:16:20 now the only thing I want to show you is
  • 00:16:22 that you can also add that great bars
  • 00:16:25 that we just saw by going to the tip
  • 00:16:28 amount right here pressing through the
  • 00:16:30 arrow now go to conditional formatting
  • 00:16:32 and now also select the data bars as we
  • 00:16:36 just saw it in the format area so we
  • 00:16:39 present to the data bars and we keep it
  • 00:16:41 the way it is you know that menu already
  • 00:16:43 and if you know press ok well then you
  • 00:16:47 see that the data bars are also added to
  • 00:16:50 our matrix right here and this is it
  • 00:16:52 actually this is what I wanted to show
  • 00:16:54 you so take a look at the official power
  • 00:16:58 bi blog to learn more about the latest
  • 00:17:00 features and about the latest preview
  • 00:17:02 features that porgy offers
  • 00:17:03 additionally I hope you saw that it's
  • 00:17:06 quite easy to sort your weekdays
  • 00:17:08 correctly in power bi desktop the way we
  • 00:17:10 did it right here
  • 00:17:11 and with that I can only say thanks for
  • 00:17:14 watching and see you in the next videos
  • 00:17:16 bye bye