Implementing Column-Level Data Profiling and Statistical Summaries in Power Query: Distribution Analysis, Outlier Detection, and Quality Metrics for Practitioner Workflows

Introduction

You've just received a CSV export from a legacy CRM system — 80,000 rows of customer transaction data — and your stakeholder wants a Power BI dashboard built by end of week. Before you write a single DAX measure or drag a visual onto the canvas, you need to answer some fundamental questions: How complete is this data? Are the numeric fields in a plausible range? Are there duplicate records? Does the OrderDate column actually contain dates, or a creative mix of formats and null values?

Most practitioners answer these questions the hard way — building the model first, then discovering anomalies when the numbers don't match the source system. A better approach is to build data profiling directly into your Power Query pipeline, so your ETL layer surfaces quality issues before they contaminate downstream analysis. Column-level profiling means each column gets quantified: its completeness rate, value distribution, statistical boundaries, and outlier flags — all computed automatically every time the query refreshes.

By the end of this lesson, you'll be able to build a reusable profiling framework in M that generates statistical summaries for any table, detect outliers using both IQR and Z-score approaches, produce a quality metrics report you can publish alongside your data model, and wire it all into a practitioner-grade workflow that you can drop into production pipelines.

What you'll learn:

• How to compute column-level statistics (count, null rate, min, max, mean, standard deviation, percentiles) using M code
• How to build a reusable profiling query that operates across any table dynamically
• Two outlier detection strategies — IQR-based and Z-score-based — and when to use each
• How to generate a quality metrics summary table for monitoring and reporting
• How to integrate profiling into a multi-stage ETL pipeline without degrading refresh performance

---

Prerequisites

This lesson assumes you're comfortable writing M code by hand in the Advanced Editor and understand fundamental query concepts. You should know how to create custom columns, work with List and Record functions, and navigate the Power Query interface confidently. If you need a foundation refresher, start with Power Query 101: Connect, Transform, Load before proceeding.

You should also have a basic grasp of descriptive statistics — mean, standard deviation, percentiles, and interquartile range. We'll explain how these apply in the context of M, but we won't cover what they mean from first principles.

---

Why Build Profiling in Power Query (and Not Somewhere Else)?

Before writing a line of code, let's settle the "why here?" question, because your options include SQL, Python/pandas, or dedicated DQ tools.

The case for Power Query is this: your transformation logic already lives here. If you're using Power Query as your ETL layer, profiling logic that sits alongside your cleansing and shaping steps runs on the same engine, against the same data snapshot, with no extra tooling to maintain. When you're building multi-stage staging architectures, you can slot a profiling layer between your raw and cleansed stages — profiling the raw ingestion and then passing cleansed data forward.

The tradeoff is performance. Profiling queries that compute statistics across large tables add evaluation cost. We'll address this directly in the performance section. For now: if your source is SQL Server, some of the profiling can fold back to the server. If your source is flat files, you'll be doing the work in the M engine.

Note: Power Query's built-in column profiling UI (the column distribution, column quality, and column profile views in the View ribbon) gives you interactive exploration during development. This lesson teaches you how to codify that profiling so it runs automatically on refresh and outputs a structured result you can load into your data model.

---

Setting Up Your Profiling Foundation

Let's work with a realistic dataset. Suppose you have a sales transactions table called SalesRaw with these columns:

Column	Type	Notes
TransactionID	Int64	Primary key
CustomerID	Int64	Foreign key
OrderDate	Date	Should be complete
Region	Text	Category field
SalesAmount	Decimal	Revenue, should be positive
Quantity	Int64	Units sold
DiscountRate	Decimal	0.0 to 1.0 range

Your goal is to build a ProfileSalesRaw query that produces one row per column, with completeness and basic statistics computed automatically.

Start by connecting to your source and staging the raw data. In a new blank query, open the Advanced Editor and establish your foundation:

let
    Source = SalesRaw,
    TableName = "SalesRaw",
    RowCount = Table.RowCount(Source),
    ColumnNames = Table.ColumnNames(Source)
