Data Literacy, Quality and Preparation

Introduction

Data visualization is only as trustworthy as the data on which it is based. A polished dashboard can communicate a false conclusion when records are duplicated, measures are aggregated at the wrong level, dates are interpreted incorrectly, or missing values are silently excluded. Data literacy therefore precedes visual literacy. Analysts must be able to identify what data represents, assess its quality, prepare it systematically, and explain the assumptions behind the resulting analysis.

This chapter presents a practical foundation for preparing data for Tableau and Power BI. It examines data types, structures, granularity, data-quality dimensions, cleaning, reshaping, joining, aggregation, validation, and reproducible workflows. Tableau Prep and Power Query are used as examples of professional preparation environments. The emphasis is not on memorising software commands, but on understanding why each operation is performed and how to determine whether it has produced a defensible result.

Learning Objectives

By the end of this chapter, you should be able to:

1. Distinguish common data types, structures, and levels of granularity.
2. Diagnose completeness, validity, consistency, uniqueness, accuracy, and timeliness problems.
3. Clean, reshape, join, filter, and aggregate data appropriately.
4. Explain the difference between joins, unions, relationships, and aggregations.
5. Validate prepared data using rules, reconciliations, and sampling.
6. Design a reproducible workflow using Tableau Prep or Power Query.
7. Recognise ethical, governance, privacy, and documentation responsibilities in data preparation.

1. Data Literacy as an Analytical Capability

Data literacy is the ability to read, interpret, question, create, and communicate with data. In business analytics it includes more than technical manipulation. A data-literate analyst asks:

• What entity does each row describe?
• What does each field mean, and in what unit?
• When and how was the value collected?
• Which values are measured, estimated, derived, or missing?
• What decisions will the analysis support?
• What errors or biases could affect the conclusion?

A useful distinction is between a data value, its meaning, and its provenance. For example, the value 1250 might represent revenue in dollars, units sold, or a customer identifier. Without a definition, the value cannot be interpreted safely. Metadata—such as a data dictionary, field description, unit, source, and refresh frequency—provides this context.

Data types

Data types describe the nature and permitted operations of values.

A field can be stored in one type but conceptually belong to another. A customer ID may contain only digits but should normally remain text, because arithmetic on identifiers is meaningless and leading zeroes may be significant. Similarly, a postal code is generally categorical rather than numeric.

Data structures

A structured dataset has a defined schema, such as a relational table. A semi-structured dataset uses tags or nested structures, such as JSON or XML. Unstructured data includes documents, images, audio, and free-form text. Tableau and Power BI commonly begin with structured or semi-structured sources, although unstructured material may first require extraction or classification.

A tabular dataset usually contains:

• Rows, representing observations or records;
• Columns, representing attributes or measures;
• Keys, identifying entities or linking tables;
• Measures, which can be aggregated, such as revenue;
• Dimensions, which describe or classify observations, such as product or region.

A well-designed analytical table generally follows a clear rule for what one row means. This rule is the table’s grain or granularity.

2. Granularity and the Meaning of a Row

Granularity is the level of detail represented by each record. Examples include one row per order, order line, customer, store-day, or product-month. The same business data can be stored at several grains, but measures must not be combined without understanding those differences.

Suppose an order table contains one row per order and an order-line table contains one row per product within an order. If an order worth $100 contains four lines, joining order-level revenue to the line table may display $100 four times. Summing the joined revenue produces $400, even though actual revenue is $100. This is a classic fan-out or duplication problem.

Before transforming data, write a grain statement such as:

Each row represents one product line on one customer order, identified by OrderID and LineNumber.

This statement helps determine whether a key is unique, whether a join is safe, and whether a measure may be summed.

Keys and cardinality

A primary key uniquely identifies a row in its table. A foreign key refers to a key in another table. Common relationship patterns are:

• One-to-one: each record in one table matches at most one record in another.
• One-to-many: one customer has many orders.
• Many-to-many: many products appear in many promotions, requiring a bridge or mapping table.

Cardinality should be tested rather than assumed. A field described as an ID may contain duplicates, blanks, or inconsistent formatting.

Figure: A reproducible preparation workflow with a feedback loop from validation to cleaning and shaping.

3. Dimensions of Data Quality

Data quality is fitness for an intended use, not an abstract property that is identical in every context. A missing telephone number may be acceptable for regional sales analysis but unacceptable for a call campaign.

Completeness

