Data Acquisition, Profiling and Preparation

Introduction

Business intelligence is only as reliable as the data on which it is based. A visually attractive dashboard can still produce misleading decisions when its source data contains duplicated transactions, inconsistent product codes, missing dates, incorrect data types, or unclear business definitions. Data acquisition and preparation therefore form the foundation of effective visualization and business intelligence (BI).

This chapter examines how analysts connect to spreadsheets, databases, cloud services, and web sources; profile data quality; clean and transform data; integrate multiple sources; validate results; and document lineage. The practical focus is on Power Query, used in Power BI and Excel, and Tableau preparation capabilities such as Tableau Prep and the Data Source page.

Preparation should not be understood as a single preliminary task. It is a controlled, repeatable process that converts raw data into trustworthy analytical data while preserving evidence of what was changed and why.

Learning Objectives

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

• distinguish common data sources and connection methods;
• assess data quality using systematic profiling techniques;
• clean and transform data in Power Query and Tableau;
• combine tables through appending, joining, and relationships;
• validate data through reconciliation, constraint checks, and sampling;
• document assumptions, transformations, dependencies, and lineage;
• design reproducible preparation workflows; and
• identify governance, privacy, security, and ethical risks in data preparation.

1. The Data Preparation Pipeline

A typical preparation pipeline contains the following stages:

1. Source identification: Determine where data originates, who owns it, and how frequently it changes.
2. Connection: Establish a secure connection to the source.
3. Profiling: Examine structure, distributions, missingness, duplicates, and anomalies.
4. Cleaning: Correct or remove errors while preserving an audit trail.
5. Transformation: Reshape data into an analytical structure.
6. Integration: Combine compatible data sources.
7. Validation: Test whether the prepared data is complete, accurate, and fit for purpose.
8. Publication: Load the data into a model, workbook, or governed semantic layer.
9. Monitoring: Recheck the process when new data arrives or source structures change.

A useful distinction is between source data, staging data, and curated data. Source data is the original extract. Staging data is an intermediate form used for controlled transformations. Curated data is the prepared output intended for analysis. Analysts should avoid overwriting the original source unless the system specifically preserves version history.

Figure: A controlled data preparation pipeline with feedback for monitoring.

2. Acquiring Data from Different Sources

Spreadsheets and flat files

Excel workbooks, CSV files, text files, and exported reports are common in organizations. They are convenient but often have weak structural controls. A workbook may contain merged cells, multiple header rows, subtotals, notes, hidden columns, or formulas with inconsistent results.

Before connecting, identify the intended table and confirm that each row represents one business event or entity. In a transaction table, for example, one row might represent one invoice line rather than one invoice. This distinction determines how measures should later be aggregated.

Power Query can connect to Excel workbooks and folders of files. A folder connector is useful when monthly files have the same structure. The analyst can combine files through a sample-file transformation, but should verify that filenames, column names, and data types are consistent. Tableau can connect to Excel and text files directly and can union files from a folder when their schemas are compatible.

Relational databases

Databases usually provide stronger structure than spreadsheets. Important connection information includes server, database, schema, table or view, authentication method, and permissions. A table normally stores one subject, while relationships connect subjects through keys.

Use database views when business logic is centrally governed or when the database can perform filtering and aggregation efficiently. Use Power Query or Tableau preparation when the transformation is specific to an analysis, requires visual inspection, or cannot be changed centrally. Analysts should not extract sensitive columns merely because they are available.

Cloud services

Cloud sources include cloud data warehouses, enterprise applications, online storage, and SaaS platforms. They may require OAuth authentication, gateway configuration, refresh schedules, or API limits. A successful connection is not sufficient evidence of correctness: the analyst must determine whether the connector retrieves a live table, cached extract, partial result, or paginated response.

Web pages and APIs

Web pages are often designed for human reading rather than reliable analysis. Tables may include repeated headings, footnotes, navigation text, or dynamically loaded values. APIs are generally preferable because they expose structured responses, often in JSON or XML. Analysts must understand endpoint parameters, pagination, rate limits, authentication, response dates, and terms of use.

When using web data, record the URL or endpoint, access date, parameters, and licensing conditions. A web result can change without notice, so reproducibility requires storing an extract or documenting a versioned source where permitted.

