Data Acquisition, Profiling, and Preparation

Introduction

A visualization is only as trustworthy as the data behind it. In business analytics, the visible chart is the final stage of a chain that begins with acquiring data from operational systems, files, cloud services, application interfaces, and databases. Data acquisition identifies and retrieves relevant data; profiling examines its structure and quality; preparation transforms it into a consistent analytical form.

Power BI and Tableau both provide visual, largely low-code environments for these activities. Power BI uses Power Query extensively for connection, profiling, transformation, and loading. Tableau supports preparation through connectors, joins, unions, relationships, calculated fields, Tableau Prep, and data-source filters. Although the interfaces differ, the underlying principles are shared: understand the source, preserve meaning, document transformations, validate results, and avoid introducing bias or duplication.

Poor preparation can produce duplicated sales, missing customers, incorrect dates, or measures that appear precise but are conceptually invalid. Good preparation creates a reliable analytical model and reduces the risk that a dashboard will support an incorrect managerial decision.

Learning Objectives

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

1. Connect Power BI and Tableau to files, databases, cloud platforms, APIs, and business applications.
2. Distinguish extraction, transformation, loading, and semantic modeling activities.
3. Profile data for completeness, validity, uniqueness, consistency, and duplication.
4. Clean, reshape, join, union, pivot, and aggregate data appropriately.
5. Use Power Query and Tableau preparation capabilities to create analysis-ready data.
6. Validate transformations using reconciliation, row-count, key, and business-rule checks.
7. Explain governance, security, privacy, and reproducibility issues in data preparation.

1. The Data Preparation Lifecycle

A useful lifecycle is:

source identification → connection → ingestion → profiling → cleaning → restructuring → integration → validation → loading → documentation and monitoring

These stages are iterative rather than strictly linear. Profiling may reveal that a source lacks a required field, while validation may show that an earlier join created duplicate records. A professional analyst therefore treats preparation as a controlled process, not as a sequence of cosmetic edits.

The distinction between data preparation and data modeling is important. Preparation changes the representation or quality of source data—for example, converting text dates into date values. Modeling defines analytical entities and relationships—for example, relating a sales fact table to a product dimension. Some operations can occur in either layer, but placing repeatable, source-oriented transformations in the preparation layer usually improves consistency and reuse.

Common acquisition patterns

A file is not necessarily a simple source. A spreadsheet may contain title rows, merged cells, subtotals, multiple tables, and manually entered corrections. Likewise, an API response may contain nested objects, pagination tokens, and changing fields. The analyst must inspect the source rather than assume that its appearance represents its analytical structure.

2. Connecting Power BI and Tableau

Files

In Power BI Desktop, the Get Data experience supports common file types and opens Power Query for transformation. Tableau connects to text files, Microsoft Excel workbooks, statistical files, and other supported formats. When importing a CSV, inspect delimiter, text qualifier, character encoding, header position, and data types. An incorrect encoding can turn names into unreadable characters; an incorrect delimiter can place the entire record in one column.

A robust file process includes a controlled folder, a naming convention, an archive of received files, and a rule for identifying the current file. Folder-based ingestion is useful when monthly files share a schema. The process should combine files only after checking that column names and meanings remain compatible.

Databases

Database connections may use live querying or import/extract modes. Power BI commonly uses Import or DirectQuery; Tableau commonly uses live connections or extracts. Import and extract modes often provide fast interactive analysis because data is stored in an optimized analytical form. Live modes can preserve freshness but depend on database performance, network conditions, concurrency, and query design.

A key concept in Power Query is query folding: when transformations are translated into source-system operations, the source database performs part of the work. Filtering early and selecting only required columns can reduce data movement. Query folding is not guaranteed for every transformation, so analysts should inspect query plans or source queries where performance matters.

Database access should use read-only accounts where possible. Credentials should not be embedded in files or scripts, and production tables should not be altered merely to support a dashboard.

Cloud platforms and business applications

Cloud connectors can simplify authentication and refresh, but they do not eliminate governance. The analyst should identify the data owner, geographic location, refresh frequency, service limits, and permitted uses. Business-application connectors may expose friendly field names that conceal complex definitions. For example, “revenue” might mean invoiced revenue, recognized revenue, or order value. The technical connection is therefore only the beginning; business meaning must be confirmed.

APIs

An API commonly returns JSON or XML. A reliable API ingestion process must address authentication, pagination, request parameters, rate limits, retries, error responses, and schema changes. If an endpoint returns 100 records per request and supplies a next-page token, the extraction must continue until the token is absent. Otherwise, the resulting dashboard silently represents only the first page.