in
    ColumnNames

This gives you the list of column names to iterate over. The RowCount variable will be used as the denominator in your completeness calculations. Hold onto both — you'll need them throughout.

---

Computing Completeness Metrics per Column

Completeness is the most foundational quality metric. For each column, you want to know:

1. How many values are non-null (valid count)
2. How many are null
3. How many are errors
4. The null rate as a percentage

In M, these are computed using List functions applied to each column's values, which you extract using Table.Column. Here's a function that computes completeness for a single column:

let
    Source = SalesRaw,
    RowCount = Table.RowCount(Source),

    GetColumnProfile = (tbl as table, colName as text, totalRows as number) as record =>
        let
            ColValues = Table.Column(tbl, colName),
            ValidCount = List.NonNullCount(ColValues),
            NullCount = totalRows - ValidCount,
            ErrorCount = List.Count(List.Select(ColValues, each _ is error)),
            NullRate = if totalRows = 0 then null else Number.Round(NullCount / totalRows * 100, 2),
            CompleteRate = if totalRows = 0 then null else Number.Round(ValidCount / totalRows * 100, 2)
        in
            [
                ColumnName = colName,
                TotalRows = totalRows,
                ValidCount = ValidCount,
                NullCount = NullCount,
                ErrorCount = ErrorCount,
                NullRate = NullRate,
                CompleteRate = CompleteRate
            ],

    ColumnNames = Table.ColumnNames(Source),
    ProfileRecords = List.Transform(ColumnNames, each GetColumnProfile(Source, _, RowCount)),
    ProfileTable = Table.FromRecords(ProfileRecords)
in
    ProfileTable

Warning: List.Select(ColValues, each _ is error) will itself throw an error if the list contains error values, because accessing an error value propagates it. Wrap your column extraction in a try expression or use Table.Column with MissingField.UseNull if your data is particularly unpredictable. We'll show robust error handling in the troubleshooting section.

Run this and you'll get a clean table with one row per column showing null rates and completeness. Already useful — but this is just the foundation.

---

Adding Numeric Distribution Statistics

For columns containing numeric data, completeness alone doesn't catch problems like negative sales amounts, impossible discount rates above 1.0, or suspiciously round numbers that suggest imputed data. You need distributional statistics.

The key M functions are in the List namespace:

• List.Min, List.Max
• List.Average (arithmetic mean)
• List.StandardDeviation
• List.Percentile (takes a list and a percentile value between 0 and 1)

Here's an extended function that conditionally computes numeric statistics based on the column's type:

let
    Source = SalesRaw,
    RowCount = Table.RowCount(Source),

    GetNumericStats = (colValues as list) as record =>
        let
            CleanValues = List.Select(colValues, each _ <> null and not (_ is error)),
            N = List.Count(CleanValues),
            Mean = if N = 0 then null else List.Average(CleanValues),
            StdDev = if N < 2 then null else List.StandardDeviation(CleanValues),
            MinVal = if N = 0 then null else List.Min(CleanValues),
            MaxVal = if N = 0 then null else List.Max(CleanValues),
            P25 = if N = 0 then null else List.Percentile(CleanValues, 0.25),
            P50 = if N = 0 then null else List.Percentile(CleanValues, 0.50),
            P75 = if N = 0 then null else List.Percentile(CleanValues, 0.75),
            IQR = if P25 = null or P75 = null then null else P75 - P25
        in
            [
                Mean = Mean,
                StdDev = StdDev,
                Min = MinVal,
                Max = MaxVal,
                P25 = P25,
                Median = P50,
                P75 = P75,
                IQR = IQR
            ],

    GetColumnProfile = (tbl as table, colName as text, totalRows as number) as record =>
        let
            ColValues = Table.Column(tbl, colName),
            ColType = Value.Type(List.First(List.Select(ColValues, each _ <> null))),
            IsNumeric = ColType = type number or ColType = Int64.Type or ColType = Decimal.Type,
            ValidCount = List.NonNullCount(ColValues),
            NullCount = totalRows - ValidCount,
            NullRate = Number.Round(NullCount / totalRows * 100, 2),
            NumStats = if IsNumeric then GetNumericStats(ColValues) else [Mean=null, StdDev=null, Min=null, Max=null, P25=null, Median=null, P75=null, IQR=null]
        in
            [
                ColumnName = colName,
                TotalRows = totalRows,
                ValidCount = ValidCount,
                NullCount = NullCount,
                NullRate = NullRate,
                Mean = NumStats[Mean],
                StdDev = NumStats[StdDev],
                Min = NumStats[Min],
                Max = NumStats[Max],
                P25 = NumStats[P25],
                Median = NumStats[Median],
                P75 = NumStats[P75],
                IQR = NumStats[IQR]
            ],

    ColumnNames = Table.ColumnNames(Source),
    ProfileRecords = List.Transform(ColumnNames, each GetColumnProfile(Source, _, RowCount)),
    ProfileTable = Table.FromRecords(ProfileRecords)