3. Data Profiling and Quality Assessment

Data profiling is the systematic examination of data structure, content, and relationships before substantial transformation. Profiling should be performed at three levels:

• Structural profiling: column names, data types, row counts, table relationships, and schema changes.
• Content profiling: distinct values, frequency distributions, minimum and maximum values, missing values, and outliers.
• Relationship profiling: uniqueness of keys, referential integrity, unmatched records, and duplication across sources.

Dimensions of data quality

Common quality dimensions include:

A simple completeness rate for a required field is:

Here, N is the total number of records and M is the number of records missing the required value. The formula assumes that every record should contain the field and that missingness is defined consistently. It measures presence, not accuracy or validity.

For example, if 48 of 500 customer records lack an email address, completeness is (500 − 48) ÷ 500 × 100% = 90.4%. The organization must still decide whether 90.4% is acceptable for the intended use.

Profiling in Power Query

Power Query provides column quality, column distribution, and column profile views. Useful operations include:

• checking errors and empty values;
• identifying inferred data types;
• viewing distinct and unique values;
• sorting to inspect extremes;
• grouping by a suspected key to find duplicates; and
• examining query dependencies.

Profiling may be based on a preview rather than the complete dataset, depending on settings. Therefore, important quality conclusions should be tested against the full dataset or an explicit sample design.

Profiling in Tableau

Tableau's Data Source page supports inspection of fields, data types, joins, unions, filters, and metadata. Tableau Prep provides a visual flow in which profile panes show distributions and null values at each step. Changes in a flow should be checked after every major operation because a filter or join can alter row counts unexpectedly.

4. Cleaning and Transformation

Cleaning is not synonymous with deleting inconvenient records. Every modification should have a reason, rule, and preferably a measurable effect.

Standardizing values

Typical operations include trimming whitespace, changing case, replacing known spelling variants, splitting columns, and standardizing dates. For example, “United Kingdom,” “UK,” and “U.K.” may need to map to one controlled value. Such mapping should be recorded in a reference table rather than embedded invisibly in many reports.

Dates require particular care. A value such as 03/04/2025 can mean 3 April or March 4 depending on locale. Specify the expected locale before conversion. Do not convert a text field to a date merely because most values look valid; first investigate invalid values and ambiguous formats.

Data types and nulls

Data types communicate meaning and control operations. A product identifier such as 00125 should normally be text, not a number, because leading zeros are meaningful. Revenue should be numeric with a defined currency, while a date should be a date rather than a formatted text string.

A null may mean unknown, not applicable, not yet recorded, or intentionally suppressed. These meanings are not interchangeable. Replacing all nulls with zero can create false totals. A missing discount is not necessarily a zero discount, and a missing customer age is not necessarily an age of zero.

Outliers and errors

An outlier is an unusually large or small observation; it is not automatically an error. A very large order may be legitimate. Investigate source documentation, business context, and related fields before changing it. Possible treatments include correction from an authoritative source, flagging for review, exclusion under a documented rule, or retention with an outlier indicator.

Reshaping data

A wide table stores repeated measures in columns, such as Sales_Jan, Sales_Feb, and Sales_Mar. A long table stores the measure in rows, with fields such as Month and Sales. Long form is often more flexible for time analysis and visualization.

In Power Query, unpivoting converts repeated measure columns into attribute-value rows. In Tableau Prep, pivot operations perform analogous reshaping. The analyst must verify that the transformation does not change the intended grain. If three monthly columns become three rows per product, the row increase is expected; if it becomes six rows, an unintended duplication may have occurred.

5. Integration: Append, Join, and Relationship

Appending

Appending stacks rows from tables with similar meanings. January sales and February sales can be appended when their columns represent the same concepts. Missing columns may produce nulls, so schema compatibility must be checked.

Joining

A join combines columns from tables using one or more matching fields. Common join types are inner, left, right, and full outer joins. A left join retains every row in the left table and adds matching values from the right table. It is often useful when enriching a fact table with a dimension table.

The major danger is join multiplication. If the join key is not unique on the right side, one left row may match several right rows, increasing row count and potentially inflating measures. Before joining, profile key uniqueness and determine the intended cardinality: one-to-one, one-to-many, or many-to-many.

