Question was hey i um sometimes the comma doesn't quite work as a delimiter when i'm trying to clean up data what if we have other things like spaces and dashes.
And commas how do we handle that for example any threw this stuff on at me what about this name you know how do i how do i separate kind of the first name and last name in this case.
So what we did is we built these three samples and they get increasingly more challenging and we're going to step through this so the names one you would see has our.
Nemesis right the gen in it weld and we're going to try to extract the last name but you know try to do that for other names as well we upped the.
Have in addition to janet weld uh you know and and these dashes we have some which are in the format of last name.
Comma first name how about that to make things interesting so let's dive in see how it's done actually before we dive in let's talk about this so hey.
We realized that with power bi one of the biggest challenges is that everything looks easy until you try using it with your own data and then something nasty happens you either hit.
The query editor blues or dax wall or get your relationships twisted or not and that's that is not some place where we want you to be stuck we want to help you get over.
That so uh and we love answering barbie a question so just go to learnpowerbi.com question we would put that in the.
Posts Related:
Link somewhere in the video in the
Description or in the corner as well but hey easy to remember learnpowerbi.comquestion that's going to talk about all the.Different ways you can get help uh you know and get your questions answered including on live zoom calls right so uh so yeah all right so know that you have that resource because.
We know that can be frustrating so let's see how we can help rico so i'm going to launch our favorite kitchen of power bi.
All right so the power query query editor or kitchen of power bi and we're gonna start with a simple example names one so again the the only challenge here is.
That well i mean um you know so so yeah so we have first name last name but we have jan in it well and now i noticed.
This frank is from netherlands as well that's so frank when dylan god what's what's wrong with these people in netherlands no just kidding it's all good it's all good.
My dad has a middle end too so i shouldn't be complaining right so um all right so now of course let's do it the wrong way first right so let the wrong way is that we're going to split.
It by delimiter right so we're going gonna split this column by delimiter and let's just say space and let's see what happens right so i'm.
Gonna hit okay and of course well this is kind of weird and maybe we can still work with this of course.
Well uh yeah i ended up with like four
Different parts frank when delton ended up like this yeah so it's not what we want so let's try that again so i'm going to delete these steps so again i'm.Just going here and clicking on these steps to delete them okay we're back at the source and now we're going to try the split again but slightly differently so i'm going to say.
Slight split by delimiter but i had kind of clicked okay really quick because i didn't want you to see this but there are other options here which is leftmost and rightmost.
Delimiter and i think for the most part at least for us to get extract the last name well we can say the rightmost delimiter right so yeah right so so we'll say you know.
Break at the last one you give me the last word after the space so this would work better as you would see so right most delimiter and bingo so at least now we have a clean last name now.
There is of course further discussion about okay can we live with this as the first name or uh we need some for the cleanup there we'll leave that for another day.
But at least you saw how using that example that you can you can simplify things much better okay are you ready for the next example so next example is a little bit trickier.
But we got this yeah so again of course we have the janet weld in franklin delta in case but you can make things interesting we have grace.
Dashteo and greg dashberg and again guys that's the reality and that's why power bi yeah sure there is the concepts which we teach in our course as well but the.
Emphasis in our program is always learning by doing because it's when you start working with real data and real data is messy data that.
You you know actually you solidify the concepts right that's real learning all right so so how do we do this now one phrase that i use and i believe in it which is that human learning comes.
Before machine learning which is i often try to think about that how would a human do it and and then you have to slow it down because humans are intuitive you know we.
Can just look at something like oh yeah i know the first name is grace last name is geo but it was like how how did you do that right i mean you know so you got to slow it down break it down.
And and dump it down and then teach that to the machine so hey i know someday robots may rule the world we're not there yet and and really the the key thing is rbi is.
You got to dumb yourself down you got to slow yourself down and that applies with the kitchen of power bi surprisingly it also applies with dax so yeah it's not that you are not smart.
Enough if you're struggling with this stuff you're not dumb enough we can help you with that join our program all right cool all right cool.
Guys that's good okay so let's uh let's do this let's do this so so clearly we have two different things so what we're going to.
Start off right away is we're going to duplicate this column so again i love love the toolbar over here the ribbon i mean oh gosh it's so awesome so names two.
Maybe i'm gonna i'm gonna change it to names to a and names to be kind of keep it clean and what i'm gonna do is i'm going to split names to a.
The old way the same way we did for um the in the earlier example names one so we're going to split by delimiter and again right most delimiter we're choosing that hitting okay.
And we have that right but again you see that it doesn't quite work for grace theo greg barry oh gosh you guys trouble makers.
Let's go to this one and this one we're going to solve only for grace dear greg bear right so we come in here and we say split column by delimiter.
And in this case we're gonna it doesn't say dash so we're just gonna use custom right so it has a few options here but you know you can always choose custom um.
You know what i've never tried if you can you specify like can can ampersand ampersand can like two characters be delimiters maybe they can it'll be interesting to try it out but hey.
Focus i'll be focused so we're going to put dash because grace dash d or greg dash bearing we're trying to split them and uh here sometimes i i try to be careful so here it says each occurrence.
Of the delimiter uh left most right most i can i can use left most or right most depending on what data is uh sometimes i also click in the.
Advanced options and oh yeah you know sometimes you can say how many maximum knows you know what i think obviously in our data set we're fine either way so i'm gonna hit okay.
All right so now is we're gonna bring it together and again guys if you see this now maybe you were not clear how to do this at the.
Beginning and and believe me when i say that so power query sometimes people are struggling with something it was it's just like oh.