Data Manipulation (Data Cleaning)

Data Cleaning Through Excel Or Power BI

1.Finding the Duplicates
2.Looking for Spelling Errors
3.Handling the Missing Data
4. Convert Data
5.Merge Data 

Finding the duplicate
-  Select the Data Set
- Click Conditional Formatting
- High light cells rule (Duplicate values)

After that go to conditional formatting and clear the Rules.

Next
Go to the Data and delete the Duplicate Data.
 - Remove Duplicates

Handling the Missing Data
Use the if statement
e.g
=if(A2,"",99,A2)

That's mean if u want to check a Null values in a A2 column. First check the A2, contains any Null values then if it is the add 99 other wise return the A2 value.

Convert Data

Some Use full commands

 - Convert to text 

=Text(A2,"dddd") -  Return the Date

=Text(A2,"mmmm") -  Return the Month

=Text(A2,"YYYY") -  Return the Year

Using the Nested Function (that means function inside a function)

Right () -  can be used to remove characters from the front of the string.

Len () - Return the length of a string 

= Right(A2,Len(A2)-4)

e.g

Basic Excel Analysis Clean & Transformation Data.

Some useful functions

= VLOOKUP

Excel VLOOKUP Function

1. Summary. VLOOKUP is an Excel function to lookup and retrieve data from a specific column in table. ...
2. Lookup a value in a table by matching on the first column.
3. The matched value from a table.
4. =VLOOKUP (value, table, col_index, [range_lookup])
5. value - The value to look for in the first column of a table.

Create Spaces 

= Trim(B2)

Select the cell then use the TRIM function.

ISO Date

Using this function 

=TEXT(A2,0000-00-00)+0

Converting the text to date format.

Another way is 

- Select the Cell then

- Go to Data

- Click Text to column

You can use the Power Query as well.

Some other useful functions

=Upper Function

= Lower Function

=Proper Function