[Performing Data Manipulation]

Data Blending

The primary focus is using test-based functions to manipulate how data is displayed.

TRIM function

Removes leading or trailing spaces from data.

CLEAN function

Removes non-printable characters from data.

LEFT function

Displays the first four characters of a cell in Excel.

RIGHT function

Extracts the last four characters of a text string.

MID function

Used to extract specific characters from the middle of a text string.

UPPER function

Converts all letters in a text string to uppercase.

LOWER function

Converts text to lowercase.

PROPER function

Capitalizes the first letter of each word in a text string.

LEN function

Determines the number of characters in a text field.

CONCATENATE function

Merges two or more text fields into one.

Delimiter

A character used to split text into parts, such as tab, commas, or pipe.

Comma

The most common delimiter used in text fields.

Parsing strings

Organizes text into structured data by splitting it into parts.

NOW function

Returns the current date and time.

TODAY function

Returns the current date only.

DATEDIFF function

Calculates the number of days between two dates.

NETWORKDAYS function

Calculates the total number of business days between two dates, excluding weekends.

WEEKDAY function

Returns a number from 1 to 7 to represent the day of the week for a given date.

WEEKNUM function

Calculates the week number of the year for a given date.

IF function

Checks if a condition is met and returns a result based on it.

ISNULL function

Checks for null or empty values in a data set.

AND function

Returns true if both specified conditions are true.

OR function

Returns true if either of the specified conditions is true.

SUM function

Adds up all the values in a column to produce a total.

DISTINCT COUNT function

Counts the number of distinct records in a column.

AVERAGE function

Adds up all the values and divides by the count of values.

MAX function

Finds the largest value in a column.

MIN function

Finds the smallest value in a column.

System functions

Functions packed with reporting/analysis tools to perform certain tasks.

Importance of system functions

They automate tasks in reporting tools, saving time and effort.

Key information in reporting

Can track page numbers, last refresh date, and report name.