Relationships and grain

A relationship links tables conceptually without necessarily physically merging them. In a BI model, a sales fact table may relate to a product dimension through ProductID. A well-designed model generally has a clear grain, explicit keys, and controlled filter direction.

A useful diagnostic question is: “What does one row represent?” If this cannot be answered for every table, integration is not yet safe. Grain ambiguity is a frequent cause of incorrect totals.

6. Practical Workflows in Power Query and Tableau

Power Query workflow

A professional Power Query workflow commonly follows these steps:

1. Connect to the source and retain the original query as a staging query.
2. Rename the query descriptively.
3. Remove irrelevant columns and rows early, where appropriate.
4. Promote headers and correct data types explicitly.
5. Standardize text, dates, and codes.
6. Replace or flag errors using documented rules.
7. Merge or append queries only after profiling keys and schemas.
8. Add validation queries, such as duplicate-key and unmatched-key checks.
9. Review the Applied Steps pane and query dependencies.
10. Load only the curated outputs required by the model.

Power Query records transformations as M expressions. This supports reproducibility because the same steps can run again when the source is refreshed. However, reproducibility depends on stable source paths, credentials, parameters, and assumptions.

Tableau preparation workflow

In Tableau, use the Data Source page for simple connections, metadata changes, joins, unions, and filters. Use Tableau Prep for more complex, multi-step flows. A typical flow contains input, cleaning, join or union, aggregate, and output steps.

Name steps according to purpose rather than leaving generic labels. Inspect row counts and field profiles after joins and filters. Save flows with version information and publish outputs to a governed location when available.

Power Query and Tableau both support visual preparation, but neither removes the need for data modeling judgment. A series of technically valid steps can still encode a false business definition.

7. Validation and Reconciliation

Validation asks whether the prepared output satisfies explicit expectations. It should occur before publication and after significant source or logic changes.

Useful validation categories include:

• Row-count checks: compare source, intermediate, and output counts.
• Control-total checks: compare totals such as sales, quantity, or invoice value before and after transformation.
• Key checks: test duplicate primary keys and unmatched foreign keys.
• Range checks: test dates, quantities, prices, and percentages against plausible limits.
• Business-rule checks: for example, shipped date should not precede order date.
• Sample checks: trace selected records from source to final output.
• Refresh checks: confirm that the latest expected period is present.

A reconciliation difference can be expressed as:

A zero difference is reassuring but not conclusive. Equal totals can occur even when individual records are wrong because errors may offset one another. Reconciliation should therefore be combined with record-level sampling and structural tests.

When a transformation intentionally changes totals—for example, filtering cancelled orders—the expected difference should be documented in advance.

8. Lineage, Documentation, and Reproducibility

Data lineage describes the origin, movement, transformation, and destination of data. At minimum, documentation should identify:

• source system, table, file, URL, or API endpoint;
• owner and access method;
• extraction date and refresh frequency;
• table grain and key fields;
• transformations and their business purpose;
• filters, exclusions, mappings, and assumptions;
• validation tests and results;
• output location and downstream reports; and
• responsible person and change history.

A data dictionary should define field names, descriptions, types, units, permitted values, and sensitivity classification. A transformation log can record the step, rationale, affected fields, and validation evidence.

Reproducibility means that another authorized analyst can obtain the same or appropriately refreshed result by following the documented process. It is improved by parameterizing file paths and dates, using stable queries, preserving source extracts when permitted, applying version control to scripts and flows, and avoiding manual edits in the middle of a pipeline.

9. Case Study: Retail Sales Integration

The following is an illustrative case.

Background

A regional retailer receives daily sales files from stores, a product master from a database, and monthly exchange rates from a cloud spreadsheet. Management wants a dashboard showing sales by product category, store, and reporting currency.

Problem

The first dashboard showed unusually high sales for several categories. Investigation found that store files used different column names, product codes contained leading zeros, and the product master contained two records for one product code. Some files also included subtotal rows.

Analysis

The analyst defined the intended grain as one row per transaction line. A profiling report found:

• subtotal rows identifiable by a blank transaction ID;
• inconsistent date formats across stores;
• duplicate product codes in the master;
• unmatched product codes in sales; and
• exchange rates missing for one reporting month.

