Coding

Power BI Desktop – Column from Examples

  • 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