8 hours 33 minutes
Okay, This is the final lesson of this module. And in this listen, we're going to be talking about how to manipulate and fix data you have important. So we have learned how to import data,
actually have seen how to import data from different kind off sources, different kind of programs, different types of data. Also, you have seen the difference between
opening and importing data.
And now let's go to some examples on how to fix this data. So
presumes that there is something wrong with that data. So way have imported this
lines from three different files.
And we have by mistaking port IDs
line with which is basically a header off the file and then some data, of course, that there could have been much more. So it's just the example. So we did it from what say three people
and we want to put it together. And now we we have this problem that we have multiple rows had a rose, which we don't need. Now if we have just three files that it's no big deal that can imagine you have 50 of them or something like that.
in order to do that. Ah, you just go to data.
Uh, tab, you go to date the tools
form, uh, group, and you click on remove duplicates
and then you'll get this dialog box in which you excel. We will ask you. Which columns do you want to look in for? Duplicates.
And of course, you have this check box which has made it has Heather's.
And ah, in this case, you wantto uncheck it
because if it's checked, then it won't look at it. So So the great portion of the worksheet is what excel we'll look at when looking for duplicates.
we are jacket and then we can say Okay. And we got this information that to duplicate values were found and removed. Four unique. Well, he's remained. So this is a unique rebel. You and all of these mileage is
our unique. Why? Because in order for excel to remove it, everything has to be identical. So every single cell in a row in a selection has to be identical in between two rows. In order for one of them to be removed, The one removed is always the one that is below the 1st 1
that is part of the group off identical rose. So we had three.
He had a rose, two were removed. The 1st 1 was kept just to give you an idea what would happen if we would
not uncheck? My data has headers
s O. If it's really we checked,
we'll have one duplicate really removed because Excel will then treat
the mileage as a heather
and, uh, sorry. The first line is a header. And then look what we have duplicated and we have two more headers. And the 1st 1 will remain because in that case, excel things. Okay, this is the 1st 1 The 2nd 1 is duplicate. I'm going to remove it.
So there's the first thing. The second thing is, can you imagine that you have imported the data from, let's say, a word or text file
and it was delimited by spaces instead of commas or tabs. And you made a mistake when importing and you got
all of the data in one column
and the values were separated by
You select the problematic data
and you click in text two cones
and it as crazy, the limited or fixed with because it can do it with fixed with this well, but you say delimited,
you get the preview of selective data
and then you choose what is the deal emitter? So in this case, it is space.
It is pretty much the same, like would see us, we import.
But now it's going to fix it. And you see, the preview is going toe Move these numbers in three columns like there should be
and we just pricks next.
And it the same thing, like when importing it asks us what is going to be the former
the new generated celeb do populated cells
you want to General because in this case, these air numbers and you,
if it's generally will convert in America, is the numbers. So you say finish
and you get it. Those numbers. You see that because it's automatically aligned to the right.
the third thing doesn't actually have to do nothing, something specifically with important data.
But if you have,
you've had this, for example, some list off part numbers for spare parts,
and now you have, ah, new nomenclature which says that we are losing the first number and we're no longer having bashes. We're just going to have dot between them
second and third number
So this is the road data.
In the 1st 2 lines, you enter how it should look
and you just become flesh feel
and excel presumes what you wanted to do
And, uh, it feels it automatically.
For example, we have some text and some number
and then some texts and some member
And then again,
some fixed and some number
next, separated by space and a number.
And then you say, Just here, 20 in here 2340
and the You click here you do the flesh feel
and he doesn't see the better. So this is the explanation. Why So let's change this
and remove from here one letter. So let's say we have five letters and numbers and then we do flesh feel
it still doesn't do it. So these numbers have to be pretty
there has to be two digits.
And if we change this to 23
and flesh feel, then excel, we say OK, I have five letters than space,
then two digits and I want to keep just two digits, and it will do it.
So, uh, this is how flesh will works.
these are three methods to fix the problems you have with your important data,
and that's pretty much all about importing and fixing them. And let's just do one thing more
and this is how to export data.
So if you could conceive as
you big folder
and you get this and then you have Excel Workbook
And here you have all the possible export for months, which are pretty much the same
as the important ones.
Mmm. So so that's it.
And you can just pick one you want. For example, you can
you can choose C s fi comma delimited
and its module five
and you can click here
and it tells you the same thing that
doesn't support support work books that contain multiple sheets because he s he doesn't support that.
So you want to say active sheet only? You say OK off course.
And some features in your world we might be lost. Of course, because CSP just a text file. They want to keep using that format.
you get it. It's now module five C s. V.
And when you look a TTE
file we were talking about.
So if we look at this file,
be open it in excel.
Yeah, that's the same file.
But we can also open that bid
example Note bed
and you would get the C S V file.
Just comma separated values. So we have
these air combs, these air rose, and this is how CS refile looks.
So this is basically all about exporting data. So,
uh, that's it.
And with this, we're concluding this model.
Sorry. There's going to be a recap after this.
Data Analyst Excel
The Data Analyst Excel Test is a premium Cybrary assessment created by iMocha and intended ...
Monitor a Web App
In this IT Pro challenge, participants get hands-on experience activating server logging and using Application ...
Learn On Demand