in
    ProfileTable

Tip: Value.Type(List.First(...)) infers the type from the first non-null value. This is a heuristic, not a guarantee — a column that's mostly null with one stray text value will fool it. A more robust approach is to check the column's declared type using Table.Schema, which we'll cover shortly.

---

Using Table.Schema for Reliable Type Detection

Instead of inferring types from values, use Table.Schema — it returns the declared metadata for every column in your table. This is far more reliable:

let
    Source = SalesRaw,
    Schema = Table.Schema(Source),
    // Schema has columns: Name, Position, TypeName, Kind, IsNullable, NumericPrecisionBase, etc.
    NumericTypes = {"Int64", "Decimal", "Double", "Single", "Percentage", "Currency"},
    
    IsNumericColumn = (colName as text) as logical =>
        let
            ColRow = Table.SelectRows(Schema, each [Name] = colName),
            TypeName = if Table.IsEmpty(ColRow) then "" else ColRow{0}[TypeName]
        in
            List.Contains(NumericTypes, TypeName)
in
    IsNumericColumn

This IsNumericColumn function becomes the gatekeeper in your profiling loop — only numeric columns get the statistical treatment. Wire it in by replacing the IsNumeric logic in the previous example with a call to this function.

Key insight: Table.Schema is one of the most underused functions in M. It gives you TypeName, IsNullable, NumericPrecision, NumericScale, and more — everything you need for a comprehensive schema audit as well as a data audit. Consider building a separate SchemaProfile query alongside your statistical profiling query for complete metadata coverage.

---

Categorical Column Profiling: Distinct Values and Frequency

For text columns like Region, you want different metrics: distinct value count, most frequent value, and whether the column has suspiciously high or low cardinality.

GetCategoricalStats = (colValues as list) as record =>
    let
        CleanValues = List.Select(colValues, each _ <> null and not (_ is error)),
        N = List.Count(CleanValues),
        DistinctValues = List.Distinct(CleanValues),
        DistinctCount = List.Count(DistinctValues),
        
        // Build frequency table
        FreqTable = List.Transform(
            DistinctValues,
            each [Value = _, Count = List.Count(List.Select(CleanValues, (v) => v = _))]
        ),
        FreqSorted = List.Sort(FreqTable, (a, b) => Value.Compare(b[Count], a[Count])),
        MostFrequentValue = if List.IsEmpty(FreqSorted) then null else FreqSorted{0}[Value],
        MostFrequentCount = if List.IsEmpty(FreqSorted) then null else FreqSorted{0}[Count],
        MostFrequentPct = if N = 0 then null else Number.Round(MostFrequentCount / N * 100, 2)
    in
        [
            DistinctCount = DistinctCount,
            MostFrequentValue = MostFrequentValue,
            MostFrequentPct = MostFrequentPct
        ]

