Implementing Custom Culture-Aware Text Classification and Regex Engine Pipelines in Power Query M: Building Reusable Pattern Libraries for Multilingual Data Standardization
Introduction
Imagine you're inheriting a CRM export from a company that just completed a three-country acquisition. Customer records from France use M. and Mme. as honorifics, German records prefix phone numbers with +49 and format them with periods, while the Brazilian Portuguese data uses CPF numbers (tax IDs formatted as 000.000.000-00) that your downstream pipeline is silently treating as garbage text and dropping. Your job is to classify, standardize, and route each record correctly — without writing a different query for every country.
This is the real problem that culture-aware text classification solves. Power Query's native Text.Contains, Text.StartsWith, and even Text.RegexMatch (available in some environments) get you partway there, but they break down fast when you need reusable, composable logic that works across character sets, cultural formatting conventions, and classification taxonomies that evolve as your organization grows. By the end of this lesson, you'll have built a full pipeline: a pattern library stored as a structured M record, a classification engine that applies patterns in priority order with culture-aware matching, and a standardization layer that normalizes matched text to canonical forms. You'll be able to drop this into any project and extend it in minutes rather than days.
What you'll learn:
- How to architect a reusable pattern library as a typed M record structure, including culture metadata and priority ordering
- How to build a custom regex dispatcher that handles M's limited native regex surface and works around its inconsistencies across Power BI, Excel, and Dataflow environments
- How to implement culture-sensitive pre-processing steps (diacritic normalization, whitespace collapsing, script detection) before classification
- How to chain classification and standardization into a single-pass pipeline using
List.Accumulate and recursive function patterns
- How to test, version, and deploy your pattern library as a shared query for multi-report reuse
Prerequisites
You should be comfortable writing custom M functions — if not, work through Writing Custom M Functions from Scratch in Power Query before continuing. You also need a solid grip on M Language Fundamentals: Syntax, Types, and Expressions for Power Query, especially how records, lists, and function values interact. Familiarity with M's lazy evaluation model (covered in Understanding M Language Query Evaluation: Lazy Evaluation, Dependency Graphs, and Step Ordering in Power Query) will help you reason about when pattern matching steps actually execute.
Understanding M's Regex Surface Area
Before building anything, you need an honest accounting of what M actually gives you for regex work, because it's much thinner than most developers expect.
In Power BI Desktop and Power Query for Excel (as of current releases), there is no general-purpose regex engine exposed through a standard function. What exists is:
Text.RegexMatch(text, regex) — available in some environments (Azure Data Factory, Power Automate, some Dataflows), not in Desktop by defaultText.Contains, Text.StartsWith, Text.EndsWith — culture-aware by default, accepting an optional Comparer argumentText.Select, Text.Remove — character-class selection, not patternsSplitter.SplitTextByRegularExpression — a splitter-type function that does use regex under the hood, available broadly
This creates an interesting constraint: you need to build a regex engine that works within these bounds, using Splitter.SplitTextByRegularExpression as your primary pattern-testing primitive and composing richer logic from M's native text functions.
The trick for using Splitter.SplitTextByRegularExpression as a regex tester is this: when you split a string on a pattern that matches, you get back a list with more than one element (or one empty and one non-empty element). When the pattern does not match, you get back a single-element list containing the original string. This becomes your MatchesRegex function:
// MatchesRegex: returns true if text matches the given regex pattern
MatchesRegex = (text as text, pattern as text) as logical =>
let
// Splitter returns list of parts split on matched regions
Splitter = Splitter.SplitTextByRegularExpression(pattern),
Parts = Splitter(text),
// If any split produced an empty-string boundary OR list length > 1
// that means the pattern matched somewhere in the text
HasMatch = List.Count(Parts) > 1 or
(List.Count(Parts) = 1 and Parts{0} <> text)
in
HasMatch
Warning: Splitter.SplitTextByRegularExpression uses the .NET regex engine when running in Power BI Desktop and SSAS-based environments, but may use a different engine in cloud Dataflows. Always test your patterns in the target environment — particularly lookaheads, lookbehinds, and Unicode property escapes (\p{L}, \p{N}) which have inconsistent support.
There's a subtlety above worth examining: Parts{0} <> text catches the case where your pattern matches the entire string and the splitter returns one empty string plus the remainder. Add some defensive hardening:
MatchesRegex = (text as nullable text, pattern as text) as logical =>
if text = null or Text.Length(text) = 0 then false
else
let
SafeText = text,
Splitter = Splitter.SplitTextByRegularExpression(pattern),
Parts = try Splitter(SafeText) otherwise {SafeText},
HasMatch = List.Count(Parts) > 1
in
HasMatch
The try ... otherwise guard is essential here — malformed regex patterns will throw exceptions that blow up your entire query if left unhandled. We'll build better error handling into the library shortly.
Designing the Pattern Library Architecture
Your pattern library needs to be more than a list of regex strings. It needs to carry context: what culture the pattern applies to, what category it classifies, what priority it has (when multiple patterns could match), and what canonical form matched text should be standardized to. Think of it as a configuration schema.
Here's the record-of-records structure we'll use:
PatternLibrary = [
Patterns = {
[
PatternId = "PHONE_FR",
Category = "PhoneNumber",
Culture = "fr-FR",
Priority = 10,
Regex = "^(\+33|0033|0)[1-9](\d{8})$",
PreProcess = "RemoveWhitespace",
Normalizer = "NormalizePhoneFR",
Description = "French phone number, 10-digit local or +33 international"
],
[
PatternId = "PHONE_DE",
Category = "PhoneNumber",
Culture = "de-DE",
Priority = 10,
Regex = "^(\+49|0049|0)[1-9][0-9]{3,}$",
PreProcess = "RemoveWhitespace",
Normalizer = "NormalizePhoneDE",
Description = "German phone number, local or +49 international"
],
[
PatternId = "TAXID_BR",
Category = "TaxIdentifier",
Culture = "pt-BR",
Priority = 20,
Regex = "^\d{3}\.\d{3}\.\d{3}-\d{2}$",
PreProcess = "TrimOnly",
Normalizer = "NormalizeCPF",
Description = "Brazilian CPF tax identifier"
],
[
PatternId = "TAXID_DE",
Category = "TaxIdentifier",
Culture = "de-DE",
Priority = 20,
Regex = "^\d{11}$",
PreProcess = "RemoveWhitespace",
Normalizer = "NormalizeSteuerID",
Description = "German Steuer-ID, 11 digits"
],
[
PatternId = "POSTAL_FR",
Category = "PostalCode",
Culture = "fr-FR",
Priority = 30,
Regex = "^\d{5}$",
PreProcess = "TrimOnly",
Normalizer = "NormalizePostalFR",
Description = "French 5-digit postal code"
],
[
PatternId = "POSTAL_DE",
Category = "PostalCode",
Culture = "de-DE",
Priority = 30,
Regex = "^\d{5}$",
PreProcess = "TrimOnly",
Normalizer = "NormalizePostalDE",
Description = "German 5-digit postal code"
]
},
Version = "1.3.0",
Owner = "DataEngineering",
Modified = "2024-11-01"
]
Notice that French and German postal codes share the same regex (^\d{5}$). This is intentional — the culture field disambiguates them, and your classifier will use culture context from the record itself before falling back to pattern-only matching. This is a critical design decision: pattern matching without cultural context is ambiguous, and you want the library to make that ambiguity explicit rather than silently wrong.
Key insight: Priority numbers are intentional integers, not an ordering of the list. Lower numbers mean "check this first." When multiple patterns from the same category match a value, the lowest-priority winner is selected. This lets you add highly specific patterns (priority 1) that override broad fallback patterns (priority 99) without restructuring the entire library.
To make this library shareable across reports, store it in a dedicated query named PatternLibrary and reference it from your transformation queries. This is the same configuration-centralization pattern described in Cross-Query State Management and Shared Parameter Tables in Power Query M: Centralizing Configuration for Multi-Report Deployments — the principle maps directly to pattern libraries.
Building the Pre-Processing Layer
Before any regex runs, you need a pre-processing step that normalizes text into a form your patterns can reliably match. Raw user input is messy: phone numbers come in with spaces, dashes, and parentheses; postal codes have leading zeros stripped in some exports; tax IDs might have non-breaking spaces (\u00A0) from web copy-paste. Your patterns will be far simpler and more maintainable if you pre-process rather than try to write regexes that handle every formatting variant.
Build the pre-processor as a dispatcher function keyed to the PreProcess field in each pattern record:
PreProcessText = (text as nullable text, strategy as text) as nullable text =>
if text = null then null
else
let
// Normalize Unicode whitespace variants to standard space first
UnifiedWhitespace = Text.Replace(
Text.Replace(text, Character.FromNumber(160), " "), // NBSP
Character.FromNumber(8239), " " // Narrow NBSP
),
Result = if strategy = "TrimOnly" then
Text.Trim(UnifiedWhitespace)
else if strategy = "RemoveWhitespace" then
Text.Remove(Text.Trim(UnifiedWhitespace), {" ", "-", ".", "(", ")", "/", "\t"})
else if strategy = "NormalizeDiacritics" then
// M has no native diacritic-stripping; approximate with a character map
NormalizeDiacriticsMap(Text.Trim(UnifiedWhitespace))
else if strategy = "CollapseWhitespace" then
// Replace runs of whitespace with single space
Text.Combine(
List.Select(
Text.SplitAny(Text.Trim(UnifiedWhitespace), " " & Character.FromNumber(9)),
each _ <> ""
),
" "
)
else
Text.Trim(UnifiedWhitespace) // Default: trim only
in
Result
The diacritic normalization case needs more attention — M has no built-in Normalize or RemoveDiacritics equivalent. You have two realistic options:
Option A: Character substitution table. Build a record mapping accented characters to their base forms:
DiacriticMap = [
#"à" = "a", #"á" = "a", #"â" = "a", #"ã" = "a", #"ä" = "a", #"å" = "a",
#"è" = "e", #"é" = "e", #"ê" = "e", #"ë" = "e",
#"ì" = "i", #"í" = "i", #"î" = "i", #"ï" = "i",
#"ò" = "o", #"ó" = "o", #"ô" = "o", #"õ" = "o", #"ö" = "o",
#"ù" = "u", #"ú" = "u", #"û" = "u", #"ü" = "u",
#"ç" = "c", #"ñ" = "n", #"ß" = "ss",
#"À" = "A", #"Á" = "A", #"Â" = "A", #"Ã" = "A", #"Ä" = "A",
#"È" = "E", #"É" = "E", #"Ê" = "E", #"Ë" = "E",
#"Ç" = "C", #"Ñ" = "N"
],
NormalizeDiacriticsMap = (text as text) as text =>
List.Accumulate(
Record.FieldNames(DiacriticMap),
text,
(state, charKey) => Text.Replace(state, charKey, Record.Field(DiacriticMap, charKey))
)
Option B: Use Text.Lower + pattern tolerance. For classification (not standardization), write patterns that accept both accented and non-accented versions: [eéèêë] instead of e. This keeps your diacritic handling inside the regex, which is maintainable for small character sets but unwieldy for broader multilingual coverage.
Tip: For German data specifically, don't strip umlauts — ü, ö, ä, ß are semantically significant and collapsing them creates incorrect names (Müller ≠ Muller). Reserve NormalizeDiacritics for contexts like address matching where phonetic equivalence matters, not name classification.
Building the Classification Engine
Now for the heart of the pipeline: a function that takes a raw text value, optionally a culture hint, and the pattern library, and returns the best classification match.
ClassifyText = (
rawText as nullable text,
cultureHint as nullable text, // e.g. "fr-FR", null means try all cultures
library as record
) as record =>
let
Patterns = library[Patterns],
// Filter to culture-relevant patterns if hint provided
CandidatePatterns = if cultureHint = null then
Patterns
else
List.Select(Patterns, each _[Culture] = cultureHint or _[Culture] = "*"),
// Sort by priority ascending (lower number = higher priority)
SortedPatterns = List.Sort(CandidatePatterns, (a, b) => Value.Compare(a[Priority], b[Priority])),
// Pre-process and test each pattern; collect all matches
Matches = List.Select(
List.Transform(
SortedPatterns,
(pattern) =>
let
Processed = PreProcessText(rawText, pattern[PreProcess]),
IsMatch = if Processed = null then false
else MatchesRegex(Processed, pattern[Regex])
in
if IsMatch then
[
PatternId = pattern[PatternId],
Category = pattern[Category],
Culture = pattern[Culture],
Priority = pattern[Priority],
Normalizer = pattern[Normalizer],
Matched = true,
ProcessedText = Processed
]
else
null
),
each _ <> null
),
// Take highest-priority match (first in sorted list that matched)
BestMatch = if List.IsEmpty(Matches) then
[
PatternId = null,
Category = "Unclassified",
Culture = null,
Priority = null,
Normalizer = null,
Matched = false,
ProcessedText = rawText
]
else
Matches{0}
in
BestMatch
This function returns a record, not a scalar. That's intentional — you get the category, the pattern that matched, the culture, and the pre-processed text all in one pass. You'll expand these fields onto your table in the next step. Returning a record from a classification function is a pattern worth internalizing; it avoids running the same pre-processing multiple times and makes debugging vastly easier since you can inspect intermediate results per row.
Warning: The List.Transform inside ClassifyText applies every pattern to every row. If your library has 50 patterns and your table has 500,000 rows, you're running 25 million regex evaluations. This is where M Language Performance Patterns and Anti-Patterns: Optimize Power Query for Speed becomes critical — always pass a cultureHint when you have one, and structure your pattern list with the most-common/most-specific patterns first.
Applying Classification to a Table
With ClassifyText defined, apply it across your customer table using Table.AddColumn and then expand the result record:
let
Source = CustomerData,
// Step 1: Classify each ContactValue field
WithClassification = Table.AddColumn(
Source,
"Classification",
(row) => ClassifyText(
row[ContactValue],
row[SourceCountryCode], // e.g. "fr-FR" from a country column
PatternLibrary
),
type record
),
// Step 2: Expand classification record to columns
Expanded = Table.ExpandRecordColumn(
WithClassification,
"Classification",
{"PatternId", "Category", "Culture", "Priority", "Normalizer", "Matched", "ProcessedText"},
{"Cls_PatternId", "Cls_Category", "Cls_Culture", "Cls_Priority", "Cls_Normalizer", "Cls_Matched", "Cls_ProcessedText"}
)
in
Expanded
At this point you have a table where every row knows its classified category, the specific pattern that matched, and the pre-processed text ready for normalization. The Cls_Normalizer column is a string name that your standardization layer will dispatch on.
Building the Standardization Layer
Classification tells you what a value is. Standardization tells you what it should look like. This is where culture-awareness gets genuinely interesting, because the canonical form differs by country: French phone numbers in international format are +33 1 23 45 67 89 (with spaces), German numbers are +49 30 12345678 (no interior spaces), and Brazilian CPFs should always be 000.000.000-00.
Build the standardization layer as a dispatcher that routes to format-specific functions:
StandardizeText = (processedText as nullable text, normalizerName as nullable text) as nullable text =>
if processedText = null or normalizerName = null then processedText
else if normalizerName = "NormalizePhoneFR" then NormalizePhoneFR(processedText)
else if normalizerName = "NormalizePhoneDE" then NormalizePhoneDE(processedText)
else if normalizerName = "NormalizeCPF" then NormalizeCPF(processedText)
else if normalizerName = "NormalizeSteuerID" then NormalizeSteuerID(processedText)
else if normalizerName = "NormalizePostalFR" then Text.PadStart(processedText, 5, "0")
else if normalizerName = "NormalizePostalDE" then Text.PadStart(processedText, 5, "0")
else processedText // passthrough for unknown normalizers
Now implement the individual normalizers. Let's look at CPF normalization as the most instructive example, because the input after RemoveWhitespace pre-processing could be either 12345678901 (digits only) or 123.456.789-01 (already formatted). Your normalizer should handle both:
NormalizeCPF = (text as text) as text =>
let
// Strip all non-digit characters first
DigitsOnly = Text.Select(text, {"0".."9"}),
Padded = Text.PadStart(DigitsOnly, 11, "0"),
// Reconstruct in canonical form: 000.000.000-00
Part1 = Text.Start(Padded, 3),
Part2 = Text.Middle(Padded, 3, 3),
Part3 = Text.Middle(Padded, 6, 3),
Check = Text.End(Padded, 2),
Canonical = Part1 & "." & Part2 & "." & Part3 & "-" & Check
in
Canonical,
NormalizePhoneFR = (text as text) as text =>
let
// text is already whitespace/dash-free from RemoveWhitespace pre-processing
// Convert local 0X format to +33 X format
Normalized = if Text.StartsWith(text, "+33") then
// Already international, reformat with spaces
let
Digits = Text.Select(text, {"0".."9", "+"}),
// +33 then pairs: +33 1 23 45 67 89
Prefix = "+33 ",
Body = Text.Middle(Digits, 3, Text.Length(Digits) - 3),
Pairs = List.Transform(
{0..((Text.Length(Body) - 1) / 2)},
(i) => Text.Middle(Body, i * 2, 2)
)
in
Prefix & Text.Combine(List.Select(Pairs, each Text.Length(_) > 0), " ")
else if Text.StartsWith(text, "0033") then
NormalizePhoneFR("+33" & Text.Middle(text, 4, Text.Length(text) - 4))
else if Text.StartsWith(text, "0") then
// Local format: 0X -> +33 X
NormalizePhoneFR("+33" & Text.Middle(text, 1, Text.Length(text) - 1))
else
text // Can't normalize, return as-is
in
Normalized
Tip: Notice that NormalizePhoneFR calls itself recursively to normalize the 0033 prefix case — it converts to +33 format and then re-runs normalization. M supports this pattern cleanly, but you need to ensure your recursive calls have a guaranteed base case. The else text final branch is that base case. For deeper recursive patterns, see Advanced M: Iterators, Accumulators, and Recursive Patterns.
Wiring Classification and Standardization Together
Add the standardization step to your table transformation:
let
Source = CustomerData,
WithClassification = Table.AddColumn(
Source, "Classification",
(row) => ClassifyText(row[ContactValue], row[SourceCountryCode], PatternLibrary),
type record
),
Expanded = Table.ExpandRecordColumn(
WithClassification, "Classification",
{"PatternId", "Category", "Culture", "Matched", "Normalizer", "ProcessedText"},
{"Cls_PatternId", "Cls_Category", "Cls_Culture", "Cls_Matched", "Cls_Normalizer", "Cls_ProcessedText"}
),
// Step 3: Standardize matched values
WithStandardized = Table.AddColumn(
Expanded,
"StandardizedValue",
(row) => if row[Cls_Matched] then
StandardizeText(row[Cls_ProcessedText], row[Cls_Normalizer])
else
row[ContactValue], // Leave unclassified values as-is
type nullable text
),
// Step 4: Add a quality flag for downstream use
WithQuality = Table.AddColumn(
WithStandardized,
"DataQualityStatus",
(row) => if not row[Cls_Matched] then "Unclassified"
else if row[StandardizedValue] = null then "ClassifiedNoStandard"
else "Valid",
type text
)
in
WithQuality
This gives you a production-ready classification and standardization pipeline. The DataQualityStatus column is your signal to downstream consumers about how much to trust each value — a practice that aligns with the schema validation patterns described in Implementing Custom Table.Schema Validation and Type Enforcement Pipelines in Power Query M for Robust Data Quality Contracts.
Advanced Pattern: Multi-Category Classification with Confidence Scoring
Sometimes a value matches patterns from multiple categories with the same priority, or you want to return a confidence-ranked list rather than a single winner. This is particularly useful for building data quality dashboards where you want to see how ambiguous your data is.
Extend the classifier to return all matches with scores:
ClassifyTextMulti = (rawText as nullable text, cultureHint as nullable text, library as record) as list =>
let
Patterns = library[Patterns],
Candidates = if cultureHint = null then Patterns
else List.Select(Patterns, each _[Culture] = cultureHint or _[Culture] = "*"),
// Score = 100 - Priority (higher priority patterns score higher)
// Plus a bonus for culture match vs. wildcard
ScoredMatches = List.Select(
List.Transform(
Candidates,
(pattern) =>
let
Processed = PreProcessText(rawText, pattern[PreProcess]),
IsMatch = if Processed = null then false
else MatchesRegex(Processed, pattern[Regex]),
CultureBonus = if pattern[Culture] = cultureHint then 10 else 0,
Score = if IsMatch then (100 - pattern[Priority]) + CultureBonus else -1
in
if IsMatch then
[
PatternId = pattern[PatternId],
Category = pattern[Category],
Score = Score,
Culture = pattern[Culture]
]
else null
),
each _ <> null
),
Ranked = List.Sort(ScoredMatches, (a, b) => Value.Compare(b[Score], a[Score]))
in
Ranked
This returns a ranked list of [PatternId, Category, Score, Culture] records. Add it to your table, and you can pivot on the top-2 matches to surface ambiguous rows for manual review.
Script Detection: Handling Unknown-Culture Data
One of the most powerful additions to your pipeline is a script detection step that runs before culture-scoped classification. When you receive data without a reliable culture field, you can use character range analysis to narrow down which script (Latin, Cyrillic, Arabic, CJK, etc.) the value is written in, and use that to scope your pattern search.
DetectScript = (text as nullable text) as text =>
if text = null or Text.Length(text) = 0 then "Unknown"
else
let
Codes = List.Transform(
Text.ToList(Text.Upper(Text.Select(text, {"A".."Z","À".."Ö","Ø".."ö","ø".."ÿ"}))),
Character.ToNumber
),
// Heuristic: if more than half the alphabetic chars are in Latin range, call it Latin
LatinCount = List.Count(List.Select(Codes, each _ >= 65 and _ <= 122)),
ExtLatinCount = List.Count(List.Select(Codes, each _ >= 192 and _ <= 687)),
TotalAlpha = List.Count(Codes),
// Check for digit-dominant strings (likely numeric IDs or postal codes)
DigitCount = List.Count(Text.ToList(Text.Select(text, {"0".."9"}))),
TotalLen = Text.Length(text),
Result = if TotalLen = 0 then "Unknown"
else if DigitCount / TotalLen > 0.8 then "Numeric"
else if TotalAlpha = 0 then "NonAlpha"
else if (LatinCount + ExtLatinCount) / TotalAlpha > 0.6 then "Latin"
else "Other"
in
Result
Use this to pre-filter your pattern candidates:
// In ClassifyText, extend the candidate filter:
CandidatePatterns =
let
Script = DetectScript(rawText),
// Latin script: try culture hint first, then all Latin-culture patterns
ByScript = if Script = "Numeric" then
List.Select(Patterns, each _[Category] = "TaxIdentifier" or _[Category] = "PostalCode" or _[Category] = "PhoneNumber")
else if Script = "Latin" then
Patterns // All current patterns are Latin-script
else
Patterns // Fallback: try everything
in
if cultureHint = null then ByScript
else List.Select(ByScript, each _[Culture] = cultureHint or _[Culture] = "*")
This is a simple heuristic that becomes powerful as you add patterns for Cyrillic (Russian phone numbers), Arabic (Saudi postal codes), or CJK scripts (Japanese zip codes). The script detector narrows the candidate set without requiring explicit culture metadata on every record.
Packaging the Library as a Shared Query
All of this logic is only as good as your ability to reuse it without copy-pasting across reports. Structure your shared query file with clear separation between the library definition, the helper functions, and the dispatcher:
Create a single query named TextClassificationEngine that returns a record containing all exported functions:
let
// ── Internal helpers ──────────────────────────────────────────
_MatchesRegex = (text as nullable text, pattern as text) as logical => ...,
_PreProcessText = (text as nullable text, strategy as text) as nullable text => ...,
_DetectScript = (text as nullable text) as text => ...,
// ── Normalizers ───────────────────────────────────────────────
_NormalizeCPF = (text as text) as text => ...,
_NormalizePhoneFR = (text as text) as text => ...,
_NormalizePhoneDE = (text as text) as text => ...,
_NormalizeSteuerID = (text as text) as text => ...,
_StandardizeText = (processedText as nullable text, normalizerName as nullable text) as nullable text => ...,
// ── Public API ────────────────────────────────────────────────
Classify = (rawText as nullable text, cultureHint as nullable text, library as record) as record =>
ClassifyText(rawText, cultureHint, library),
Standardize = (processedText as nullable text, normalizerName as nullable text) as nullable text =>
_StandardizeText(processedText, normalizerName),
ClassifyAndStandardize = (rawText as nullable text, cultureHint as nullable text, library as record) as record =>
let
Cls = Classify(rawText, cultureHint, library),
Std = if Cls[Matched] then Standardize(Cls[ProcessedText], Cls[Normalizer]) else rawText
in
Cls & [StandardizedValue = Std]
in
[
Classify = Classify,
Standardize = Standardize,
ClassifyAndStandardize = ClassifyAndStandardize
]
In consuming queries, reference the engine like this:
let
Engine = TextClassificationEngine,
Source = CustomerData,
Result = Table.AddColumn(
Source,
"ClassificationResult",
(row) => Engine[ClassifyAndStandardize](row[ContactValue], row[CultureCode], PatternLibrary),
type record
)
in
Result
This is the building a reusable function library in Power Query pattern applied at the engine level — your consumers never need to know how the internals work. They call ClassifyAndStandardize and expand the result record.
Note: When you reference TextClassificationEngine from multiple queries in the same PBIX, Power Query's dependency resolution will evaluate it once and share the result. This is true for records and scalars, but verify that function values are shared correctly in your version of Power BI Desktop — behavior here has changed across releases. See the lazy evaluation discussion in Understanding M Language Query Evaluation for a deeper treatment.
Versioning and Extending the Pattern Library
As your organization adds more source systems and countries, you need a strategy for versioning pattern library updates without breaking existing queries. The Version field in your library record is a start, but you need a systematic evolution approach.
Backward-compatible additions: Adding new patterns to the list never breaks existing behavior, as long as new patterns have lower priority (higher priority numbers) than existing ones. Existing matches still win.
Breaking changes: Changing a regex that previously matched a value (tightening it) will reclassify previously-valid rows. Always version-bump the library record, and consider storing both old and new libraries in your PatternLibraries query:
PatternLibraries = [
v1_2 = [
Patterns = { /* old patterns */ },
Version = "1.2.0"
],
v1_3 = [
Patterns = { /* new patterns */ },
Version = "1.3.0"
],
Current = "v1_3" // Switch here to upgrade everything at once
]
Consuming queries reference PatternLibraries[PatternLibraries[Current]] — change Current and every downstream query adopts the new library on the next refresh. This is the same centralized parameter table discipline that makes multi-report deployments maintainable, as discussed in Cross-Query State Management and Shared Parameter Tables in Power Query M: Centralizing Configuration for Multi-Report Deployments.
Hands-On Exercise
Build a complete classification pipeline for an e-commerce data set with customer contact records from three regions. Here's your starting data structure (create this as a table in Power Query):
SampleData = Table.FromRecords({
[ContactId = 1, ContactValue = "06 12 34 56 78", SourceCountry = "fr-FR", ContactType = "Unknown"],
[ContactId = 2, ContactValue = "123.456.789-01", SourceCountry = "pt-BR", ContactType = "Unknown"],
[ContactId = 3, ContactValue = "+49 030 12345678", SourceCountry = "de-DE", ContactType = "Unknown"],
[ContactId = 4, ContactValue = "75008", SourceCountry = "fr-FR", ContactType = "Unknown"],
[ContactId = 5, ContactValue = "12345678901", SourceCountry = "pt-BR", ContactType = "Unknown"],
[ContactId = 6, ContactValue = "NOT A NUMBER", SourceCountry = "fr-FR", ContactType = "Unknown"],
[ContactId = 7, ContactValue = null, SourceCountry = "de-DE", ContactType = "Unknown"],
[ContactId = 8, ContactValue = "0033142345678", SourceCountry = "fr-FR", ContactType = "Unknown"]
})
Your tasks:
- Implement
MatchesRegex, PreProcessText, and ClassifyText as separate queries
- Build a
PatternLibrary query with at minimum: French phones, German phones, Brazilian CPF, French postal codes, and German postal codes
- Apply
ClassifyText to the SampleData table and expand the result
- Add
StandardizeText with at least NormalizeCPF and NormalizePhoneFR normalizers
- Add the
DataQualityStatus column and verify that record 6 (bad data) and record 7 (null) are flagged as Unclassified
- Stretch goal: Add a
DetectScript step and test that records with null SourceCountry can still be classified correctly for the Numeric script case
Expected final output columns: ContactId, ContactValue, SourceCountry, Cls_Category, Cls_Culture, Cls_Matched, StandardizedValue, DataQualityStatus
Common Mistakes & Troubleshooting
Mistake 1: Regex anchors not matching because pre-processing changes length
Your regex ^\d{11}$ requires exactly 11 digits. If RemoveWhitespace strips punctuation from 123-456-789-01 and leaves 12345678901 (11 digits), the match works. But if your data has a trailing newline that Text.Trim doesn't remove (some CSV exports include \r characters), the $ anchor fails. Fix: add Character.FromNumber(13) (carriage return) to your Text.Remove character list in the RemoveWhitespace strategy.
Mistake 2: Assuming Splitter.SplitTextByRegularExpression is available everywhere
In some on-premises gateway versions and older Power Query for Excel installations, this function behaves differently or raises errors on certain Unicode patterns. Add a try/otherwise wrapper around every regex call, and log failures to a separate quality table rather than silently failing.
Mistake 3: Priority ordering not working because patterns aren't sorted
If you add patterns to your library list in arbitrary order and forget that ClassifyText needs them sorted by Priority, the first matching pattern wins rather than the highest-priority one. Always include the sort step inside ClassifyText, not outside it — never assume callers will pre-sort.
Mistake 4: Culture hint mismatch on ambiguous patterns
French and German postal codes both match ^\d{5}$. If your source data has SourceCountry = "fr-FR" but the value is actually a German postal code (because the customer moved), you'll classify it as French. This is a data quality problem, not a pipeline bug — surface it with a separate validation step that cross-checks postal codes against known ranges by country.
Mistake 5: Normalizer functions not handling edge cases
NormalizePhoneFR called with a 7-digit local number (malformed input) will produce a malformed +33 result. Every normalizer should include a length/format check and return null or the original value when the input doesn't meet minimum requirements — never produce a canonically-formatted but semantically wrong output. Validate after normalizing, not before.
Warning: A common performance trap is calling ClassifyAndStandardize inside a Table.TransformColumns expression that also triggers other column operations. Each column transformation in Table.TransformColumns shares one evaluation pass, but if you call a function that references the same row multiple times via different column expressions, Power Query may re-evaluate the source row. Consolidate all classification work into a single Table.AddColumn that returns a record, then expand — this is provably more efficient for expensive row-level functions.
Summary & Next Steps
You've built a full culture-aware text classification and standardization pipeline from first principles. The key architectural decisions that make this system robust and maintainable are:
- Pattern library as a first-class data structure — not hardcoded conditionals, but a typed record you can inspect, version, and extend without touching transformation logic
- Pre-processing as a named strategy — text normalization happens before regex matching, and each pattern declares what normalization it needs
- Classification returns a record — gives you all context in one pass, enabling both standardization and quality flagging without re-evaluating patterns
- Priority-ordered matching with culture scoping — handles ambiguity explicitly rather than letting it produce silent errors
- Centralizing the engine as a shared query — makes updates propagate across reports automatically
From here, there are several powerful directions to explore. You can extend the pattern matching approach to handle fuzzy name classification — the techniques in Implementing Custom Fuzzy Matching and Record Linkage Algorithms in Power Query M: Beyond Native Fuzzy Join give you the tools to match company names and addresses that don't conform to rigid patterns. For classification results that feed downstream currency or numeric processing, Implementing Custom Number Formatting and Currency Conversion Pipelines in Power Query M: Locale-Aware Parsing, Rounding Strategies, and Multi-Currency Normalization covers the complementary numeric domain. And if your pipeline needs to evaluate classification rules that are themselves stored as data (rather than compiled M code), Building a Custom M Language Parser and Tokenizer for Dynamic Expression Evaluation in Power Query takes the idea of a data-driven engine to its logical conclusion.
The broader principle here is that M is capable of far more structured, maintainable logic than most practitioners use it for. When you stop thinking of Power Query as a "click to transform" tool and start treating it as a functional programming environment with a well-defined type system, the complexity you can handle — and the reliability you can deliver — grows dramatically.