Completeness concerns whether required values are present. For a field with N records, where n_v are non-missing, the completeness rate is:

This measure assumes that missingness can be identified consistently and that every record has equal importance. It does not establish that present values are correct.

Validity

A value is valid when it conforms to an allowed format, range, domain, or rule. Examples include a percentage between 0% and 100%, a recognised country code, or a date that can be parsed. A value can be valid but inaccurate: 2024-01-01 is a valid date even if the correct date was 2024-01-02.

Consistency

Consistency means that the same concept is represented similarly across records or sources. Problems include United Kingdom, UK, and U.K., or sales reported in dollars in one file and pounds in another. Standardisation must preserve meaning; indiscriminately converting text to upper case may damage names or codes.

Uniqueness

Uniqueness concerns duplicate records where only one occurrence should exist. A duplicate count is meaningful only after the expected key is defined. Two rows for the same customer may be legitimate if the grain is customer-month, but erroneous if the grain is one row per customer.

Accuracy and timeliness

Accuracy is closeness to the real-world value. It often requires comparison with a trusted source, transaction document, or domain expert. Timeliness concerns whether data is sufficiently current for the decision. A daily inventory dashboard may be unusable if its source refreshes monthly.

Missing data mechanisms

Missingness may be:

• Structural: the value does not apply, such as apartment number for a detached property.
• Operational: a system failed to capture it.
• Informational: the value was deliberately withheld.
• Unknown: the reason is not recorded.

Replacing all missing values with zero is dangerous. Zero means a measured quantity of none; missing means the quantity is unknown or unavailable. Analysts should retain a missing-value indicator where the distinction matters.

4. Cleaning and Standardisation

Cleaning should be rule-based, documented, and performed with awareness of the business meaning. Common operations include:

1. Removing leading and trailing whitespace.
2. Standardising case where appropriate.
3. Replacing known aliases using a reference table.
4. Parsing dates with an explicitly specified locale.
5. Converting numeric text to numeric types after checking symbols and separators.
6. Handling nulls according to documented business rules.
7. Detecting impossible values and outliers for investigation.
8. Removing duplicates only when the duplicate criterion is justified.

Dates and locale

The string 03/04/2025 may mean 3 April or 4 March. A robust workflow uses an unambiguous source format, such as ISO-style 2025-04-03, or explicitly specifies locale during parsing. Date-time fields also require attention to time zones. A transaction recorded at 23:30 UTC may belong to the next calendar day in another location.

Numeric fields

Currency symbols, thousands separators, parentheses for negatives, and percentage signs can prevent numeric conversion. The analyst must determine whether 5% should become 0.05 or 5, depending on the model’s convention. Units must be recorded in metadata.

Outliers

An outlier is an observation unusually distant from others, but it is not automatically an error. A very large order may be a genuine wholesale transaction. Outliers should be investigated using source records and domain knowledge rather than deleted solely because they affect a chart.

5. Reshaping Data

Reshaping changes the arrangement of data without necessarily changing its underlying facts.

Wide and long formats

In a wide table, categories often appear as separate columns:

In a long table, category values are stored in rows:

Long format is often preferable for visualization because one column can represent the category and another the measure. In Tableau Prep, pivoting converts fields between these forms. In Power Query, Unpivot Columns performs a comparable operation. The analyst should check that the resulting grain remains clear and that generated attribute names are correctly interpreted.

Split, merge, and derive

A field such as London, UK may be split into city and country. Conversely, separate year and month fields may be combined into a date. Derived fields—such as profit calculated from revenue and cost—should include a definition and unit. A derived measure is not independently observed and may change if its source fields or business rules change.

6. Combining Tables Correctly

Union or append

A union stacks rows from tables with compatible columns. For example, January, February, and March sales files can be appended into one table. Column names and data types should be aligned first. A union is appropriate when tables have the same grain and represent additional observations.

Join

A join combines columns from two tables using matching key fields. Common join types are:

A left join is useful when a master list must be retained even if supplementary attributes are missing. However, if the right-hand key is not unique, the join can multiply rows. After every join, compare row counts, key counts, and important totals.

Relationships and semantic models

Power BI models often use relationships between fact and dimension tables rather than physically joining every table. Tableau can also use relationships in its logical layer. This approach can preserve table-level grain and allow the analytical engine to generate queries in context. It does not eliminate the need to understand cardinality, filter direction, ambiguous paths, and many-to-many relationships.