Warning: The frequency table approach above uses List.Select inside a List.Transform — this is O(n²) and will be extremely slow on columns with high cardinality or large row counts. For production use on tables with more than ~50,000 rows, compute categorical frequencies using a Table.Group approach on the source table before extracting the max, rather than iterating over the list. We'll show the optimized version in the performance section.

---

Outlier Detection: The IQR Method

The IQR (interquartile range) method flags outliers as values that fall outside the "fences" defined by:

• Lower fence: Q1 − 1.5 × IQR
• Upper fence: Q3 + 1.5 × IQR

This is non-parametric — it makes no assumptions about the underlying distribution — which makes it robust for the kinds of messy real-world data you'll encounter in practice.

Here's a query that takes your SalesAmount column and flags outliers:

let
    Source = SalesRaw,
    ColValues = Table.Column(Source, "SalesAmount"),
    CleanValues = List.Select(ColValues, each _ <> null and not (_ is error)),
    
    Q1 = List.Percentile(CleanValues, 0.25),
    Q3 = List.Percentile(CleanValues, 0.75),
    IQR = Q3 - Q1,
    LowerFence = Q1 - 1.5 * IQR,
    UpperFence = Q3 + 1.5 * IQR,
    
    // Add outlier flag to source table
    WithOutlierFlag = Table.AddColumn(
        Source,
        "SalesAmount_OutlierFlag",
        each if [SalesAmount] = null then null
             else if [SalesAmount] < LowerFence or [SalesAmount] > UpperFence then "Outlier"
             else "Normal",
        type text
    ),
    
    // Summary stats
    OutlierCount = List.Count(List.Select(ColValues, each _ <> null and (_ < LowerFence or _ > UpperFence))),
    OutlierRate = Number.Round(OutlierCount / List.Count(CleanValues) * 100, 2)
in
    [
        LowerFence = LowerFence,
        UpperFence = UpperFence,
        OutlierCount = OutlierCount,
        OutlierRate = OutlierRate,
        TableWithFlags = WithOutlierFlag
    ]

The key design decision here is to return both the fence thresholds and the flagged table as fields in a record. This lets downstream queries access either the metrics or the enriched data from a single computed step.

---

Outlier Detection: The Z-Score Method

The Z-score method is parametric — it assumes an approximately normal distribution — and flags values that are more than k standard deviations from the mean (typically k = 2.5 or 3):

let
    Source = SalesRaw,
    ColValues = List.Select(Table.Column(Source, "SalesAmount"), each _ <> null),
    
    Mean = List.Average(ColValues),
    StdDev = List.StandardDeviation(ColValues),
    ZThreshold = 3.0,
    
    WithZScore = Table.AddColumn(
        Source,
        "SalesAmount_ZScore",
        each if [SalesAmount] = null then null
             else Number.Round(Number.Abs([SalesAmount] - Mean) / StdDev, 3),
        type number
    ),
    
    WithOutlierFlag = Table.AddColumn(
        WithZScore,
        "SalesAmount_ZOutlier",
        each if [SalesAmount_ZScore] = null then null
             else if [SalesAmount_ZScore] > ZThreshold then "Outlier"
             else "Normal",
        type text
    )
in
    WithOutlierFlag

When to use IQR vs Z-score:

• IQR is better when your data has heavy tails, skewness, or known non-normal distributions (common in sales data, where a few large enterprise deals skew the distribution heavily)
• Z-score is better when your data is roughly symmetric and you want a mathematically interpretable threshold (e.g., "flag anything beyond 3σ")
• For financial data, IQR almost always performs better because revenue distributions are rarely normal

Key insight: Both methods should be used as investigation triggers, not automatic deletion criteria. A SalesAmount of $2.4M might be a data entry error or it might be your most important customer. Flag it for review, don't silently drop it. This is why the output is a flag column rather than a filtered table.

---

Building the Quality Metrics Summary Table