The duplicate product records meant that a simple merge would multiply affected sales rows.

Approach and solution

The analyst created a folder-based Power Query connection for store files and removed subtotal rows using a documented rule. Column names were standardized, dates were parsed using the appropriate locale, and product codes were explicitly typed as text. A validation query identified duplicate product codes in the master; the data owner confirmed which record was current and retired the other.

Unmatched sales product codes were retained in an exception table rather than silently discarded. Exchange rates were joined by currency and month, and the missing month was escalated to the finance owner. Control totals were calculated before and after each major step. Tableau was then connected to the curated output for visual exploration.

Outcome

The revised process eliminated join multiplication, made exceptions visible, and produced consistent category totals. The dashboard included a data-quality indicator showing unmatched products and missing exchange rates. Management could therefore distinguish operational sales performance from preparation exceptions.

Learning points

1. Establish grain before integration.
2. Profile keys before joining.
3. Treat exceptions as information, not as nuisances to hide.
4. Use domain owners to resolve ambiguous business meanings.
5. Validate both structure and totals.
6. Document decisions so that the next refresh is repeatable.

10. Governance, Ethics, and Contemporary Practice

Data preparation can create ethical and regulatory risks. Personal data should be collected and processed for a legitimate purpose, limited to what is necessary, protected from unauthorized access, and retained according to policy and applicable law. Analysts should avoid exposing names, addresses, health information, or identifiers in extracts when aggregated data is sufficient.

Access should follow least privilege. Credentials must not be stored in query text, shared files, or public repositories. Cloud connections and gateways should use approved authentication and monitoring practices.

Cleaning can introduce bias. Removing records with missing values may disproportionately exclude a demographic group. Standardizing categories can erase meaningful local differences. Automated anomaly detection may flag legitimate activity from small or underrepresented groups. Decisions about exclusions, imputation, and classification should be documented and reviewed.

AI-assisted preparation tools can suggest data types, detect anomalies, generate transformations, or describe columns. These suggestions are not authoritative. Analysts must verify generated steps, protect confidential data, test for bias, and retain human accountability. Contemporary professional practice increasingly combines visual tools with data contracts, automated quality tests, metadata catalogs, lineage platforms, and version-controlled deployment.

Best-Practice Checklist

Before publishing a prepared dataset, confirm that:

• the business purpose and grain are explicit;
• the source and refresh method are documented;
• data types, dates, currencies, and units are correct;
• required fields, keys, and relationships have been tested;
• joins have been checked for multiplication;
• exclusions and replacements have documented reasons;
• source and output totals have been reconciled where appropriate;
• privacy, security, and access controls are satisfied;
• exceptions are visible to responsible owners; and
• the workflow can be rerun without undocumented manual steps.

Chapter Summary

Data acquisition establishes the connection between analytical questions and organizational or external data sources. Spreadsheets are accessible but structurally fragile; databases provide stronger schema controls; cloud services require attention to authentication and refresh; and web sources require careful documentation of endpoints, dates, and licensing.

Profiling examines structure, content, and relationships. Data quality should be considered through completeness, validity, accuracy, consistency, uniqueness, and timeliness rather than through a single score. Cleaning includes standardization, type conversion, null treatment, error investigation, and reshaping. Integration requires a clear grain and careful distinction between appending, joining, and relating tables. Non-unique join keys can multiply rows and inflate measures.

Power Query and Tableau provide practical environments for repeatable preparation, but the tools cannot determine business meaning on behalf of the analyst. Validation should combine row counts, control totals, key tests, business rules, and record-level sampling. Lineage and documentation make transformations auditable and reproducible. Finally, data preparation must address privacy, security, fairness, access control, and responsible use of automation.

Key Terms

Data acquisition: Obtaining data from files, systems, services, or web sources for analysis.

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

Data quality: The extent to which data is fit for its intended purpose.

Completeness: The proportion of required values that are present.

Validity: Conformance to specified formats, ranges, or business rules.

Uniqueness: The absence of unintended duplicate records or keys.

Data grain: The business meaning represented by one row of a table.

Staging data: Intermediate data retained during preparation.

Curated data: Prepared data suitable for controlled analytical use.

Append: Combining tables by stacking rows.

Join: Combining columns from tables using matching fields.