A common star schema has a central fact table containing events or measurements and surrounding dimension tables containing descriptive attributes. A sales fact table might contain order-line revenue, while product, customer, date, and store tables provide context. Dimensions should generally have one row per dimension key at the relevant version or time state.

7. Aggregation and Measures

Aggregation summarises detailed values. Common operations include sum, count, distinct count, average, minimum, and maximum. The correct aggregation depends on the measure’s meaning.

For values x₁, x₂, …, x_n, the arithmetic mean is:

where n is the number of observed values included and x̄ is the mean. The mean is appropriate when each observation is a comparable unit and the intended population is clear. It can be misleading for rates or ratios.

For example, if Branch A has 90 successful calls out of 100 and Branch B has 1 successful call out of 2, the unweighted average of branch rates is:

The overall success rate is instead:

The second calculation is a weighted result because it uses total successes divided by total attempts. Analysts must define the numerator and denominator before aggregating a rate.

Distinct count is different from count. Counting order lines measures activity records; counting distinct OrderID values measures orders. A duplicated join can inflate ordinary counts while leaving a distinct count apparently plausible, so both should be reviewed.

8. Validation and Reconciliation

Validation asks whether the prepared output satisfies technical and business expectations. It should occur throughout the workflow, not only at the end.

Validation techniques

• Schema checks: confirm field names, types, and expected columns.
• Domain checks: test permitted values and ranges.
• Key checks: identify null or duplicate keys.
• Row-count checks: compare source, intermediate, and output counts.
• Reconciliation: compare totals with a trusted report or source system.
• Referential checks: identify foreign keys with no matching dimension record.
• Sample review: inspect selected records from source to output.
• Exception reporting: preserve rejected or suspicious rows for investigation.

A reconciliation difference can be expressed as:

The difference should be zero when transformations are expected to preserve the measure, or it should be explained when filters, exclusions, currency conversion, or deduplication intentionally change it. A percentage variance may be calculated as:

This formula assumes that the source total is non-zero and that both totals have the same definition and unit.

Validation should also consider filters. A dashboard may show a lower total than the source because it excludes cancelled orders. That is not necessarily an error, but the exclusion must be documented and visible to users.

9. Reproducible Workflows in Tableau Prep and Power Query

A reproducible workflow is a sequence of documented transformations that can be rerun when new data arrives. It is preferable to manual editing because it improves consistency, auditability, and maintenance.

Tableau Prep

A typical Tableau Prep flow contains:

1. Input steps for files, databases, or published sources.
2. Profile and cleaning steps for inspecting distributions, nulls, and values.
3. Calculated fields for derived variables.
4. Join or union steps for combining sources.
5. Aggregate or pivot steps for changing grain or shape.
6. Output steps for publishing an extract or file.

The profile pane helps reveal unexpected values and distributions. A flow should use meaningful step names, such as Standardise region codes rather than generic labels. Cleaning operations should be ordered so that type conversion occurs before calculations that depend on the converted field.

Power Query

Power Query uses a sequence of applied steps. Common operations include:

• Change Type
• Remove Rows or Remove Duplicates
• Replace Values
• Split Column
• Merge Queries
• Append Queries
• Group By
• Pivot and Unpivot Columns
• Add Custom Column

Power Query records transformations in the M language, which supports repeatability. Users should review the step order because later steps may depend on column names or types created earlier. Source files should be stored in stable locations, and parameters can be used for folder paths, dates, or environment-specific settings.

Workflow documentation

A professional preparation log should record:

Version control, naming conventions, source snapshots, and test cases make it possible to determine what changed between refreshes. A workflow should fail visibly when a required column disappears rather than silently producing an incomplete dashboard.

10. Applied Case: Regional Retail Performance

The following is an illustrative case.

Background

A retail company wants a Power BI dashboard comparing monthly sales, margin, and customer performance across regions. It provides three files: order lines, a customer master, and a regional target file.

Problem

The first dashboard reports revenue 18% higher than the finance report. Investigation shows that the order-line table has one row per product in an order, while a shipping-cost table has multiple records per order. A direct join repeats order values. The files also contain inconsistent region names and dates stored in mixed formats.

Analysis

The analyst profiles the sources and records the grains:

• Order lines: one row per OrderID–LineNumber.
• Shipping records: one row per OrderID–ShipmentID.
• Customers: one row per CustomerID.
• Targets: one row per Region–Month.