Now that you have completeness, distributions, and outlier statistics, you want to assemble them into a structured quality report — a single table that gives you a pass/fail view of every column. This becomes a query you load into your model alongside your fact tables.

let
    Source = SalesRaw,
    RowCount = Table.RowCount(Source),
    Schema = Table.Schema(Source),
    NumericTypes = {"Int64", "Decimal", "Double", "Currency"},
    
    // Thresholds (adjust per your data quality SLA)
    MaxAllowedNullRate = 5.0,       // columns > 5% null fail
    MaxAllowedOutlierRate = 2.0,    // columns > 2% outliers flag for review
    
    GetColumnMetrics = (colName as text) as record =>
        let
            ColRow = Table.SelectRows(Schema, each [Name] = colName),
            TypeName = if Table.IsEmpty(ColRow) then "Unknown" else ColRow{0}[TypeName],
            IsNumeric = List.Contains(NumericTypes, TypeName),
            ColValues = Table.Column(Source, colName),
            CleanValues = List.Select(ColValues, each _ <> null and not (_ is error)),
            ValidCount = List.Count(CleanValues),
            NullCount = RowCount - List.NonNullCount(ColValues),
            NullRate = Number.Round(NullCount / RowCount * 100, 2),
            
            // Numeric stats (conditional)
            Mean = if IsNumeric and ValidCount > 0 then List.Average(CleanValues) else null,
            StdDev = if IsNumeric and ValidCount > 1 then List.StandardDeviation(CleanValues) else null,
            MinVal = if IsNumeric and ValidCount > 0 then List.Min(CleanValues) else null,
            MaxVal = if IsNumeric and ValidCount > 0 then List.Max(CleanValues) else null,
            Q1 = if IsNumeric and ValidCount > 0 then List.Percentile(CleanValues, 0.25) else null,
            Q3 = if IsNumeric and ValidCount > 0 then List.Percentile(CleanValues, 0.75) else null,
            IQR = if Q1 <> null and Q3 <> null then Q3 - Q1 else null,
            LowerFence = if IQR <> null then Q1 - 1.5 * IQR else null,
            UpperFence = if IQR <> null then Q3 + 1.5 * IQR else null,
            OutlierCount = if IsNumeric and LowerFence <> null then
                List.Count(List.Select(CleanValues, each _ < LowerFence or _ > UpperFence))
                else null,
            OutlierRate = if OutlierCount <> null and ValidCount > 0 then
                Number.Round(OutlierCount / ValidCount * 100, 2)
                else null,
            
            // Distinct values (for all columns)
            DistinctCount = List.Count(List.Distinct(CleanValues)),
            
            // Quality flags
            NullQualityStatus = if NullRate <= MaxAllowedNullRate then "PASS" else "FAIL",
            OutlierQualityStatus = if OutlierRate = null then "N/A"
                                   else if OutlierRate <= MaxAllowedOutlierRate then "PASS"
                                   else "REVIEW",
            OverallStatus = if NullQualityStatus = "FAIL" then "FAIL"
                            else if OutlierQualityStatus = "REVIEW" then "REVIEW"
                            else "PASS"
        in
            [
                ColumnName = colName,
                DataType = TypeName,
                TotalRows = RowCount,
                ValidCount = ValidCount,
                NullCount = NullCount,
                NullRate = NullRate,
                DistinctCount = DistinctCount,
                Mean = Mean,
                StdDev = StdDev,
                Min = MinVal,
                Max = MaxVal,
                Q1 = Q1,
                Median = if IsNumeric and ValidCount > 0 then List.Percentile(CleanValues, 0.50) else null,
                Q3 = Q3,
                IQR = IQR,
                LowerFence = LowerFence,
                UpperFence = UpperFence,
                OutlierCount = OutlierCount,
                OutlierRate = OutlierRate,
                NullQualityStatus = NullQualityStatus,
                OutlierQualityStatus = OutlierQualityStatus,
                OverallStatus = OverallStatus
            ],
    
    ColumnNames = Table.ColumnNames(Source),
    MetricRecords = List.Transform(ColumnNames, each GetColumnMetrics(_)),
    MetricsTable = Table.FromRecords(MetricRecords),
    
    // Add profile run timestamp
    WithTimestamp = Table.AddColumn(MetricsTable, "ProfileRunTime", each DateTimeZone.UtcNow(), type datetimezone)
