Implementing Custom Table Sampling and Statistical Estimation Pipelines in Power Query M: Stratified, Systematic, and Random Row Selection for Large Dataset Profiling

Introduction

You're handed a sales transaction table with 12 million rows. Your Power BI model refresh is timing out. Your stakeholders want column-level statistics — cardinality, null rates, distribution skew — but pulling the entire dataset into memory isn't feasible. What do you do?

The instinct is to slice to some arbitrary Table.FirstN and call it a day. But that's not sampling — that's just taking the top of the pile, which is often the worst possible representation of your data. Real sampling requires deliberate row selection strategies that give you statistically defensible estimates from a fraction of the data. Done right, you can characterize a 10-million-row dataset by examining 50,000 rows, with confidence intervals you can actually trust.

By the end of this lesson, you'll have a complete, reusable sampling and profiling pipeline built in Power Query M. We'll implement three industry-standard sampling methods — simple random, systematic, and stratified — then wire them into a statistical estimation framework that produces column profiles. You'll understand not just the M mechanics but the statistical reasoning behind each approach, so you can choose the right method for each situation.

What you'll learn:

• How to implement simple random sampling, systematic sampling, and stratified sampling in pure M code
• How to build a reusable sampling dispatcher that selects strategy based on data characteristics
• How to construct a column profiling pipeline that estimates null rates, cardinality, mean, and skew from a sample
• How to manage the performance trade-offs of each sampling approach in Power Query's evaluation model
• How to use metadata and type annotation to make your sampling functions self-documenting and composable

---

Prerequisites

You should be comfortable writing multi-step let expressions, working with List.Generate and List.Transform, and have a working understanding of how tables, lists, and records relate to each other in M. If you need a refresher on M's core data structures, List and Record Operations in M: Transform, Select, and Combine Data Structures covers the foundation thoroughly. You should also understand what query folding is and roughly when it applies — this lesson will intentionally work outside folding boundaries for most operations.

---

Why Native Sampling Falls Short

Power Query exposes Table.Sample natively in some contexts, and you can always use Table.SelectRows with a random condition. But these tools have serious gaps for production profiling work.

Table.Sample is a convenience function with no guaranteed statistical properties. It doesn't give you control over the sampling fraction, the random seed, or the stratification schema. Table.SelectRows with Number.Random() is stateless — each row evaluation is independent, so you can't guarantee your sample size or reproduce the same sample later.

The deeper problem is representativeness. If your dataset is sorted by date and you take the first 1% of rows, you've sampled only the oldest records. If your data has natural clusters — regions, product categories, customer segments — a flat random sample may accidentally under-sample some clusters entirely. Stratified sampling solves this, but Power Query gives you no native mechanism for it.

Before we write a line of code, let's define the three strategies clearly:

• Simple random sampling: Every row has an equal probability of selection. Useful when you have no prior knowledge about data structure.
• Systematic sampling: Select every k-th row, where k = N / n (population size divided by desired sample size). Faster than random because it avoids sorting; works well when data is not periodically patterned.
• Stratified sampling: Divide the population into subgroups (strata) and sample each subgroup proportionally or at a fixed rate. Essential when subgroups have different characteristics that you want represented.

---

Setting Up a Realistic Test Dataset

Before implementing the samplers, we need something to work with. Let's build a synthetic transaction dataset in M that simulates the characteristics of real data: a dominant product category, a sparse region, and some null values.