API data often contains nested arrays. A customer record may contain an array of orders, and expanding that array changes the grain from one row per customer to one row per order. This change must be documented because it affects joins, counts, and measures.

3. Data Profiling

Data profiling is the systematic examination of data content, structure, and quality before substantial transformation. It answers questions such as:

• What does one row represent?
• Which columns are identifiers, measures, attributes, or dates?
• Which fields contain nulls or unexpected values?
• Are keys unique?
• Are units and definitions consistent?
• Are there outliers, duplicate records, or invalid categories?

Power Query provides column quality, column distribution, and column profile views. Tableau provides metadata inspection, field summaries, data-grid examination, and profiling capabilities within Tableau Prep. These tools are aids to reasoning, not substitutes for business knowledge.

Grain and key structure

The grain is the level of detail represented by one row. A sales table may have one row per invoice line, while a budget table may have one row per department and month. Combining them without recognizing their grains can create many-to-many duplication.

A candidate key should uniquely identify rows. If a table has 10,000 rows but only 9,850 distinct transaction IDs, either duplicates exist or the supposed identifier is not a key. Composite keys may be required, such as customer ID plus transaction date, although dates alone are often insufficient because multiple transactions can occur on one day.

Quality dimensions

Common dimensions include:

• Completeness: required values are present.
• Validity: values conform to permitted formats or domains.
• Accuracy: values represent the real-world object correctly.
• Consistency: the same concept is represented in compatible ways.
• Uniqueness: records are not unintentionally duplicated.
• Timeliness: data is sufficiently current for its purpose.

For a field, a simple completeness rate is:

Here, a non-null required value is a value that is present and not merely an empty string; the denominator is the number of records being assessed. The measure assumes that each record has equal importance. If records have different business weights, a weighted completeness measure may be more appropriate.

For example, if 9,600 of 10,000 customer records contain a valid email address, completeness is (9,600 ÷ 10,000) × 100% = 96%. This does not prove that the email addresses are accurate or deliverable; it measures presence only.

A profiling report should preserve both counts and percentages. Percentages alone can hide material problems in large datasets, while counts alone make comparisons difficult.

4. Cleaning and Standardization

Cleaning should be guided by a documented rule and a reason for applying it. Common operations include:

1. Type correction. Convert text to numeric, date, Boolean, or categorical types. A date such as “03/04/2025” is ambiguous unless locale is specified.
2. Whitespace and character normalization. Trim leading and trailing spaces, standardize case when appropriate, and replace non-printing characters.
3. Missing-value treatment. Distinguish null, blank, zero, “not applicable,” and “unknown.” Replacing all missing values with zero changes meaning and may distort averages.
4. Domain validation. Check that quantity is not negative unless returns are represented that way, that country codes are permitted, and that percentages lie within their defined range.
5. Duplicate handling. Determine whether duplicates are errors, legitimate repeated events, or multiple versions of a record.
6. Outlier investigation. An unusually large sale may be an error, a bulk order, or a genuine event. Automatic deletion is unsafe without context.
7. Reference-data standardization. Map variants such as “UK,” “U.K.,” and “United Kingdom” to an approved value.

Power Query transformations include filtering, replacing values, splitting and merging columns, changing types, extracting text, grouping, and adding custom columns. Tableau supports similar operations through data-cleaning steps, calculated fields, aliases, grouping, splitting, and type assignment. In both tools, preserve the original source where feasible and create a transparent sequence of applied steps.

A useful principle is minimum necessary alteration. Do not round financial amounts early, discard rows merely because they are unusual, or overwrite source values without retaining a traceable transformation.

5. Reshaping Data

Analytical tools generally work best when data has a clear rectangular structure: fields in columns and observations in rows. Reshaping converts a source layout into that structure.

Wide and long forms

In wide form, each period may be a separate column:

In long form, periods become values in a Month column:

Long form is generally more flexible for time-series analysis, filtering, and visualization. In Power Query, unpivot converts selected columns into attribute-value pairs. Tableau Prep offers pivot operations that can convert columns to rows or rows to columns, depending on the source layout.

Pivoting and aggregation

Pivoting can produce a cross-tab for reporting, but it should not be used merely to make data look familiar. A pivot requires an aggregation rule when multiple records map to the same row-column intersection. Summing transactions may be valid for sales; averaging may be appropriate for temperature; selecting an arbitrary value is usually not valid.