The analyst checks key uniqueness and finds that ShipmentID is unique but OrderID is not unique in the shipping table. Therefore, joining shipping directly to order lines creates a many-to-many multiplication for orders with multiple shipments.

Approach and solution

First, the analyst standardises dates and region codes. Second, shipping is aggregated to one row per OrderID before it is joined to order-level calculations. Customer data is joined using CustomerID after trimming and type alignment. Targets are related by a validated Region–Month key. Cancelled orders are excluded using a documented status rule.

The analyst then creates validation tests. Order-line revenue is compared with the source finance total after applying the same cancellation rule. Duplicate checks confirm that the aggregated shipping table has one row per OrderID. A sample of orders is traced from the raw files through the prepared output.

Outcome

The revised revenue agrees with finance within the accepted rounding tolerance. The dashboard now distinguishes order-line units from distinct orders and displays a warning that targets are monthly while transactions are daily. Users can interpret regional comparisons without mistaking shipment rows for sales events.

Learning points

1. Grain must be established before joining.
2. A many-to-many join can inflate measures without producing an obvious error message.
3. Aggregating a table to the required key before joining can prevent fan-out.
4. Business filters must be applied consistently in reconciliation.
5. A trustworthy dashboard depends on documented assumptions, not merely attractive visuals.

11. Governance, Ethics, and Contemporary Practice

Preparation decisions can affect people and organisational outcomes. Removing records with missing income may disproportionately exclude certain groups. Standardising names can erase culturally meaningful distinctions. Combining datasets may create sensitive inferences not present in either source alone.

Analysts should follow applicable privacy and data-protection requirements, including purpose limitation, data minimisation, access control, retention rules, and secure handling of personal information. Personally identifiable information should be masked, pseudonymised, or excluded where it is not required. Access to prepared outputs should be appropriate to the sensitivity of the data.

AI-assisted data preparation can suggest type conversions, detect anomalies, generate formulas, or explain transformation code. Such suggestions require human review. An AI tool may infer a wrong date locale, silently drop records, expose confidential data, or create a plausible but incorrect mapping. Analysts remain responsible for validation, documentation, and approval.

Contemporary preparation environments increasingly support cloud dataflows, semantic layers, data catalogues, lineage tracking, automated quality rules, and incremental refresh. These capabilities improve scale but do not replace conceptual understanding. Automation can execute a flawed rule very efficiently.

Best-Practice Checklist

Before publishing prepared data, confirm that:

• The grain of every important table is documented.
• Keys and relationship cardinalities have been tested.
• Data types, units, dates, and time zones are correct.
• Missing values and exclusions have explicit rules.
• Joins and unions have been checked for row multiplication or loss.
• Aggregations match the meaning of each measure.
• Source and output totals have been reconciled.
• Exceptions are retained or reported rather than silently discarded.
• The workflow can be rerun from the source.
• Steps, assumptions, owners, and limitations are documented.
• Privacy, access, retention, and ethical risks have been considered.

Chapter Summary

Data literacy requires analysts to understand not only how to manipulate data but also what each value means and what each row represents. Data types, structures, keys, cardinality, and grain provide the foundation for safe analysis. Data quality should be assessed through completeness, validity, consistency, uniqueness, accuracy, and timeliness, with missingness interpreted rather than automatically replaced.

Cleaning includes standardising text, parsing dates, converting types, investigating outliers, and applying documented rules. Reshaping changes wide data to long data or derives fields for analytical use. Unions append compatible rows, while joins combine columns and may multiply records when keys are not unique. Relationships can preserve table-level grain in a semantic model, but their cardinality and filter behaviour still require testing.

Aggregation must reflect the meaning of a measure. Totals, counts, distinct counts, averages, and rates are not interchangeable. Validation combines schema checks, key tests, row-count comparisons, reconciliations, referential checks, and record sampling. Tableau Prep flows and Power Query applied steps support reproducible preparation when they are documented, versioned, and designed to fail visibly when assumptions are violated. Finally, data preparation has ethical and governance consequences, particularly when personal data or automated tools are involved.

Key Terms

Accuracy: The degree to which a value represents the real-world fact it is intended to describe.

Aggregation: The process of summarising detailed records using operations such as sum, count, or average.

Cardinality: The numerical relationship between records in linked tables, such as one-to-many.

Completeness: The extent to which required values are present.

Data dictionary: Documentation defining fields, meanings, types, units, and permitted values.

Data grain: The level of detail represented by one row in a dataset.

