Data Transformation Automated Data Cleansing Pipeline in Power Query

Project Overview

This project aims to create an automated data cleansing pipeline using Power Query in either Excel or Power BI. The focus will be on transforming a raw dataset into a structured format suitable for analysis, emphasizing the application of various data cleansing techniques.

Dataset

• File: Automated_Data_Cleansing_Pipeline_Dataset.csv
• Size: 13.3 KB

Tasks Overview

1. Import Data

   • Load the raw data file into Power Query.

2. Data Cleaning Transformations

   • Remove duplicates.
   • Correct errors (e.g. wrong data types).
   • Standardize formats (e.g. date formats, text casing).

3. Data Enrichment

   • Merge and append data from multiple sources if applicable.

4. Documentation

   • Document each transformation step with detailed annotations.

Step-by-Step Process

1. Import Data

• Open Power Query in Excel or Power BI.
• Use the Get Data option:
  • For Excel: Data > Get Data > From File > From Text/CSV
  • For Power BI: Home > Get Data > Text/CSV
• Select the dataset and click Import.

2. Data Cleaning Transformations

A. Remove Duplicates

• Navigate to the Home tab.
• Click on Remove Rows > Remove Duplicates.
• Ensure you select relevant columns to check for duplicates.

B. Correct Errors

• Identify columns with data type inconsistencies.
• Use the Transform tab to change data types appropriately.
• For example, select a column and choose Data Type > Date or Text as needed.

C. Standardize Formats

• For text columns, apply formatting using:
  • For example, to convert text to uppercase:
    • Select column, Transform > Format > Uppercase.
  • To standardize date formats:
    • Select date column and set a consistent format using Transform > Date > Data Type.

3. Data Enrichment

• To merge datasets:
  • Use Home > Combine > Merge Queries.
  • Select another dataset and match on common fields.
• To append datasets:
  • Use Home > Combine > Append Queries and select multiple tables.

4. Documentation

• For every transformation step, click on Advanced Editor.
• You will see the M code generated for each step. Document each step using comments:

let
    // Step 1: Load raw data
    Source = Csv.Document(File.Contents("Path_to_your_file"), [Delimiter=",", Columns=4, Encoding=1252, QuoteStyle=QuoteStyle.None]),
    // Step 2: Remove duplicates based on "ID" column
    RemoveDuplicates = Table.Distinct(Source),
    // Step 3: Change data type for 'Date' column
    ChangedType = Table.TransformColumnTypes(RemoveDuplicates, {{"Date", type date}}),
    // Step 4: Standardize 'Name' to uppercase
    StandardizedText = Table.TransformColumns(ChangedType, {{"Name", Text.Upper, type text}}),
    // Further steps for merging and appending datasets can be added here
in
    StandardizedText

Expected Outcome

A complete Power Query script that automates the data cleansing process, documented with annotations for clarity on performed transformations.

Best Practices

• Regularly save your work and maintain version control for your queries.
• Test your pipeline with different datasets to ensure robustness.
• Comment extensively to improve the readability of your Power Query scripts.

Conclusion

This structured approach to building an automated data cleansing pipeline in Power Query will enhance data quality and prepare datasets for insightful analysis. Focusing on documentation and following best practices will also support long-term data management skills. For further learning, consider utilizing the resources available on the Enterprise DNA Platform.