Splitting and combining fields

A full name can be split into given name and family name, while year and month fields can be combined into a date. Such operations must account for exceptions: names do not always contain one space, and address formats differ across countries. Text parsing rules should therefore be tested on representative edge cases.

6. Combining Data: Union, Join, and Relationship

Union or append

A union stacks rows from tables with compatible columns. January and February sales files can be appended to create one sales table. Columns must have compatible meanings, not merely similar names. “Revenue” in one file and “gross revenue” in another may require business clarification before unioning.

Missing columns introduced by a union typically receive nulls for sources that do not contain them. The resulting schema should be reviewed, and a source-period field should often be added for traceability.

Join

A join combines columns from tables based on matching keys. Common types are inner, left, right, and full outer joins. A left join retains every row from the left table and adds matching values from the right table; unmatched right-side fields become null.

The most dangerous join error is unexpected row multiplication. Suppose a sales table has one row per order and a customer table has one row per customer. Joining on a unique customer ID should preserve the number of sales rows. If the customer table contains two rows for one customer, each related sale may appear twice. A one-to-many join can be correct when the intended grain is preserved, but an unrecognized many-to-many join can inflate revenue.

Relationships and semantic models

Power BI relationships and Tableau relationships can defer some combination logic until analysis. This is useful when tables have distinct grains and should not be physically flattened. However, relationships still require correct keys, cardinality, filter direction, and date logic. A relationship is not a cure for duplicate identifiers or ambiguous business definitions.

Before joining or relating tables, document:

• grain of each table;
• candidate keys and their uniqueness;
• expected cardinality;
• treatment of unmatched records;
• whether filtering should propagate in one or both directions.

7. Validation and Reconciliation

Validation tests whether the prepared output is structurally and semantically fit for use. It should occur after significant steps and before publication.

Structural checks

Check row counts, column counts, data types, null counts, distinct-key counts, and duplicate counts. A sudden row-count increase after a join is a warning requiring explanation, not necessarily an error.

Reconciliation checks

Compare totals between source and output where the transformation should preserve them. If a sales union is intended to preserve transaction amounts, compare source total sales with prepared total sales, allowing for explicitly excluded invalid records. For a filter, reconcile the excluded population and document the reason.

A relative difference can be expressed as:

The formula is meaningful when the source total is nonzero and both totals use the same units and scope. A tolerance must be defined in advance; a zero difference is not always required because rounding, currency conversion, or approved exclusions may create small differences.

Business-rule checks

Examples include:

• order date is not later than shipment date unless back-order logic allows it;
• quantity is positive for sales and negative for returns according to the stated convention;
• every product code maps to an approved product dimension, or unmatched codes are reported;
• revenue equals quantity × unit price only when discounts, taxes, and returns are treated consistently.

Validation should produce an exception table rather than silently removing failures. Exceptions can be assigned to data owners for correction.

8. Detailed Applied Case: Retail Sales Integration

This is an illustrative case. A regional retailer wants a Power BI and Tableau-ready dataset for weekly sales analysis. The organization receives monthly CSV files from stores, customer data from a CRM system, product data from an ERP database, and online orders through a REST API.

Background and problem

The management dashboard reports revenue by region, channel, and product category. Users have noticed that dashboard revenue is higher than the finance report. Initial investigation finds that store files use “UK” and “United Kingdom” inconsistently, the CRM contains duplicate customer records, and the API returns orders in pages of 100 records.

Analysis

The intended grain is one row per order line. Store files contain one row per order line, but the API contains one row per order with a nested array of lines. The API must therefore be expanded to order-line grain. Product codes are the proposed key for product enrichment. Customer ID is not unique in the CRM, so it cannot safely be used in a many-to-one join until duplicates are resolved.

Approach and solution

1. Acquire. The analyst connects Power Query to the controlled store folder, the ERP database, the CRM export, and the API. Tableau users can use equivalent file, database, application, and web connectors or consume the certified prepared output.
2. Profile. Column profiles identify 2.4% missing customer IDs, inconsistent country labels, and 312 duplicate CRM IDs. API pagination is tested by comparing returned page counts with the endpoint’s total-record metadata.
3. Standardize. Country labels are mapped through a reference table. Dates are parsed using the documented locale. Currency is retained as a separate field, and conversion is performed using an approved rate table rather than an unrecorded manual adjustment.
4. Reshape. Nested API order lines are expanded. Store monthly columns are unpivoted where the source uses month columns. The output grain is recorded in the query documentation.
5. Resolve dimensions. The CRM owner supplies a survivorship rule: retain the most recently verified customer record, while preserving a duplicate-resolution log. Product codes are checked against the ERP dimension.
6. Combine. Store and online order lines are unioned after aligning column names and adding a channel field. Product attributes are joined using a unique product code. Customer attributes are related only after duplicate resolution.
7. Validate. Row counts are checked after each stage. Revenue is reconciled by source and month. Unmatched product codes and missing customer IDs are placed in exception tables rather than deleted.
8. Publish. The certified dataset is loaded into Power BI and Tableau. Refresh credentials, owner, refresh schedule, source definitions, and known limitations are documented.