Data literacy: The ability to understand, evaluate, work with, and communicate data.

Dimension: A descriptive field or table used to classify and filter measures.

Fact table: A table containing events, transactions, or measurable observations.

Foreign key: A field referring to a key in another table.

Join: An operation that combines columns from tables using matching fields.

Key: A field or combination of fields used to identify or link records.

Missing value: A value that is unavailable, inapplicable, withheld, or not captured.

Primary key: A field or combination of fields that uniquely identifies a record.

Quality validation: Testing whether data meets defined technical and business expectations.

Relationship: A logical connection between tables in a semantic data model.

Reshaping: Changing the arrangement of rows and columns, such as pivoting or unpivoting.

Union: An operation that appends rows from compatible tables.

Validity: Conformity to an allowed format, range, domain, or rule.

Uniqueness: The absence of unintended duplicate records for the defined grain.

Review Questions

Short Answer Questions

1. What is meant by the grain of a dataset, and why should it be documented?
2. Distinguish a union from a join.
3. Why should a customer ID usually be stored as text rather than as a number?
4. Name four dimensions of data quality.
5. What is the purpose of reconciliation in a preparation workflow?

Long Answer Questions

1. Explain how a many-to-many join can inflate revenue and describe two methods for preventing the problem.
2. Compare wide and long data formats and discuss why long format is often useful for visualization.
3. Describe a reproducible data-preparation workflow using either Tableau Prep or Power Query.
4. Explain why replacing missing values with zero can produce misleading business analysis.
5. Discuss how data governance and privacy should influence the preparation of customer data.

Analytical / Application Questions

1. A sales table has one row per order line, while a promotions table has several rows per order. Design a preparation and validation approach that prevents duplicated revenue.
2. A dashboard shows a 12% difference from the finance total. Identify the checks you would perform and explain how you would determine whether the difference is an error or an intentional scope difference.
3. A service team reports a 70% average success rate across two branches: Branch A achieved 90 successes from 100 attempts, and Branch B achieved 1 success from 2 attempts. Calculate the overall success rate and explain why it differs from the simple average of the branch rates.

Multiple Choice Questions with Answers

Question 1

Which statement best defines data grain?

A. The file format used to store a dataset

B. The level of detail represented by one row

C. The number of columns in a table

D. The frequency at which a source refreshes

Correct Answer: B. The level of detail represented by one row

Question 2

A table contains one row per order, and another contains several rows per order because of shipments. What is the main risk of joining them directly?

A. All dates will become text

B. The order measures may be multiplied

C. The tables will automatically be sorted

D. All unmatched records will be deleted

Correct Answer: B. The order measures may be multiplied

Question 3

Which operation appends rows from compatible tables?

A. Union or append

B. Inner join

C. Relationship

D. Aggregation

Correct Answer: A. Union or append

Question 4

Which value is most appropriately treated as categorical text rather than a numeric measure?

A. Revenue

B. Units sold

C. Postal code

D. Discount amount

Correct Answer: C. Postal code

Question 5

A field has 950 non-missing values in a dataset of 1,000 records. What is its completeness rate?

A. 5%

B. 50%

C. 90%

D. 95%

Correct Answer: D. 95%

Question 6

Which validation method compares a prepared total with a trusted source total?

A. Reconciliation

B. Pivoting

C. Tokenisation

D. Sorting

Correct Answer: A. Reconciliation

Question 7

Why is an unweighted average of branch success rates sometimes misleading?

A. It always removes missing values

B. It gives branches with different attempt volumes equal influence

C. It cannot contain percentages

D. It converts dates into numbers

Correct Answer: B. It gives branches with different attempt volumes equal influence

Question 8

Which Power Query operation changes columns such as January, February, and March into attribute-value rows?

A. Merge Queries

B. Remove Duplicates

C. Unpivot Columns

D. Group By

Correct Answer: C. Unpivot Columns

Question 9

Which statement about a missing value is correct?

A. It always means zero

B. It always indicates fraud

C. It may mean unavailable, inapplicable, or uncaptured information

D. It should always be replaced by the mean

Correct Answer: C. It may mean unavailable, inapplicable, or uncaptured information

Question 10

What is a key benefit of a reproducible preparation workflow?

A. It guarantees that source data is accurate

B. It allows documented transformations to be rerun consistently

C. It eliminates the need for validation

D. It prevents all data from being updated

Correct Answer: B. It allows documented transformations to be rerun consistently