in
    WithTimestamp

This is your complete profiling query. Load it as a separate table in your model — disable it from loading to the data model if you only want it for Power Query inspection, or load it to create a Data Quality dashboard in Power BI.

Tip: Parameterize MaxAllowedNullRate and MaxAllowedOutlierRate using Power Query parameters so analysts can adjust thresholds without touching M code. This pairs naturally with parameterized queries and dynamic data sources, where you'll learn how to expose these controls cleanly.

---

Integrating Profiling into a Multi-Stage Pipeline

Where does profiling fit in a real pipeline? The short answer: between your raw ingestion layer and your cleansed layer, with the profiling query reading from raw and writing a metrics table that your data steward can monitor.

Your query dependency graph looks like this:

[SalesRaw]  ──┬──→  [SalesProfiled]     (quality metrics table, loaded separately)
              └──→  [SalesCleansed]     (proceeds only after profiling logic passes)

This separation is a core principle in building multi-stage staging architectures in Power Query. Your raw layer captures the data exactly as received. Your profiling layer quantifies it. Your cleansed layer applies the transformations you designed based on what profiling revealed.

For the cleansed layer, you can use the computed fence values from your profiling query as filtering thresholds. Reference your profiling query and extract the boundaries:

let
    // Reference profiling results to get fence values dynamically
    ProfileResults = SalesProfiled,
    
    SalesAmountRow = Table.SelectRows(ProfileResults, each [ColumnName] = "SalesAmount"){0},
    LowerFence = SalesAmountRow[LowerFence],
    UpperFence = SalesAmountRow[UpperFence],
    
    Source = SalesRaw,
    
    // Apply cleansing using profiled boundaries
    FilteredRows = Table.SelectRows(Source, each 
        [SalesAmount] <> null and 
        [SalesAmount] >= LowerFence and 
        [SalesAmount] <= UpperFence
    )
in
    FilteredRows

This is elegant because your cleansing thresholds adapt to each data refresh. If this month's data has a different distribution (perhaps a new market was onboarded with higher deal sizes), the fences recalculate and your cleansing logic stays appropriate.

---

Performance Considerations

Let's be direct: this profiling query touches every value in every column of your source table. For a 7-column, 80,000-row table, it performs 7 full column scans. For a 50-column table with 500,000 rows, it's expensive.

Strategies to manage this:

1. Buffer your source table. If the source is a flat file or web API, use Table.Buffer(Source) at the top of your profiling query. This forces a single evaluation of the source and allows subsequent column scans to read from in-memory cache rather than re-evaluating the source connector each time.

let
    Source = Table.Buffer(SalesRaw),
    RowCount = Table.RowCount(Source),
    ...

This is discussed in depth in Power Query Performance: Master Folding, Buffering & Optimization Techniques. The key tradeoff is memory pressure vs. re-evaluation cost.

2. Profile only critical columns. Instead of profiling all columns, maintain a config table (a simple Excel sheet or Power Query table) listing which columns to profile and what thresholds to apply. Filter ColumnNames against this list before your profiling loop.

3. Separate profiling from main model refresh. In Power BI Service, you can load the profiling query to a separate dataset that refreshes on a different schedule — perhaps daily — while your main model refreshes hourly. See orchestrating multi-query refresh dependencies for the mechanics.

4. For SQL sources, use native queries. If your source is SQL Server, you can push statistical computation to the database engine using native SQL queries for MIN, MAX, AVG, STDEV, PERCENTILE_CONT, which are orders of magnitude faster than M-side computation. Refer to Connecting to SQL Server in Power Query: Native Queries and Credential Management for how to pass native query strings that won't break query folding.

