Data Manipulation Techniques
Executing Data Manipulation Techniques
Short description explaining what the learner will understand after completing this study map.
Sorting Data
Sorting rearranges how records are displayed without changing the values in the fields. Analysts use sorting both for presentation and for exploring unfamiliar data.
Ascending & Descending
Text: A–Z or Z–A. Numbers: minimum-to-maximum or maximum-to-minimum.
Multi-sort
Sort within a sort—for example, last name first, then first name.
Top N / Bottom N
Show only the highest or lowest N records, such as the top 25 ordered products. This also filters the result.
Custom Sort
Apply a meaningful business order when alphabetical order is inappropriate, such as education level.
Sorting changes record order for display; it does not change the underlying field values.
Filtering Data
Filtering reduces the records presented. Requirements should determine where filters belong and whether users need comparison data beyond the immediate reporting period.
Back-end Filters
Queries can restrict source data before it reaches a report. Date fields are common filter targets.
Date Range
Filter between a starting and ending date. Dynamic reports often expose date controls to consumers.
Hard-coded Visual Filters
Automatically applied to a visual, page, or report and not adjusted by the user.
Interactive Filters
Slicers and controls let consumers choose the subset they want to investigate. Visuals can also filter other visuals.
SQL date-filter example
SELECT SalesOrderID, OrderDate
FROM Sales.SalesOrderHeader
WHERE OrderDate = CAST(GETDATE() AS DATE);Aggregation & Reduction
Aggregate functions operate on groups of records. Reduction decreases data volume, but must be performed carefully so the analysis remains valid.
SUM
Adds values to produce a total.
COUNT
Counts records.
DISTINCT COUNT
Counts unique values only once.
AVERAGE
Totals values and divides by the count.
MAX / MIN
Return the largest or smallest value.
Cleaning & Transforming Strings
Text functions clean unwanted characters and transform text into forms that are easier to integrate, compare, group, and report.
Extra spaces and non-printable characters can prevent otherwise identical values from matching in joins, lookups, groups, and aggregates.
Date Functions & Date Tables
Date functions derive useful time attributes and durations. Date tables provide a complete calendar so reports can represent dates even when no transaction occurred.
NOW() / TODAY()
Return current date-time or current date.
DATEDIFF()
Calculates elapsed time between a start and end date.
NETWORKDAYS()
Counts business days between dates, excluding weekends.
WEEKDAY / WEEKNUM / MONTH
Derive day-of-week, week number, or month from a date.
Combining, Parsing & Standardizing Text
Analysts frequently merge fields, split strings into components, and standardize noisy text before analysis.
^\(\d{3}\)\d{3}-\d{4}$ matches a phone number in the form (xxx)xxx-xxxx.
Derived Variables, Recoding & Scaling
New variables can be derived from existing fields. Recoding changes values to improve meaning, grouping, standardization, or suitability for analysis.
Logical Functions & Imputation
Logical functions test conditions and return values. Imputation replaces missing data with estimated values when that choice is justified.
IF
Tests a condition and returns one result when true and another when false.
ISNULL / ISBLANK
Tests whether a value is missing.
AND / OR
Combine multiple logical conditions.
Imputation
Possible methods include using an average or a predicted/historical value. Document the method used.
Not every missing value should be imputed. Leaving a value missing—and documenting it—may support a more accurate analysis.
Knowledge Check
Check your understanding of the major concepts in executing data manipulation techniques.