let
    RowCount = 500000,
    
    Regions = {"North", "South", "East", "West", "International"},
    // International is intentionally rare — only 2% of rows
    RegionWeights = {0.28, 0.27, 0.27, 0.16, 0.02},
    
    Categories = {"Electronics", "Apparel", "Home & Garden", "Automotive", "Sports"},
    
    WeightedRegion = (seed as number) as text =>
        let
            v = Number.Mod(seed * 1.6180339887, 1),
            cumulative = List.Generate(
                () => [i = 0, cum = 0],
                each [i] < List.Count(RegionWeights),
                each [i = [i] + 1, cum = [cum] + RegionWeights{[i]}],
                each [cum]
            ),
            idx = List.Count(List.Select(cumulative, each _ < v))
        in
            Regions{Number.Min(idx, List.Count(Regions) - 1)},
    
    Rows = List.Transform(
        List.Numbers(1, RowCount),
        (i) => [
            TransactionID   = i,
            Region          = WeightedRegion(i),
            Category        = Categories{Number.Mod(i * 7, List.Count(Categories))},
            Amount          = Number.Round(50 + Number.Mod(i * 3.7, 950), 2),
            Quantity        = Number.Mod(i, 10) + 1,
            CustomerID      = if Number.Mod(i, 20) = 0 then null 
                              else "CUST-" & Text.PadStart(Text.From(Number.Mod(i, 5000)), 5, "0"),
            TransactionDate = Date.AddDays(#date(2022, 1, 1), Number.Mod(i, 730))
        ]
    ),
    
    Result = Table.FromRecords(Rows)
in
    Result

This gives us 500,000 rows with a known structure: a rare "International" region (≈2%), regular null patterns in CustomerID (every 20th row), and a date range spanning two years. This lets us verify our samplers are working correctly by comparing sample statistics against known population values.

---

Simple Random Sampling

The core challenge with random sampling in M is that Number.Random() is volatile — it returns a different value every time it's evaluated, which means you can't use it directly in a Table.SelectRows predicate without getting unpredictable sample sizes. The solution is to generate your random index list once, sort it, then use Table.SelectRows with a row-index lookup.

Here's the approach: add a row index to the table, generate a fixed list of random indices, then filter to rows whose index appears in that list.

let
    SampleRandom = (sourceTable as table, sampleSize as number, optional seed as nullable number) as table =>
        let
            ActualSeed = if seed = null then 42 else seed,
            
            TotalRows = Table.RowCount(sourceTable),
            
            // Clamp sample size to available rows
            EffectiveSample = Number.Min(sampleSize, TotalRows),
            
            // Generate a deterministic pseudo-random index list using a linear
            // congruential generator — avoids Number.Random() volatility
            LCG = List.Generate(
                () => [state = ActualSeed, indices = {}],
                each List.Count([indices]) < EffectiveSample,
                each [
                    state  = Number.Mod([state] * 1664525 + 1013904223, TotalRows),
                    indices = List.Distinct(List.Combine({[indices], {[state]}}))
                ],
                each [indices]
            ),
            
            // The last state contains our full unique index list
            SampleIndices = List.Sort(
                List.Last(LCG),
                Order.Ascending
            ),
            
            // Index the source table so we can filter by position
            Indexed = Table.AddIndexColumn(sourceTable, "__RowIndex__", 0, 1, Int64.Type),
            
            // Keep only rows whose index appears in our sample list
            IndexSet   = List.Buffer(SampleIndices),
            Filtered   = Table.SelectRows(
                Indexed,
                each List.Contains(IndexSet, [__RowIndex__])
            ),
            
            Result = Table.RemoveColumns(Filtered, {"__RowIndex__"})
        in
            Result
in
    SampleRandom

The linear congruential generator (LCG) here is doing important work. Rather than relying on M's volatile Number.Random(), we produce a deterministic sequence from a seed — same seed always produces the same sample. The List.Distinct call inside the generator ensures we never select the same row twice (sampling without replacement).

Notice that List.Generate here accumulates the index list in [indices] rather than just returning the new state. This is necessary because we need uniqueness — the LCG might produce duplicates before the list reaches EffectiveSample distinct values. The generator loop continues until List.Count([indices]) reaches the target, discarding duplicates along the way. This approach is discussed in depth in Advanced M: Iterators, Accumulators, and Recursive Patterns.

---

Systematic Sampling

Systematic sampling is simpler and faster than random sampling for large tables. You choose a starting point and then select every k-th row. No index set construction, no filtering — just arithmetic on row position.

let
    SampleSystematic = (sourceTable as table, sampleSize as number, optional startOffset as nullable number) as table =>
        let
            TotalRows      = Table.RowCount(sourceTable),
            EffectiveSample = Number.Min(sampleSize, TotalRows),
            
            // Step size: how many rows between each selected row
            StepSize       = Number.Floor(TotalRows / EffectiveSample),
            
            // Start offset: defaults to half a step to avoid bias from leading rows
            Offset         = if startOffset = null 
                             then Number.Floor(StepSize / 2) 
                             else Number.Min(startOffset, StepSize - 1),
            
            // Generate the list of indices: Offset, Offset+k, Offset+2k, ...
            SampleIndices  = List.Generate(
                () => Offset,
                each _ < TotalRows,
                each _ + StepSize
            ),
            
            Indexed        = Table.AddIndexColumn(sourceTable, "__RowIndex__", 0, 1, Int64.Type),
            IndexSet       = List.Buffer(SampleIndices),
            
            Filtered       = Table.SelectRows(
                Indexed,
                each List.Contains(IndexSet, [__RowIndex__])
            ),
            
            Result         = Table.RemoveColumns(Filtered, {"__RowIndex__"})
        in
            Result
in
    SampleSystematic

This is significantly faster than the random variant because List.Generate here runs in O(n) with no uniqueness checks or state accumulation. The step size arithmetic is deterministic and cheap.

The startOffset parameter matters more than it looks. If you always start at row 0 and your data is sorted by date, row 0 is January 1st and your systematic sample will always include the first day of every period — which can bias seasonal analysis. Starting at StepSize / 2 puts your first selected row in the middle of the first interval, which is a more neutral default.

---

Stratified Sampling

This is where the real power is. Stratified sampling ensures that every subgroup in your data is represented in proportion to its size in the population. For our transaction dataset, that means "International" gets its 2% share of the sample even though a flat random sample might miss it entirely at small sample sizes.

The algorithm is:

1. Group the table by the stratification column(s).
2. For each group, calculate the number of rows to sample proportionally.
3. Apply random or systematic sampling within each group.
4. Recombine all group samples.

let
    SampleStratified = (
        sourceTable     as table,
        stratifyBy      as list,       // column names to stratify on
        sampleSize      as number,
        optional seed   as nullable number
    ) as table =>
        let
            ActualSeed  = if seed = null then 42 else seed,
            TotalRows   = Table.RowCount(sourceTable),
            
            // Add a row index before grouping so we can track positions
            Indexed     = Table.AddIndexColumn(sourceTable, "__RowIndex__", 0, 1, Int64.Type),
            
            // Group by stratification columns
            Grouped     = Table.Group(
                Indexed,
                stratifyBy,
                {
                    {"__StratumRows__", each _, type table},
                    {"__StratumCount__", Table.RowCount, Int64.Type}
                }
            ),
            
            // Calculate how many rows each stratum contributes to the sample
            WithAllocation = Table.AddColumn(
                Grouped,
                "__AllocatedSample__",
                each Number.Max(1, Number.Round(_ [__StratumCount__] / TotalRows * sampleSize, 0)),
                Int64.Type
            ),
            
            // Apply random sampling within each stratum
            // Vary the seed per stratum to avoid correlated sampling
            WithSamples = Table.AddColumn(
                WithAllocation,
                "__SampledRows__",
                (stratumRow) =>
                    let
                        StratumTable     = stratumRow[__StratumRows__],
                        AllocatedN       = stratumRow[__AllocatedSample__],
                        StratumSeed      = ActualSeed + Table.RowCount(stratumRow[__StratumRows__]),
                        
                        StratumRowCount  = Table.RowCount(StratumTable),
                        EffectiveN       = Number.Min(AllocatedN, StratumRowCount),
                        
                        // LCG within stratum
                        LCG = List.Generate(
                            () => [state = StratumSeed, indices = {}],
                            each List.Count([indices]) < EffectiveN,
                            each [
                                state   = Number.Mod([state] * 1664525 + 1013904223, StratumRowCount),
                                indices = List.Distinct(List.Combine({[indices], {[state]}}))
                            ],
                            each [indices]
                        ),
                        
                        SampleIndices   = List.Sort(List.Last(LCG), Order.Ascending),
                        IndexSet        = List.Buffer(SampleIndices),
                        
                        // StratumTable already has __RowIndex__ from the outer Indexed step,
                        // but we need positional index within the stratum
                        StratumIndexed  = Table.AddIndexColumn(StratumTable, "__StratumPos__", 0, 1, Int64.Type),
                        
                        Filtered        = Table.SelectRows(
                            StratumIndexed,
                            each List.Contains(IndexSet, [__StratumPos__])
                        ),
                        
                        Cleaned         = Table.RemoveColumns(Filtered, {"__StratumPos__"})
                    in
                        Cleaned,
                type table
            ),
            
            // Combine all stratum samples into a single table
            SampleList  = Table.ToRecords(
                Table.SelectColumns(WithSamples, {"__SampledRows__"})
            ),
            
            Combined    = Table.Combine(
                List.Transform(SampleList, each _[__SampledRows__])
            ),
            
            Result      = Table.RemoveColumns(Combined, {"__RowIndex__"})
        in
            Result
in
    SampleStratified

The seed variation per stratum (StratumSeed = ActualSeed + Table.RowCount(...)) is a subtle but important detail. If every stratum uses the same seed and they all happen to be small tables, the LCG produces identical sequences, meaning you always pick the same relative positions across strata. Adding a stratum-specific offset breaks this correlation.

This is a natural place to apply the Advanced Table Operations: Group, Join, and Transform in M Language patterns — Table.Group is doing the heavy lifting here, and understanding its internals helps you debug when stratum counts look wrong.

---

Building the Sampling Dispatcher

Now we have three strategies. Let's build a dispatcher function that selects the right one based on parameters, so callers don't need to know which function to invoke.

let
    SampleTable = (
        sourceTable     as table,
        sampleSize      as number,
        optional method as nullable text,            // "random", "systematic", "stratified"
        optional stratifyBy as nullable list,        // required for stratified
        optional seed   as nullable number
    ) as table =>
        let
            Method = if method = null then "random" else Text.Lower(method),
            
            ValidMethods = {"random", "systematic", "stratified"},
            
            _ = if not List.Contains(ValidMethods, Method)
                then error Error.Record(
                    "InvalidArgument",
                    "method must be one of: random, systematic, stratified. Got: " & Method,
                    [ValidMethods = ValidMethods, Received = Method]
                )
                else null,
            
            // Validate stratified requirements
            __ = if Method = "stratified" and (stratifyBy = null or List.Count(stratifyBy) = 0)
                 then error Error.Record(
                     "MissingArgument",
                     "stratifyBy must be provided and non-empty for stratified sampling",
                     null
                 )
                 else null,
            
            Result = if Method = "random" then
                         SampleRandom(sourceTable, sampleSize, seed)
                     else if Method = "systematic" then
                         SampleSystematic(sourceTable, sampleSize, null)
                     else
                         SampleStratified(sourceTable, stratifyBy, sampleSize, seed)
        in
            Result
in
    SampleTable

This dispatcher pattern is documented thoroughly in Writing Custom M Functions from Scratch in Power Query — the key design principle is that the dispatcher validates inputs eagerly and surfaces meaningful errors before any sampling work begins. See how the _ and __ bindings are used purely for side effects (validation) — they're not consumed downstream. For production use, wrapping these in try-otherwise blocks gives you even more control, as covered in Error Handling and Try-Otherwise Patterns in M: Building Production-Ready Power Query Solutions.

---

Building the Statistical Profiling Pipeline

Now that we can get a representative sample, let's use it. The profiling pipeline takes a sample table and produces a row-per-column profile with key statistics.

let
    ProfileSample = (sampleTable as table) as table =>
        let
            Columns     = Table.ColumnNames(sampleTable),
            TotalRows   = Table.RowCount(sampleTable),
            
            ProfileOneColumn = (colName as text) as record =>
                let
                    Values      = Table.Column(sampleTable, colName),
                    NonNull     = List.Select(Values, each _ <> null),
                    NullCount   = TotalRows - List.Count(NonNull),
                    
                    NullRate    = Number.Round(NullCount / TotalRows, 4),
                    
                    // Cardinality: distinct value count
                    Distinct    = List.Count(List.Distinct(NonNull)),
                    
                    // Type-sensitive statistics
                    FirstNonNull = if List.Count(NonNull) > 0 then NonNull{0} else null,
                    
                    IsNumeric   = try (FirstNonNull is number) otherwise false,
                    IsDate      = try (FirstNonNull is date) otherwise false,
                    
                    NumericValues = if IsNumeric then
                                       List.Transform(NonNull, each _ as number)
                                   else {},
                    
                    SampleMean  = if IsNumeric and List.Count(NumericValues) > 0 then
                                      List.Average(NumericValues)
                                  else null,
                    
                    SampleMin   = if IsNumeric and List.Count(NumericValues) > 0 then
                                      List.Min(NumericValues)
                                  else if IsDate and List.Count(NonNull) > 0 then
                                      List.Min(NonNull)
                                  else null,
                    
                    SampleMax   = if IsNumeric and List.Count(NumericValues) > 0 then
                                      List.Max(NumericValues)
                                  else if IsDate and List.Count(NonNull) > 0 then
                                      List.Max(NonNull)
                                  else null,
                    
                    // Sample standard deviation
                    SampleStdDev = if IsNumeric and List.Count(NumericValues) > 1 then
                                       let
                                           Mean = SampleMean,
                                           Deviations = List.Transform(NumericValues, each (_ - Mean) ^ 2),
                                           Variance   = List.Sum(Deviations) / (List.Count(NumericValues) - 1)
                                       in
                                           Number.Sqrt(Variance)
                                   else null,
                    
                    // Skewness estimate using Pearson's second coefficient: 3*(mean-median)/stddev
                    SampleMedian = if IsNumeric and List.Count(NumericValues) > 0 then
                                       let
                                           Sorted = List.Sort(NumericValues),
                                           n      = List.Count(Sorted),
                                           Mid    = Number.IntegerDivide(n, 2)
                                       in
                                           if Number.Mod(n, 2) = 1 then
                                               Sorted{Mid}
                                           else
                                               (Sorted{Mid - 1} + Sorted{Mid}) / 2
                                   else null,
                    
                    PearsonSkew = if IsNumeric and SampleStdDev <> null and SampleStdDev > 0 then
                                      3 * (SampleMean - SampleMedian) / SampleStdDev
                                  else null,
                    
                    // Top value by frequency
                    FreqTable   = List.Transform(
                        List.Distinct(NonNull),
                        (v) => [Value = v, Count = List.Count(List.Select(NonNull, each _ = v))]
                    ),
                    
                    TopValue    = if List.Count(FreqTable) > 0 then
                                      Record.Field(
                                          List.MaxN(FreqTable, 1, each [Count]){0},
                                          "Value"
                                      )
                                  else null
                in
                    [
                        ColumnName      = colName,
                        SampleRowCount  = TotalRows,
                        NullCount       = NullCount,
                        NullRate        = NullRate,
                        DistinctCount   = Distinct,
                        CardinalityRatio = Number.Round(Distinct / Number.Max(1, List.Count(NonNull)), 4),
                        SampleMin       = Text.From(SampleMin),
                        SampleMax       = Text.From(SampleMax),
                        SampleMean      = SampleMean,
                        SampleMedian    = SampleMedian,
                        SampleStdDev    = SampleStdDev,
                        PearsonSkewness = PearsonSkew,
                        MostFrequentValue = Text.From(TopValue)
                    ],
            
            ProfileRows = List.Transform(Columns, ProfileOneColumn),
            Result      = Table.FromRecords(ProfileRows)
        in
            Result
in
    ProfileSample

The frequency calculation using List.Select is intentionally naive — it's O(n²) on the distinct value count. For high-cardinality columns this will be slow, but on a sample of 10,000–50,000 rows it's perfectly acceptable. If you need to profile columns with millions of distinct values, replace the frequency section with a grouped sort approach.

---

Assembling the Complete Pipeline

Let's put it all together as an end-to-end pipeline. In practice, you'd save each function as a named query in Power Query and reference them by name.

// Master query: SamplingPipeline
let
    // --- Configuration ---
    Source          = TransactionData,     // reference to your data source query
    SampleMethod    = "stratified",
    StratifyColumns = {"Region"},
    TargetSampleN   = 25000,
    RandomSeed      = 99,
    
    // --- Step 1: Buffer the source to avoid multiple evaluations ---
    // See performance section below for when this is appropriate
    Buffered        = Table.Buffer(Source),
    
    // --- Step 2: Draw the sample ---
    Sample          = SampleTable(
                          Buffered,
                          TargetSampleN,
                          SampleMethod,
                          StratifyColumns,
                          RandomSeed
                      ),
    
    // --- Step 3: Profile the sample ---
    Profile         = ProfileSample(Sample),
    
    // --- Step 4: Add metadata columns for traceability ---
    WithMeta        = Table.AddColumn(
                          Table.AddColumn(
                              Table.AddColumn(Profile,
                                  "SamplingMethod",    each SampleMethod,  type text),
                              "StratifyColumns",   each Text.Combine(StratifyColumns, ", "), type text),
                          "SampleFraction",    each Number.Round(TargetSampleN / Table.RowCount(Buffered), 4), type number),
    
    // --- Step 5: Sort for readability ---
    Result          = Table.Sort(WithMeta, {{"NullRate", Order.Descending}})
in
    Result

This pipeline is clean, traceable, and reproducible. The SamplingMethod and SampleFraction metadata columns mean that any consumer of this profile table can see exactly how it was generated — critical for data governance.

---

Performance Architecture: When to Buffer and When Not To

The Table.Buffer call in the pipeline above deserves explanation. Without it, every reference to Source in downstream steps triggers a fresh evaluation. If your source is a database, this means multiple round-trips; if it's an in-memory computed table, it means recomputing from scratch.

For sampling specifically, buffering has another consequence: it materializes the entire table into memory before we sample it. This seems wasteful when the whole point was to avoid loading everything — but it's unavoidable for random and stratified sampling because those methods need to know the total row count and may need to access any row by index.

Systematic sampling is the exception. Because it selects rows at fixed intervals, it can operate on a streaming table without knowing the total row count upfront — though you still need a rough estimate to set your step size. If your source is large enough that buffering is impractical, systematic sampling is your best option, possibly with query folding to push the row numbering to the database.

The trade-off is captured well in M Language Performance Patterns and Anti-Patterns: Optimize Power Query for Speed — the core principle is: buffer exactly once, as late as possible, only when you'll reference the buffered table multiple times.

For the stratified sampler, there's a subtler performance concern: Table.Group followed by per-stratum List.Generate creates a nested evaluation graph. If you have many strata (hundreds of distinct values in stratifyBy), the overhead of creating and sampling each sub-table becomes significant. For high-cardinality stratification, consider limiting to the top N strata or using a binned stratification column.

For more on managing query evaluation dependencies, see Understanding M Language Query Evaluation: Lazy Evaluation, Dependency Graphs, and Step Ordering in Power Query.

---

Verifying Your Sampler: Checking Against Known Population Values

A sampler you can't verify is a sampler you can't trust. With our synthetic dataset, we know the exact population statistics, so let's write a quick validation query.

let
    // Population statistics (computed once and cached)
    PopulationSize      = Table.RowCount(TransactionData),
    
    PopRegionDist       = Table.Group(
                              TransactionData,
                              {"Region"},
                              {{"Count", Table.RowCount, Int64.Type}}
                          ),
    PopRegionPct        = Table.AddColumn(
                              PopRegionDist,
                              "PopPct",
                              each Number.Round([Count] / PopulationSize, 4),
                              type number
                          ),
    
    // Sample statistics
    MySample            = SampleTable(
                              Table.Buffer(TransactionData),
                              25000,
                              "stratified",
                              {"Region"},
                              42
                          ),
    
    SampleSize          = Table.RowCount(MySample),
    
    SampleRegionDist    = Table.Group(
                              MySample,
                              {"Region"},
                              {{"Count", Table.RowCount, Int64.Type}}
                          ),
    
    SampleRegionPct     = Table.AddColumn(
                              SampleRegionDist,
                              "SamplePct",
                              each Number.Round([Count] / SampleSize, 4),
                              type number
                          ),
    
    // Join population and sample distributions
    Comparison          = Table.Join(
                              PopRegionPct, {"Region"},
                              SampleRegionPct, {"Region"},
                              JoinKind.FullOuter
                          ),
    
    WithDelta           = Table.AddColumn(
                              Comparison,
                              "AbsDelta",
                              each Number.Abs([PopPct] - [SamplePct]),
                              type number
                          ),
    
    Result              = Table.Sort(WithDelta, {{"PopPct", Order.Descending}})
in
    Result

For a well-implemented stratified sampler with n=25,000, the AbsDelta for each region should be under 0.005 (half a percentage point). If you see larger deltas on small strata like "International," increase your sample size or switch from proportional to equal-allocation stratification (sample equal N per stratum regardless of stratum size).

---

Hands-On Exercise

Scenario: You work at a logistics company. You have a 2-million-row shipment dataset with columns: ShipmentID, OriginCountry, DestinationCountry, ShipmentClass (Express, Standard, Economy), WeightKg, DelayDays, and CarrierID. Some DelayDays values are null for shipments still in transit.

Tasks:

1. Implement the three sampling functions from this lesson in a new Power BI file. Create each as a separate named query so they're reusable.

2. Draw a stratified sample of 15,000 rows stratified by ShipmentClass. Verify that your sample's class distribution matches the population within 1 percentage point.

3. Run the profiling pipeline on your stratified sample. Identify:

   • Which columns have null rates above 5%
   • Which numeric columns show strong skewness (Pearson coefficient > 1 or < -1)
   • The most frequent OriginCountry and DestinationCountry

4. Compare systematic vs. stratified sampling at n=5,000. Compute the mean WeightKg from each sample and compare to the true population mean. Which method produces a closer estimate? Why do you think that is for this dataset?

5. Extend ProfileSample to also output a 95% confidence interval for SampleMean using the formula SampleMean ± 1.96 * (SampleStdDev / Number.Sqrt(SampleRowCount)). Add MeanLowerBound and MeanUpperBound columns to the profile output.

---

Common Mistakes & Troubleshooting

"My random sample has fewer rows than I asked for." This almost always means the LCG is cycling through duplicates exhaustively before reaching your target count. It happens when sampleSize is a large fraction of TotalRows (say, >40%). At that point, the probability of a new random index being unique drops dramatically, and the generator loop runs for a very long time. For sample fractions above 30–40%, consider inverting the logic: randomly select which rows to exclude instead, since the exclusion set is smaller.

"My stratified sample has wildly unequal stratum sizes." Check your stratifyBy columns for unexpected values. A column you thought had 5 distinct values might have 50 due to trailing spaces, mixed case, or nulls being their own implicit stratum. Run List.Distinct(Table.Column(source, "yourColumn")) and inspect the result before stratifying.

"The pipeline runs fine in Power BI Desktop but times out in the service." The service has stricter memory limits and timeout policies than Desktop. The most common cause is Table.Buffer materializing too much data. If your source is a SQL database, push more work to the database using folding-compatible filtering before buffering. For systematic sampling specifically, you may be able to fold the row-number predicate directly — see Implementing Custom Query Folding Logic in M: Keeping Transformations Native to the Data Source.

"The profile shows all nulls for numeric statistics on a column I know has numbers." Your column's value type is probably any rather than number. The _ is number check in ProfileOneColumn relies on the runtime type of FirstNonNull. If your column contains numbers stored as text (a common import problem), FirstNonNull is number will return false. Add a type-coercion step before profiling, or modify IsNumeric to also try Number.From(_ ) <> null.

"Table.Group with two stratification columns creates a cross-product I didn't expect." When you pass stratifyBy = {"Region", "Category"}, each unique combination of Region × Category becomes a stratum. For 5 regions × 5 categories = 25 strata. If some combinations don't exist in your data, those strata simply don't appear — no problem. But if you have 100 regions and 50 categories, you could get up to 5,000 strata, each getting only a handful of sample rows. Use single-column stratification on the most analytically important grouping, or pre-compute a combined stratification key.

---

Summary & Next Steps

You've built a complete, production-grade sampling and profiling system in pure M. Here's what you have:

• A simple random sampler with deterministic seeding via a linear congruential generator, safe from Number.Random() volatility
• A systematic sampler with configurable offset, faster for large tables where buffering is expensive
• A stratified sampler that guarantees proportional representation across any grouping key, with per-stratum LCG seeding for independence
• A dispatcher function that validates inputs and routes to the right strategy
• A column profiling pipeline that produces null rates, cardinality, mean, median, standard deviation, and Pearson skewness from any sample table
• A validation query pattern for verifying sample fidelity against known population statistics

The natural next step is making these functions part of a reusable library. The pattern for doing that — packaging functions with documentation metadata and version properties — is covered in Building a Reusable Function Library in Power Query. Once you have a function library, you can reference your samplers across dozens of reports without copy-pasting M code.

For the profiling side, there's significant room to extend the ProfileSample function into a full data quality contract engine — adding expected ranges, regex pattern checks, and referential integrity tests. That territory is covered in Implementing Custom Table.Schema Validation and Type Enforcement Pipelines in Power Query M for Robust Data Quality Contracts.

If your datasets are genuinely massive — hundreds of millions of rows in a database — the sampling logic you've learned here pairs naturally with incremental refresh patterns that limit how much data Power Query ever needs to touch. Implementing Incremental Refresh Logic in Power Query M: RangeStart, RangeEnd, and Folding-Compatible Filter Patterns shows how to combine those two approaches.

Statistical sampling is one of the highest-leverage skills a Power Query practitioner can have. You've just moved from "grab the first 10k rows and hope for the best" to "draw a reproducible, stratified sample with defensible statistical properties." That's a meaningful upgrade to the quality of every downstream analysis you produce.