← Case Studies Data preparation

Automated Dataset Generation Using Power Query

Automated dataset generation using Power Query to consolidate and transform data from multiple files into a dashboard-ready dataset.

At a glance

  1. 01 Problem

    A dashboard depended on several source files with different structures, consolidated manually before each refresh.

  2. 02 Constraint

    Inputs vary in shape, and the dashboard needs every relevant combination present to report accurately.

  3. 03 Approach

    A Power Query pipeline imports the files, aligns and cleans columns, applies validation checks, and generates the permutations the report requires.

  4. 04 Outcome

    A refreshable, dashboard-ready dataset that updates when the source files change, with less manual preparation.

Overview

Business dashboards often rely on data collected from multiple files and sources. However, combining these datasets manually can lead to inconsistencies, errors, and significant time spent on data preparation.

In this project, I developed a Power Query automation process that consolidates and transforms data from multiple files into a clean, structured dataset ready for dashboard reporting. The solution uses a series of data transformations and logical combinations to ensure that the final dataset is accurate and consistent.

This automated pipeline significantly reduced the manual effort required to prepare data while ensuring reliable inputs for business intelligence dashboards.


Problem

The dashboard relied on data stored across multiple files with different structures, which created several challenges:

  • Time-consuming manual data consolidation
  • Risk of inconsistent formatting and errors
  • Difficulty ensuring data accuracy across multiple sources
  • Repetitive preparation steps before dashboard refresh

Without automation, preparing the dataset for reporting required significant manual intervention.


Solution

I designed a Power Query-based automation workflow that processes multiple data sources and generates a unified dataset for dashboard consumption.

The automation performs several transformation steps, including:

  • Importing data from multiple files
  • Applying transformation logic
  • Generating permutations and combinations required for reporting
  • Structuring the final dataset to align with dashboard requirements

Once configured, the process allows the dataset to be refreshed automatically whenever the source files are updated.


Key Features

1. Multi-Source Data Integration

The workflow reads data from multiple input files, consolidating them into a single structured dataset.

This removes the need for manual data merging.


2. Automated Data Transformation

Using Power Query transformations, the process cleans and standardizes the data by:

  • Removing inconsistencies
  • Aligning column structures
  • Transforming raw data into analysis-ready formats
  • Applying validation checks so the data stays accurate for refreshable Power BI dashboards

3. Permutation-Based Data Structuring

The solution generates the required permutations and combinations to ensure the dataset contains all relevant relationships needed for accurate dashboard reporting.

This ensures the dashboard visuals reflect complete and meaningful insights.


4. Dashboard-Ready Output

The final output is a curated dataset optimized for Power BI dashboards, ensuring smooth visualization and accurate reporting.

The dataset structure allows dashboards to refresh efficiently without requiring additional preprocessing.


Technologies & Tools

  • Power Query – Data transformation and automation
  • Microsoft Excel – Data source management
  • Power BI – Dashboard integration and visualization

Impact

This automation significantly improved the data preparation process by:

  • Reducing manual data preparation effort
  • Improving data accuracy and consistency
  • Enabling faster dashboard refresh cycles
  • Creating a reliable data pipeline for reporting

The project demonstrates how data transformation automation can improve both efficiency and reporting quality.


Key Skills Demonstrated

  • Data Transformation with Power Query
  • Multi-Source Data Integration
  • Data Pipeline Automation
  • Dashboard Data Modeling
  • Business Intelligence Data Preparation