Outcome

The revenue discrepancy is traced to two causes: API pagination had omitted later pages in one earlier process, while a duplicate CRM join had multiplied some online order lines. After correction, order-line counts and revenue reconcile within the agreed currency-rounding tolerance. Management receives separate metrics for gross sales, returns, and net sales, avoiding the previous ambiguity around “revenue.”

Learning points

The case demonstrates that technical connectivity does not guarantee analytical validity. Grain must be established before joins, API completeness must be tested, reference data must be governed, and reconciliation must be designed as part of the workflow rather than added after a dashboard appears wrong.

9. Governance, Ethics, and Security

Data preparation can expose personal, confidential, or regulated information. Customer names, email addresses, locations, and transaction histories may be personal data under applicable privacy law. Analysts should apply purpose limitation, data minimization, access control, retention rules, and appropriate de-identification. A dashboard rarely requires a full email address merely because the source contains one.

Row-level security may restrict users to their regions or departments, but it must be tested with representative accounts. Hiding a field in a visualization is not equivalent to protecting it in the underlying dataset. Credentials, API keys, and gateway secrets should be managed through approved secure services.

Ethical preparation also concerns exclusion and representation. Removing records with missing demographic information may change the population represented in an analysis. Imputing values can create apparent precision. Transformation logs should state what was changed, why it was changed, and who approved the rule.

10. Contemporary Practice and AI Assistance

Modern platforms increasingly offer automated type detection, anomaly detection, natural-language assistance, and AI-generated transformation suggestions. These features can accelerate exploration, but they do not establish data accuracy. An automated suggestion may infer the wrong date locale, treat an identifier as a number, or remove a legitimate outlier.

Use AI assistance under human review: inspect generated steps, test against known records, protect confidential data, and retain reproducible transformation logic. Certified semantic models, reusable dataflows, Tableau Prep flows, source control, and automated data-quality tests are increasingly important for enterprise analytics. The goal is not merely faster preparation but preparation that can be audited, refreshed, and trusted.

Best-Practice Checklist

• Define the analytical question and intended grain before connecting sources.
• Use the least-privileged credentials and approved connectors.
• Profile before cleaning; do not assume that a neat table is a reliable table.
• Preserve source data and record transformation steps.
• Standardize through reference tables where possible.
• Test joins for cardinality and row multiplication.
• Handle API pagination, rate limits, and schema changes explicitly.
• Validate totals, row counts, keys, nulls, and business rules.
• Separate data errors from legitimate unusual observations.
• Document ownership, refresh, definitions, exclusions, and limitations.
• Protect personal and confidential information throughout the workflow.
• Re-test after source-schema changes and platform refreshes.

Chapter Summary

Data acquisition connects analytical tools to files, databases, cloud services, APIs, and business applications. Power BI and Tableau provide substantial preparation capabilities, but the analyst remains responsible for understanding source meaning and controlling transformation risk. Profiling establishes grain, keys, data types, missingness, duplication, and quality dimensions. Cleaning standardizes values while preserving semantic distinctions among null, blank, zero, and unknown. Reshaping converts wide and nested structures into analysis-ready forms. Unions stack compatible rows, whereas joins combine columns and can create unintended duplication when cardinality is misunderstood. Relationships can preserve separate grains but still require valid keys and clear filter behavior.

Reliable preparation concludes with validation: structural checks, reconciliation, exception reporting, and business-rule tests. Governance requires secure access, privacy protection, documentation, reproducibility, and human oversight of automated or AI-assisted suggestions. A trustworthy dashboard is therefore the product of a controlled data pipeline, not simply an attractive visual interface.

Key Terms

Data acquisition: The process of locating, connecting to, and retrieving data from one or more sources.

Data profiling: Systematic examination of data structure, content, and quality.

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

Candidate key: A field or field combination that can uniquely identify records.

