- 00:00:00 welcome to another video about power bi
- 00:00:03 desktop in this video I want to show you
- 00:00:06 a quite nice function the column from
- 00:00:09 example function let's take a look so I
- 00:00:13 prepared this query right here with 4
- 00:00:16 different columns so we have one column
- 00:00:18 with different car manufacturers one
- 00:00:21 column with the headquarters of this
- 00:00:23 specific car manufacturer the number of
- 00:00:25 vehicles sold in 2015 and when they
- 00:00:28 published that information now this is
- 00:00:31 interesting but the more interesting
- 00:00:32 thing is that column from example
- 00:00:34 function I just talked about because if
- 00:00:37 you look at the different columns right
- 00:00:38 here you see that we have some problems
- 00:00:40 in the first column for example I would
- 00:00:43 like to exclude the second word so I
- 00:00:47 don't want to have folks wagon group but
- 00:00:49 only for clogging the same thing is true
- 00:00:51 for Nissan and Renault so to do this we
- 00:00:55 could now simply use a function like
- 00:00:56 extract we talked about that in our
- 00:00:58 initial project video however with the
- 00:01:02 column from example function we can do
- 00:01:04 that really easily so the only thing we
- 00:01:07 need to do is we need to select that
- 00:01:09 column right here now go to the add
- 00:01:12 column ribbon right there
- 00:01:13 and now select column from examples
- 00:01:16 right here if you click it in the lower
- 00:01:17 part right here and then simply select
- 00:01:20 from selection so only the selected
- 00:01:22 column then and preset then this menu
- 00:01:26 opens well and with this menu we have a
- 00:01:29 new column right here this one and in
- 00:01:32 this column I can now type some text one
- 00:01:35 example could be Toyota right so the
- 00:01:39 content of the first column in this
- 00:01:40 first row if a temperate centre like
- 00:01:44 this then I see that nothing changed
- 00:01:48 right so we still have folks walking
- 00:01:50 group right here we have to miss some
- 00:01:52 Japan and Renault France but what if I
- 00:01:56 now write Volkswagen in that second row
- 00:02:00 and press ENTER again then it gets more
- 00:02:04 interesting right because now power bi
- 00:02:06 understood that I only want to keep the
- 00:02:09 first word so basically the text that is
- 00:02:12 left of a specific
- 00:02:14 in our case to space but nevertheless if
- 00:02:17 we now press ctrl + Enter then we
- 00:02:21 created a new column which contains
- 00:02:23 exactly the values that we want to have
- 00:02:25 now as I said we could have done that
- 00:02:28 with an extract function for example so
- 00:02:30 there are more ways on how to get that
- 00:02:32 result but we can do more with the
- 00:02:34 column from examples function let's take
- 00:02:36 a look at the second column now this
- 00:02:38 headquarters column so in this column we
- 00:02:41 have the different countries but Japan
- 00:02:44 right here France right here and Germany
- 00:02:47 there are some problems with this
- 00:02:50 capital letters in it I would like to
- 00:02:52 get rid of those and to do this I could
- 00:02:55 for example simply replace it so I could
- 00:02:58 simply replace the capital letters right
- 00:02:59 here with the replace function or I can
- 00:03:02 again use the column from examples
- 00:03:04 function let's try it out again so be
- 00:03:07 again select column from examples from
- 00:03:09 the election right here and now we
- 00:03:11 simply click right here into this row
- 00:03:14 that we have that mistakes in the way
- 00:03:16 Japan is written and if we now write
- 00:03:19 into this column Japan the way we want
- 00:03:21 it to be and press ENTER now then it
- 00:03:25 gets more interesting right because now
- 00:03:27 probably I understood that only the
- 00:03:29 first letter should be a capital letter
- 00:03:31 and it also applied it for France right
- 00:03:34 here like this and for Germany down
- 00:03:37 there if we now press again ctrl + Enter
- 00:03:41 then we see that we have this new column
- 00:03:44 again now with the countries written
- 00:03:47 correctly so we now saw that we can use
- 00:03:50 this function for specific delimiter
- 00:03:52 operations and also on text specific
- 00:03:54 topics like we saw it in this column
- 00:03:56 let's take a look at another example we
- 00:04:00 have that published column right here
- 00:04:01 let me select it again what if I want to
- 00:04:04 know on which they this data was
- 00:04:07 published let's again select the column
- 00:04:09 from examples function and again from
- 00:04:11 selection and now I know that the 11th
- 00:04:15 of May 2016 was a Wednesday and if I
- 00:04:19 write Wednesday right here
- 00:04:21 oops like this and press Enter
- 00:04:26 then you can see that power bi
- 00:04:27 immediately understood what I want to
- 00:04:30 display right here and based on the
- 00:04:32 information it has in that column so the
- 00:04:34 date it was able to now retrieve all the
- 00:04:37 corresponding weekdays of the remaining
- 00:04:40 dates this is also nice right so we can
- 00:04:44 also use the function on date operations
- 00:04:47 let me show you one last example where
- 00:04:50 this function might be a big help to you
- 00:04:52 so what if I want to combine two columns
- 00:04:55 let's say you want to combine the part
- 00:04:58 of manufacturer column and the
- 00:05:00 headquarters column well to do this we
- 00:05:03 simply again select column from examples
- 00:05:05 but now we could also select from all
- 00:05:08 columns like this if we do that we now
- 00:05:12 simply need to look for a value in a
- 00:05:14 column that we want to start with in our
- 00:05:17 case I would for example use Misun
- 00:05:20 because this is something that we
- 00:05:21 corrected so we have missing right here
- 00:05:24 in the corrected form and right there in
- 00:05:27 this incorrect form so if I now refer to
- 00:05:30 listen right here then power bi know was
- 00:05:33 that deep manufacturer should be
- 00:05:35 retrieved from that column so let's go
- 00:05:38 to a list and then and right Misun like
- 00:05:42 this and now right the headquarters
- 00:05:45 Japan press Enter and again you can see
- 00:05:50 that this worked perfectly and if I now
- 00:05:53 press control enter we have an
- 00:05:55 additional column so we also know that
- 00:05:57 we can combine columns with that
- 00:05:59 function but I want to combine that
- 00:06:01 columns even better so let's select it
- 00:06:04 one less time so the column for example
- 00:06:06 function and from all columns and now
- 00:06:10 select this one right here and right in
- 00:06:12 this Sun and now I will simply add is
- 00:06:17 headquartered in Japan so we have Nissan
- 00:06:23 which is an information we have in this
- 00:06:25 column we have the headquarters which is
- 00:06:27 information of this column and is
- 00:06:29 headquartered in as a simple text if we
- 00:06:32 now press Enter
- 00:06:33 we can see that this also works and if I
- 00:06:37 now press control enter again
- 00:06:39 we also have this column created so this
- 00:06:43 is it this was just a little example on
- 00:06:46 how to use this column from example
- 00:06:49 function so maybe this helps you
- 00:06:51 sometimes when you work with power bi
- 00:06:53 desktop and the only thing I can say
- 00:06:55 right now is thanks a lot for watching
- 00:06:57 and see you next time