How To Easily Standardize Data using Power Query(.pbix included)

Data standardization is one of the most critical steps in preparing datasets for analysis, reporting, or integration. Without consistent formats, even the most advanced analytics tools struggle to deliver reliable insights. Microsoft Power Query, built into Excel and Power BI, offers a straightforward way to clean and standardize data without heavy coding.

• Consistency in formats Standardization ensures that dates, numbers, and text fields follow a uniform structure, reducing errors in calculations and comparisons.
• Improved data quality Clean, standardized data eliminates duplicates, mismatches, and inconsistencies, making reports more trustworthy.
• Efficiency in workflows Power Query automates repetitive cleaning tasks, saving time and effort compared to manual corrections.
• Integration readiness When merging datasets from multiple sources, standardized values ensure smooth joins and transformations.

This is what we wish to achieve.

🎁 Get friend links for all of our 1500> Power BI learning articles here 🎁

Implementation in Power BI:

We will go through these steps:

• Going through the Table
• Opening Power Query
• Creating The Required Query
• Invoking The Custom Function For Standardization
• Applying The Changes And Completion

Happy learning!

1. Going through the Table:

The table is called Standardize_Marks.

• The Table has two columns, StudentID and ExamScore, both featuring 100% valid data with zero errors or empty values across 20 rows.
• Both columns contain exactly 20 distinct values, and every exam score in this small sample range from 45 to 98.

2. Opening Power Query:

• In the Home tab press on the Transform Data in the Queries section.
• It will open the Power Query window.

3. Creating The Required Query:

• In the Queries section left side right click then New Query →Blank Query.
• Rename it to fnStandardize, as I always say fn is prefixed in function name for better overall understanding and nothing more.

• Now press Advanced Editor in Home tab and replace existing code full with below code.

(SourceTable as table, ColumnName as text) =>
let
    // 1. Extract the column data as a list, ignoring nulls
    ColumnData = List.Buffer(List.Select(Table.Column(SourceTable, ColumnName), each _ <> null)),

    // 2. Calculate Mean and Standard Deviation of the column
    ColumnMean = List.Average(ColumnData),
    ColumnStdDev = List.StandardDeviation(ColumnData),

    // 3. Add a new column with the calculated Z-scores
    StandardizedTable = Table.AddColumn(
        SourceTable,
        ColumnName & "_Standardized",
        each if Record.Field(_, ColumnName) = null then null
             else (Record.Field(_, ColumnName) - ColumnMean) / ColumnStdDev,
        type number
    )
in
    StandardizedTable

(SourceTable as table, ColumnName as text) =>
let
    // 1. Extract the column data as a list, ignoring nulls
    ColumnData = List.Buffer(List.Select(Table.Column(SourceTable, ColumnName), each _ <> null)),

    // 2. Calculate Mean and Standard Deviation of the column
    ColumnMean = List.Average(ColumnData),
    ColumnStdDev = List.StandardDeviation(ColumnData),

    // 3. Add a new column with the calculated Z-scores
    StandardizedTable = Table.AddColumn(
        SourceTable,
        ColumnName & "_Standardized",
        each if Record.Field(_, ColumnName) = null then null
             else (Record.Field(_, ColumnName) - ColumnMean) / ColumnStdDev,
        type number
    )
in
    StandardizedTable

4. Invoking The Custom Function For Standardization:

• In SourceTable select Standardize_Marks table, type ExamScore in ColumnName and press Invoke.

• Click on Close & Apply in the upper right column.

• All the marks are Standardized with value nearly 75 being the center point.

5. Applying The Changes And Completion:

• In the Report view, you will find the new table Invoked Function in the Data section on the right side.
• Now in a Table visualization you can add the columns from new table for further analysis.

Download the data for the KPI from this link.

Download the PBIX file from this link.

Shashanka Shekhar - Medium Read writing from Shashanka Shekhar on Medium. Contributor for Microsoft Power BI. I like Data Analysis and Data…

Thank you for your attention!

Follow me or subscribe to get all my Power BI articles!

One Hot Encoding Categorical Columns in Power Query(.pbix included) When working with data in Power Query, handling categorical variables effectively is crucial for building robust…

💡 MUST TRY — Power BI GPT — Personal Power BI Learning Coach💡

Power BI Masterclass Article Classification

Level: Beginner

Category: DAX

Tags: Tutorial, DAX