Relationship: A logical connection between tables based on keys.

Join multiplication: Unintended row increase caused by non-unique join matches.

Unpivot: Converting repeated columns into attribute-value rows.

Null: A missing, unknown, or not-applicable value.

Data lineage: Information about data origin, movement, transformations, and destinations.

Reconciliation: Comparison of source and output results to identify differences.

Power Query: Microsoft's query and transformation technology used in Power BI and Excel.

Tableau Prep: Tableau's visual tool for cleaning, reshaping, and combining data.

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

Review Questions

Short Answer Questions

1. What is the difference between profiling and cleaning data?
2. Why should an identifier such as 00125 usually be stored as text?
3. What is the purpose of an exception table?
4. State two risks of joining tables on a non-unique key.
5. Name three items that should be recorded in a data-lineage document.

Long Answer Questions

1. Explain the stages of a repeatable data preparation pipeline and the purpose of each stage.
2. Compare spreadsheets, relational databases, cloud services, and web APIs as data sources.
3. Discuss how nulls, outliers, and inconsistent categories should be handled responsibly.
4. Explain the differences between appending, joining, and creating relationships between tables.
5. Describe how Power Query and Tableau Prep support reproducible preparation workflows.

Analytical / Application Questions

1. A sales table contains 100,000 rows before a product merge and 104,500 rows afterward. ProductID is expected to be unique in the product table. Diagnose possible causes and propose validation steps.
2. A customer dataset has 2,000 records, of which 120 lack a required postal code. Calculate the completeness rate and explain why this result alone cannot establish data fitness.
3. Design a preparation and governance plan for a dashboard that combines monthly spreadsheets, a cloud CRM export, and a public web API. Specify profiling tests, integration risks, validation checks, and lineage records.

Multiple Choice Questions with Answers

Question 1

Which activity is the primary purpose of data profiling?

A. Designing dashboard colours

B. Examining structure, content, and relationships before transformation

C. Publishing a report to users

D. Encrypting every source file

Correct Answer: B. Examining structure, content, and relationships before transformation

Question 2

What is the main risk when a left-table row matches several rows in the right table during a join?

A. The left table is automatically deleted

B. Dates are converted to text

C. Rows and measures may be unintentionally multiplied

D. All unmatched records are removed

Correct Answer: C. Rows and measures may be unintentionally multiplied

Question 3

Which operation stacks rows from tables with compatible meanings?

A. Append

B. Pivot

C. Aggregate

D. Transpose

Correct Answer: A. Append

Question 4

A table has 500 records and 48 missing values in a required field. What is its completeness rate?

A. 9.6%

B. 48.0%

C. 90.4%

D. 95.2%

Correct Answer: C. 90.4%

Question 5

Why should a product code such as 00125 generally be assigned a text data type?

A. Text values always use less storage

B. Leading zeros may be meaningful

C. Numeric fields cannot be joined

D. Text prevents all duplicates

Correct Answer: B. Leading zeros may be meaningful

Question 6

Which validation is most directly designed to identify duplicate primary keys?

A. Grouping by the key and counting records

B. Changing the report theme

C. Sorting columns alphabetically

D. Removing all null values

Correct Answer: A. Grouping by the key and counting records

Question 7

What does data lineage describe?

A. Only the visual layout of a dashboard

B. The origin, movement, transformation, and destination of data

C. The speed of a computer processor

D. The number of colours in a chart

Correct Answer: B. The origin, movement, transformation, and destination of data

Question 8

Why is replacing every null with zero potentially dangerous?

A. Zero is never a valid number

B. A null may mean unknown or not applicable rather than zero

C. Power BI cannot display zeros

D. Tableau automatically deletes zeros

Correct Answer: B. A null may mean unknown or not applicable rather than zero

Question 9

Which source is generally preferable when structured web data is available?

A. A screenshot of a web page

B. A manually copied paragraph

C. A documented API response

D. A browser bookmark

Correct Answer: C. A documented API response

Question 10

Which practice best supports reproducibility?

A. Making undocumented manual edits before every refresh

B. Overwriting the original source file

C. Using documented, parameterized, repeatable transformation steps

D. Removing all validation queries

Correct Answer: C. Using documented, parameterized, repeatable transformation steps