Warning: Never apply Table.Buffer in queries that are already being buffered by the Power Query engine's caching. Redundant buffering can consume significant memory. Profile your queries in the Query Diagnostics tool before and after adding buffers to confirm actual improvement.

---

Hands-On Exercise: Build a Complete Sales Data Quality Report

Let's put everything together in a structured exercise. You'll build a three-query system: a raw staging query, a profiling query, and a quality gate.

Step 1: Create your sample data

In Power Query, create a blank query called SalesRaw using Table.FromRows to simulate 20 rows with intentional quality issues:

let
    Source = Table.FromRows(
        {
            {1001, 101, #date(2024,1,15), "North", 1250.00, 5, 0.10},
            {1002, 102, #date(2024,1,16), "South", 980.50, 3, 0.05},
            {1003, 103, #date(2024,1,17), "North", null, 2, 0.00},
            {1004, 104, #date(2024,1,18), "East", -150.00, 1, 0.15},  // negative amount
            {1005, 105, #date(2024,1,19), "West", 2200.75, 8, 0.20},
            {1006, 106, null, "North", 875.00, 4, 0.10},              // null date
            {1007, 107, #date(2024,1,21), "South", 99999.99, 2, 0.00}, // likely outlier
            {1008, 108, #date(2024,1,22), "East", 1100.00, 6, 1.50},  // impossible discount
            {1009, 109, #date(2024,1,23), "West", 560.00, 3, 0.05},
            {1010, 110, #date(2024,1,24), "North", 720.00, 2, 0.10},
            {1011, 111, #date(2024,1,25), "South", 1340.00, 5, 0.15},
            {1012, 112, #date(2024,1,26), "East", 890.00, 3, 0.08},
            {1013, 113, #date(2024,1,27), "West", null, 1, 0.00},     // null amount
            {1014, 114, #date(2024,1,28), "North", 1675.25, 7, 0.20},
            {1015, 115, #date(2024,1,29), "South", 430.00, 2, 0.05},
            {1016, 116, #date(2024,1,30), "East", 955.00, 4, 0.10},
            {1017, 117, #date(2024,1,31), "West", 1890.00, 6, 0.18},
            {1018, 118, #date(2024,2,1), "North", 670.00, 3, 0.12},
            {1019, 119, #date(2024,2,2), "South", 1120.00, 5, 0.08},
            {1020, 120, #date(2024,2,3), "East", 0.01, 1, 0.00}       // suspicious near-zero
        },
        {"TransactionID", "CustomerID", "OrderDate", "Region", "SalesAmount", "Quantity", "DiscountRate"}
    ),
    TypedSource = Table.TransformColumnTypes(Source, {
        {"TransactionID", Int64.Type},
        {"CustomerID", Int64.Type},
        {"OrderDate", type date},
        {"Region", type text},
        {"SalesAmount", type number},
        {"Quantity", Int64.Type},
        {"DiscountRate", type number}
    })
in
    TypedSource

Step 2: Build the profiling query

Create a new blank query called SalesProfiled. Paste the complete quality metrics query from the previous section, replacing Source = SalesRaw with a reference to your query.

Step 3: Examine the results

After running SalesProfiled, you should observe:

• SalesAmount: NullRate = 10%, OutlierFlag on row 1007 ($99,999.99), lower fence catches row 1004 (-$150)
• OrderDate: NullRate = 5% (one null), TypeName = "Date"
• DiscountRate: Max = 1.50 (impossible — should not exceed 1.0), which your IQR fence should catch as an outlier

Step 4: Verify your quality gates

Add a final step to SalesProfiled that extracts only the FAIL and REVIEW rows:

FailedColumns = Table.SelectRows(WithTimestamp, each [OverallStatus] <> "PASS")

This becomes your data steward's watchlist — three columns with issues, clearly identified, with the specific metrics that explain why.

---

Common Mistakes & Troubleshooting

"Expression.Error: The column 'X' of the table wasn't found"

This happens when your source schema changes and a column your profiling query hardcodes no longer exists. The fix is to use Table.ColumnNames dynamically and cross-reference against Table.Schema before accessing any column. This is closely related to the patterns in handling dynamic schema changes in Power Query.

Profiling query times out or is extremely slow

First, check whether query folding is occurring. Open Query Diagnostics (Tools → Start Diagnostics) and run the query. Look for whether the engine is making multiple source requests. If your source is a CSV or Excel file and you're not using Table.Buffer, each Table.Column call may re-read the file. Add Source = Table.Buffer(OriginalSource) as your first step.

List.Percentile returns null on some columns

This occurs when CleanValues contains fewer than 2 items. Always guard percentile calculations with if List.Count(CleanValues) >= 2 then ... else null. Also, List.Percentile in some Power Query versions requires the second argument to be a literal number, not a variable. If you're computing multiple percentiles, call it separately for each rather than trying to pass a dynamic value.

Error values in columns causing silent failures

If a column contains M error values (not nulls — actual errors), List.Count will count them, but List.Average or List.Percentile will propagate the error. Always filter with each _ <> null and not (_ is error) for your clean list. For deeper guidance on error handling in M, see Debugging and Error Handling in M: Building Robust try-otherwise Logic and Diagnostic Workflows in Power Query.

Profile shows identical results every refresh even after source data changes

This is a caching issue. In Power BI Desktop, query results can be cached between refreshes in the same session. Close and reopen the file, or use "Refresh Preview" in the Query Editor explicitly. In Power BI Service, confirm the scheduled refresh ran successfully.

Outlier detection flags too many or too few rows

If IQR-based detection is too aggressive, your data may be heavily skewed. Try using a larger multiplier (2.0 or 3.0 instead of 1.5) or switch to Z-score with a threshold of 3.0. If it's too permissive, your distribution may be bimodal (two distinct populations in one column). In that case, profile the column separately for each population — for example, partition SalesAmount by Region before computing fences. Grouping and aggregating in Power Query covers the Group By mechanics you'd need for this.

Note: Data profiling is not a one-time activity. Build it as a persistent layer that runs with every refresh and accumulates a history of quality metrics over time. A column whose null rate climbs from 2% to 18% across three months tells a story about an upstream system change — one you'll miss if you only profile ad hoc.

---

Summary & Next Steps

You now have a complete column-level profiling framework in M that you can drop into any Power Query pipeline. Here's what you've built:

• A completeness layer that quantifies null and error rates per column against configurable thresholds
• A statistical layer that computes mean, standard deviation, min, max, and the full five-number summary for numeric columns
• IQR-based and Z-score-based outlier detection with flag columns attached to the source table
• A quality metrics summary table with PASS/FAIL/REVIEW status per column, timestamped at each refresh
• Integration patterns that wire profiling into a multi-stage pipeline without disrupting your transformation logic

The natural next steps from here are:

1. Automate quality gate enforcement — rather than just flagging failures, implement a pipeline abort pattern that raises an explicit error when a FAIL column is detected, preventing bad data from reaching your model. The patterns in implementing data validation and quality checks in Power Query extend what you've built here into full assertion pipelines.

2. Build a reusable profiling function library — wrap your GetColumnMetrics function in a module that accepts any table and threshold record as parameters. Building Reusable Power Query Function Libraries walks through the architecture for doing this cleanly.

3. Extend to cross-column validation — single-column profiling catches unary anomalies. Cross-column rules (e.g., DiscountRate > 0 requires a non-null ApprovalCode) require a different approach using Table.AddColumn with multi-column references. This is where profiling graduates into full data quality management.

4. Surface profiling in Power BI — load your SalesProfiled table and build a Data Quality Monitor dashboard with conditional formatting on OverallStatus, trend lines for null rates over time, and drill-through to the specific rows flagged as outliers. Your profiling data is already structured to support all of this.

---

META