Completeness: The extent to which required values are present.

Validity: Conformance to an accepted format, range, or domain.

Data cleansing: Correcting, standardizing, or documenting problematic data values.

Query folding: Translation of eligible transformation steps into operations performed by the source system.

Import mode: Loading data into an analytical engine for querying.

Live connection: Querying a source system or extract during analysis rather than fully importing data.

API: An application programming interface that enables structured interaction between software systems.

Pagination: Dividing a large API response into multiple pages or requests.

Union: Combining datasets by stacking compatible rows.

Join: Combining columns from datasets using matching fields.

Relationship: A modeled association between tables based on related fields.

Pivot: Restructuring data by converting categories between rows and columns.

Unpivot: Converting multiple columns into attribute-value rows.

Reconciliation: Comparing source and prepared results to identify unexplained differences.

Schema drift: An unexpected change in fields, types, or structure of a source.

Data lineage: A record of where data originated and how it was transformed.

Review Questions

Short Answer Questions

1. What is meant by the grain of a dataset, and why must it be identified before joining tables?
2. Distinguish a union from a join.
3. What does query folding mean in Power Query?
4. Why is replacing every missing value with zero potentially misleading?
5. Name four dimensions of data quality.

Long Answer Questions

1. Explain a complete data preparation lifecycle from source identification through publication and monitoring.
2. Compare file, database, cloud, API, and business-application sources, including major risks associated with each.
3. Explain how wide-to-long reshaping supports flexible business analysis.
4. Discuss how an unintended many-to-many join can distort dashboard measures and how an analyst can detect it.
5. Explain the governance and privacy responsibilities involved in preparing customer data for Power BI or Tableau.

Analytical / Application Questions

1. A sales table has 50,000 rows before a customer join and 57,500 rows afterward. Describe the diagnostic tests you would perform before accepting the result.
2. An API reports 12,450 available orders, but the prepared table contains 10,000 rows. Develop a validation plan that considers pagination, nested order lines, filters, and duplicate handling.
3. A monthly workbook contains sales as separate columns and uses both “UK” and “United Kingdom.” Design a Power Query or Tableau preparation workflow that reshapes, standardizes, and validates the data.

Multiple Choice Questions with Answers

Question 1

Which operation stacks rows from datasets with compatible columns?

A. Union

B. Inner join

C. Pivot

D. Relationship

Correct Answer: A. Union

Question 2

What is the primary purpose of data profiling?

A. To decorate a dashboard

B. To examine structure and quality before transformation

C. To encrypt every field

D. To replace the semantic model

Correct Answer: B. To examine structure and quality before transformation

Question 3

A table has 9,600 non-null required values in 10,000 records. What is its completeness rate?

A. 4%

B. 40%

C. 96%

D. 104%

Correct Answer: C. 96%

Question 4

What is a major risk of joining a sales table to a customer table containing duplicate customer IDs?

A. All dates become text

B. Sales rows may be multiplied

C. The source database is automatically deleted

D. The table must become wide form

Correct Answer: B. Sales rows may be multiplied

Question 5

Which operation converts month columns into attribute-value rows?

A. Unpivot

B. Union

C. Filter

D. Extract

Correct Answer: A. Unpivot

Question 6

What does query folding allow Power Query to do?

A. Translate eligible transformations for execution by the source system

B. Remove the need for data validation

C. Convert every table into a chart

D. Guarantee that all joins are one-to-one

Correct Answer: A. Translate eligible transformations for execution by the source system

Question 7

Which API issue can cause an extraction to contain only the first portion of available records?

A. Pagination not being handled

B. Using a read-only account

C. Applying a documented filter

D. Adding a source column

Correct Answer: A. Pagination not being handled

Question 8

Which validation is most appropriate after a transformation intended to preserve sales totals?

A. Compare source and output totals using the same scope and units

B. Change all nulls to zero

C. Sort the columns alphabetically

D. Hide the source table

Correct Answer: A. Compare source and output totals using the same scope and units

Question 9

Why should null, blank, zero, and “not applicable” be distinguished?

A. They always have identical meanings

B. They represent different information states

C. Only zero can be stored in a database

D. BI tools cannot display blanks

Correct Answer: B. They represent different information states

Question 10

Which practice best supports responsible AI-assisted data preparation?

A. Accept all automated suggestions without review

B. Upload confidential data to any public assistant

C. Inspect, test, document, and approve generated transformations

D. Delete the source after automation

Correct Answer: C. Inspect, test, document, and approve generated transformations