Implementing Custom Locale-Aware Number and Date Parsing Pipelines in Power Query M: Resolving Regional Format Conflicts When Combining Multi-Country Data Sources

Introduction

Here's a scenario that plays out in enterprise data teams constantly: you're building a consolidated sales report pulling data from regional offices in Germany, the United States, Brazil, and Japan. The German office exports CSVs where 1.234,56 means one thousand two hundred thirty-four point five six. The US system uses 1,234.56 for the same value. The Brazilian file uses 01/02/2024 to mean February 1st. The American file uses that same string to mean January 2nd. And somewhere in the Japanese data, dates are formatted as 2024年1月15日. Power Query's default type inference, left to its own devices, will happily mangle every single one of these — silently, without errors, producing numbers and dates that are plausible but completely wrong.

This isn't an edge case. The moment your organization operates across more than one country, or integrates data from any external partner, regional format conflicts become the most insidious category of data quality problem you'll face. Unlike a schema mismatch or a null value, a silently mis-parsed date doesn't throw an error. It just corrupts your analysis, and the corruption often isn't discovered until someone spots a business anomaly weeks later.

By the end of this lesson, you'll know how to build locale-aware parsing pipelines that explicitly handle number and date formats from any country, resolve conflicts when multiple locales appear in a single dataset, construct reusable M function libraries for regional parsing, and design ETL architectures that make locale provenance explicit and auditable.

What you'll learn:

• How Power Query's locale system works under the hood — and where it breaks down
• Explicit locale-aware number parsing using Number.FromText with culture parameters
• Explicit locale-aware date parsing using Date.FromText and DateTime.FromText with culture parameters
• Strategies for detecting locale origin from metadata and routing records through the correct parsing logic
• Building a reusable, parameterized multi-locale parsing library using M functions
• Handling hybrid datasets where multiple locales appear within a single column

---

Prerequisites

You should be comfortable with:

• Writing M code directly in the Advanced Editor (not just using the UI)
• Basic M language concepts: let...in, records, lists, function definitions — covered in Understanding the M Formula Language: Syntax, Data Types, and Expression Basics
• Combining data from multiple sources — see Combining Data from Multiple Sources with Append and Merge Queries in Power Query
• Error handling with try...otherwise — covered in Debugging and Error Handling in M: Building Robust try-otherwise Logic and Diagnostic Workflows in Power Query

---

How Power Query's Locale System Actually Works

Before we can fix locale problems, we need to understand what Power Query is actually doing when it parses text into typed values.

Power Query operates with a concept called the locale context, which is set at the query level. In Power BI Desktop, this defaults to the locale of the machine running the query. In Excel, it typically uses the system regional settings. When you use a UI action like "Change Type," Power Query generates code that looks like this:

= Table.TransformColumnTypes(Source, {{"Revenue", type number}}, "en-US")

That third argument — "en-US" — is the culture string. When Power Query parses "1,234.56" with culture "en-US", it correctly produces 1234.56. When it parses that same string with culture "de-DE", it interprets the comma as a decimal separator and produces 1.234 — wrong by a factor of 1000. If no culture argument is provided, Power Query uses whatever the ambient locale is, making the query's behavior machine-dependent.

The underlying M functions that honor locale arguments fall into two families:

Text-to-value functions (locale-sensitive):

• Number.FromText(text, culture) — parses text as a number using the specified locale's decimal/group separators
• Date.FromText(text, options) — parses text as a date using a specified format or culture
• DateTime.FromText(text, options) — same for datetime values
• DateTimeZone.FromText(text, options) — includes timezone offset
• Decimal.From(value, culture) — converts any type to decimal with locale awareness
• Int64.From(value, culture), Double.From(value, culture) — similar for integers and doubles

Type conversion functions (also locale-sensitive):

• Table.TransformColumnTypes(table, typeList, culture) — bulk column type changes with one culture applied to all specified columns

What's critically missing from that second group is per-column locale control in a single call. Table.TransformColumnTypes applies one culture to all columns being transformed. This matters enormously when your table has a Revenue column from Germany and an OrderDate column from the United States — you can't handle them in a single transform call without splitting the work.

---

Understanding Culture Strings

Culture strings follow the IETF BCP 47 standard: a language code plus an optional region code, separated by a hyphen. The region code is what determines numeric and date formatting, since France and Belgium both speak French but use different formats in some contexts.

Key culture strings you'll encounter in multinational ETL:

Culture	Region	Decimal Sep	Thousands Sep	Date Format
en-US	United States	.	,	M/d/yyyy
en-GB	United Kingdom	.	,	d/M/yyyy
de-DE	Germany	,	.	dd.MM.yyyy
fr-FR	France	,	(narrow space)	dd/MM/yyyy
pt-BR	Brazil	,	.	dd/MM/yyyy
ja-JP	Japan	.	,	yyyy/MM/dd (Gregorian)
zh-CN	China	.	,	yyyy/M/d
es-ES	Spain	,	.	d/M/yyyy
nl-NL	Netherlands	,	.	d-M-yyyy
sv-SE	Sweden	,		yyyy-MM-dd

Note that en-GB and en-US share decimal and thousands separators but swap the day/month positions in short dates — this is the most common source of ambiguous date errors in English-language datasets.

---

Locale-Aware Number Parsing in Detail

Let's start with numbers, since they're slightly simpler than dates. The core function is Number.FromText:

// Basic usage
Number.FromText("1.234,56", "de-DE")   // Returns: 1234.56
Number.FromText("1,234.56", "en-US")   // Returns: 1234.56
Number.FromText("1 234,56", "fr-FR")   // Returns: 1234.56 (space as thousands sep)
Number.FromText("1.234,56", "pt-BR")   // Returns: 1234.56

Where this gets tricky is with values that are ambiguous across locales:

Number.FromText("1,234", "en-US")   // Returns: 1234 (thousands separator)
Number.FromText("1,234", "de-DE")   // Returns: 1.234 — wait, that's also 1.234 as a decimal

Actually, let's be precise: Number.FromText("1,234", "de-DE") returns 1.234 — the number one point two three four — because in German locale, the comma is the decimal separator. So "1,234" in a German file is just the number 1.234, not 1234.

This ambiguity is exactly why locale provenance must be established before parsing, not inferred from the values themselves.

Building a Number Parsing Function

Here's a reusable M function for safe locale-aware number parsing with error handling:

// ParseNumberWithLocale: safely parse a text value as number for a given culture
// Returns null if parsing fails rather than throwing an error
let
    ParseNumberWithLocale = (textValue as nullable text, culture as text) as nullable number =>
        if textValue = null or Text.Trim(textValue) = "" then
            null
        else
            let
                trimmed = Text.Trim(textValue),
                // Remove currency symbols and percentage signs common in exported spreadsheets
                cleaned = Text.Remove(trimmed, {"$", "€", "£", "¥", "R$", "%", " "}),
                result = try Number.FromText(cleaned, culture) otherwise null
            in
                result
in
    ParseNumberWithLocale

The Text.Remove call handles a real-world annoyance: exported spreadsheets routinely include currency symbols and sometimes embed non-breaking spaces as thousands separators (France is a common culprit). Stripping those before parsing prevents silent failures.

Applying Number Parsing Across Columns

For a table where specific columns have known locales:

let
    Source = Csv.Document(File.Contents("C:\Data\germany_sales.csv"), [Delimiter=";", Encoding=65001]),
    PromoteHeaders = Table.PromoteHeaders(Source),
    
    // Parse Revenue column using German locale
    ParseRevenue = Table.TransformColumns(
        PromoteHeaders,
        {
            {"Revenue", each ParseNumberWithLocale(_, "de-DE"), type nullable number},
            {"Quantity", each ParseNumberWithLocale(_, "de-DE"), type nullable number},
            {"Discount", each ParseNumberWithLocale(_, "de-DE"), type nullable number}
        }
    )
in
    ParseRevenue

The key difference from Table.TransformColumnTypes is that Table.TransformColumns applies a function to each cell rather than a type conversion, giving you full control over the parsing logic including culture selection.

---

Locale-Aware Date Parsing in Detail

Dates are more complex than numbers because:

1. There are vastly more date format variants across cultures
2. The same string (like 01/02/03) can plausibly parse into six different dates depending on assumptions
3. Some cultures use calendar systems other than the Gregorian calendar
4. Timestamps add timezone complexity

The primary functions are Date.FromText and DateTime.FromText, both of which accept an options record rather than a plain string since Power Query 2019:

// New options-record syntax (preferred)
Date.FromText("15.03.2024", [Format="dd.MM.yyyy", Culture="de-DE"])
Date.FromText("03/15/2024", [Format="MM/dd/yyyy", Culture="en-US"])
Date.FromText("15/03/2024", [Format="dd/MM/yyyy", Culture="en-GB"])
Date.FromText("2024-03-15", [Format="yyyy-MM-dd", Culture=""])  // ISO 8601 invariant

// DateTime with timezone
DateTime.FromText("15.03.2024 14:30:00", [Format="dd.MM.yyyy HH:mm:ss", Culture="de-DE"])
DateTimeZone.FromText("2024-03-15T14:30:00+01:00", [Format="yyyy-MM-ddTHH:mm:sszzz", Culture=""])

The Format parameter uses .NET composite format strings, which is the same standard used in C# and Excel's TEXT function. Key tokens:

• d / dd — day (1-31) / zero-padded day (01-31)
• M / MM / MMM / MMMM — month number / zero-padded / abbreviated name / full name
• y / yy / yyyy — year (1-2 digits) / 2-digit year / 4-digit year
• H / HH — 24-hour hour / zero-padded
• h / hh — 12-hour hour / zero-padded
• m / mm — minute / zero-padded
• s / ss — second / zero-padded
• tt — AM/PM designator
• zzz — timezone offset as +HH:mm

Handling the Japanese Date Format

Japanese dates exported from ERP systems often appear in formats that mix ASCII and Japanese characters, like 2024年03月15日 or the imperial calendar format 令和6年3月15日 (Reiwa 6, March 15). The Gregorian format is straightforward:

Date.FromText("2024年03月15日", [Format="yyyy年MM月dd日", Culture="ja-JP"])

The imperial calendar is trickier. Power Query doesn't have native imperial era support, so you need a lookup table:

let
    // Japanese imperial era start dates
    EraTable = #table(
        {"Era", "EraStart", "EraOffset"},
        {
            {"令和", #date(2019, 5, 1), 2018},   // Reiwa: year 1 = 2019
            {"平成", #date(1989, 1, 8), 1988},   // Heisei: year 1 = 1989
            {"昭和", #date(1926, 12, 25), 1925},  // Showa: year 1 = 1926
            {"大正", #date(1912, 7, 30), 1911}    // Taisho: year 1 = 1912
        }
    ),
    
    ParseJapaneseImperialDate = (imperialText as text) as nullable date =>
        let
            // Extract era name (first 2 characters for modern eras)
            era = Text.Start(imperialText, 2),
            eraRow = Table.SelectRows(EraTable, each [Era] = era),
            eraOffset = if Table.IsEmpty(eraRow) then null 
                        else Table.First(eraRow)[EraOffset],
            // Extract year number between era name and 年
            yearText = Text.BetweenDelimiters(imperialText, era, "年"),
            imperialYear = try Number.FromText(yearText) otherwise null,
            gregorianYear = if eraOffset = null or imperialYear = null 
                            then null 
                            else eraOffset + imperialYear,
            // Extract month and day
            monthText = Text.BetweenDelimiters(imperialText, "年", "月"),
            dayText = Text.BetweenDelimiters(imperialText, "月", "日"),
            month = try Number.FromText(monthText) otherwise null,
            day = try Number.FromText(dayText) otherwise null,
            result = if gregorianYear = null or month = null or day = null 
                     then null 
                     else try #date(gregorianYear, month, day) otherwise null
        in
            result
in
    ParseJapaneseImperialDate

This kind of function captures institutional knowledge that is otherwise undocumented in your ETL pipeline. When you codify the era offset table, you've made explicit something that would otherwise live only in someone's head — or not at all.

---

The Core Architecture: Source-Tagged, Route-Based Parsing

The most robust approach to multi-locale pipelines is what I call source-tagged routing: every row carries metadata about its locale origin, and the parsing logic routes each row through the appropriate parsing function based on that tag.

This is fundamentally different from trying to detect the locale from the data itself (which is fragile) or applying a single locale to the whole merged dataset (which is wrong).

Step 1: Tag Each Source Before Combining

When you combine files using Combining Multiple Files from a Folder, Power Query adds a Source.Name column. Build on this:

// Query: RawGermanySales
let
    Source = Csv.Document(
        File.Contents("\\FileServer\Sales\DE\germany_q1_2024.csv"),
        [Delimiter=";", Encoding=65001, QuoteStyle=QuoteStyle.Csv]
    ),
    PromoteHeaders = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    AddLocaleTag = Table.AddColumn(PromoteHeaders, "SourceLocale", each "de-DE", type text),
    AddSourceTag = Table.AddColumn(AddLocaleTag, "SourceRegion", each "DE", type text)
in
    AddSourceTag

Do the same for each regional source. The SourceLocale column carries the culture string directly — it's not just documentation, it's data you'll actually use in parsing logic.

Step 2: Combine as Text — Never Auto-Type

This is the most important architectural decision in a multi-locale pipeline: append all sources before any type conversion, and ensure all columns arrive as text.

// Query: CombinedRawSales
let
    Germany = RawGermanySales,
    USA = RawUSASales,
    Brazil = RawBrazilSales,
    Japan = RawJapanSales,
    
    // Append all as-is — all columns are still text at this point
    Combined = Table.Combine({Germany, USA, Brazil, Japan}),
    
    // Confirm key columns are text type (defensive)
    EnforceText = Table.TransformColumnTypes(Combined, {
        {"Revenue", type text},
        {"OrderDate", type text},
        {"Quantity", type text},
        {"SourceLocale", type text}
    })
in
    EnforceText

Step 3: Route-Based Parsing Using SourceLocale

Now apply locale-aware parsing using the SourceLocale column as a routing key:

// Query: ParsedSales
let
    Source = CombinedRawSales,
    
    // Parse Revenue: route by SourceLocale
    ParseRevenue = Table.AddColumn(
        Source,
        "Revenue_Parsed",
        each
            let locale = [SourceLocale]
            in Number.FromText(
                Text.Remove(Text.Trim([Revenue]), {"$", "€", "£", "¥", "R$", " "}),
                locale
            ),
        type nullable number
    ),
    
    // Parse OrderDate: format varies by locale
    ParseDate = Table.AddColumn(
        ParseRevenue,
        "OrderDate_Parsed",
        each
            let
                locale = [SourceLocale],
                rawDate = Text.Trim([OrderDate]),
                parsed = 
                    if locale = "de-DE" then
                        try Date.FromText(rawDate, [Format="dd.MM.yyyy", Culture="de-DE"]) 
                        otherwise null
                    else if locale = "en-US" then
                        try Date.FromText(rawDate, [Format="M/d/yyyy", Culture="en-US"]) 
                        otherwise null
                    else if locale = "pt-BR" then
                        try Date.FromText(rawDate, [Format="dd/MM/yyyy", Culture="pt-BR"]) 
                        otherwise null
                    else if locale = "ja-JP" then
                        try Date.FromText(rawDate, [Format="yyyy/MM/dd", Culture="ja-JP"]) 
                        otherwise null
                    else
                        try Date.FromText(rawDate, [Format="yyyy-MM-dd", Culture=""]) 
                        otherwise null  // ISO 8601 fallback
            in
                parsed,
        type nullable date
    ),
    
    // Remove original text columns and rename parsed ones
    RemoveOriginals = Table.RemoveColumns(ParseDate, {"Revenue", "OrderDate"}),
    RenameColumns = Table.RenameColumns(RemoveOriginals, {
        {"Revenue_Parsed", "Revenue"},
        {"OrderDate_Parsed", "OrderDate"}
    })
in
    RenameColumns

This pattern is explicit, auditable, and extensible. Adding a new country is as simple as adding an else if branch with that country's locale and format.

---

Handling Intra-Column Locale Mixing

The nightmare scenario: a single column in a single file contains dates from multiple locales. This happens when:

• Data was manually entered by staff in different countries using the same form
• A system migration joined records from multiple legacy systems without normalizing formats
• An API returns dates in the user's browser locale, and users are international

You can't use the routing approach here because there's no row-level locale tag. You need a heuristic or a cascade parser.

The Cascade Parser Pattern

// Try multiple format/locale combinations in order of specificity
// Most restrictive formats first to avoid false positives
let
    CascadeParseDateText = (rawDate as nullable text) as nullable date =>
        if rawDate = null or Text.Trim(rawDate) = "" then null
        else
            let
                d = Text.Trim(rawDate),
                
                // ISO 8601 — unambiguous, try first
                iso = try Date.FromText(d, [Format="yyyy-MM-dd", Culture=""]) otherwise null,
                
                // 4-digit year with dot separators — almost certainly German/Dutch/Swedish
                german = if iso <> null then null 
                         else try Date.FromText(d, [Format="dd.MM.yyyy", Culture="de-DE"]) 
                              otherwise null,
                
                // Slash-separated with 4-digit year — need year position to disambiguate
                // yyyy first = ISO-ish (Japanese, Chinese, Swedish ISO variants)
                jpSlash = if german <> null or iso <> null then null
                          else try Date.FromText(d, [Format="yyyy/MM/dd", Culture="ja-JP"])
                               otherwise null,
                
                // For ambiguous MM/dd/yyyy vs dd/MM/yyyy — this is the hard case
                // We attempt US format and flag it, since there's no reliable way to distinguish
                // 01/02/2024 without domain knowledge
                usFormat = if iso <> null or german <> null or jpSlash <> null then null
                           else try Date.FromText(d, [Format="MM/dd/yyyy", Culture="en-US"])
                                otherwise null,
                
                // Final result: first non-null wins
                result = List.First(List.RemoveNulls({iso, german, jpSlash, usFormat}))
            in
                result
in
    CascadeParseDateText

For values where you can heuristically rule out some formats — for example, if you know the data covers only January through December 2024 and you can validate that the parsed date falls within a known range — you can add a validation step:

let
    ValidateDateInRange = (parsedDate as nullable date, minDate as date, maxDate as date) as nullable date =>
        if parsedDate = null then null
        else if parsedDate >= minDate and parsedDate <= maxDate then parsedDate
        else null
in
    ValidateDateInRange

---

Building a Complete Locale Parsing Library

For production use, encapsulate all your locale logic in a dedicated query that acts as a module:

// Query name: LocaleParsingLib
// Returns a record of parsing functions — import this into consuming queries
let
    // ── Number Parsing ──────────────────────────────────────────────────
    
    CleanNumericText = (t as nullable text) as nullable text =>
        if t = null then null
        else Text.Remove(Text.Trim(t), {"$", "€", "£", "¥", "R$", "%", "\u00A0", "\u202F"}),
    
    ParseNumber = (t as nullable text, culture as text) as nullable number =>
        let cleaned = CleanNumericText(t)
        in if cleaned = null or cleaned = "" then null
           else try Number.FromText(cleaned, culture) otherwise null,
    
    // ── Date Parsing ────────────────────────────────────────────────────
    
    // Format registry: maps culture + format-key to format string
    DateFormatRegistry = [
        #"de-DE" = "dd.MM.yyyy",
        #"de-AT" = "dd.MM.yyyy",
        #"de-CH" = "dd.MM.yyyy",
        #"en-US" = "M/d/yyyy",
        #"en-GB" = "d/M/yyyy",
        #"en-AU" = "d/MM/yyyy",
        #"fr-FR" = "dd/MM/yyyy",
        #"fr-BE" = "dd/MM/yyyy",
        #"pt-BR" = "dd/MM/yyyy",
        #"es-ES" = "d/M/yyyy",
        #"es-MX" = "d/MM/yyyy",
        #"nl-NL" = "d-M-yyyy",
        #"ja-JP" = "yyyy/MM/dd",
        #"zh-CN" = "yyyy/M/d",
        #"sv-SE" = "yyyy-MM-dd",
        #"ko-KR" = "yyyy. M. d",
        invariant = "yyyy-MM-dd"
    ],
    
    ParseDate = (t as nullable text, culture as text) as nullable date =>
        let
            cleaned = if t = null then null else Text.Trim(t),
            format = Record.FieldOrDefault(DateFormatRegistry, culture, DateFormatRegistry[invariant])
        in
        if cleaned = null or cleaned = "" then null
        else try Date.FromText(cleaned, [Format=format, Culture=culture]) otherwise null,
    
    ParseDateTime = (t as nullable text, culture as text) as nullable datetime =>
        let
            cleaned = if t = null then null else Text.Trim(t),
            dateFormat = Record.FieldOrDefault(DateFormatRegistry, culture, DateFormatRegistry[invariant]),
            dtFormat = dateFormat & " HH:mm:ss"
        in
        if cleaned = null or cleaned = "" then null
        else try DateTime.FromText(cleaned, [Format=dtFormat, Culture=culture]) otherwise null,
    
    // ── Table-Level Helpers ─────────────────────────────────────────────
    
    // Parse all numeric columns for a given locale
    ParseNumericColumns = (tbl as table, columns as list, culture as text) as table =>
        Table.TransformColumns(
            tbl,
            List.Transform(columns, each {_, (v) => ParseNumber(v, culture), type nullable number})
        ),
    
    // Parse all date columns for a given locale
    ParseDateColumns = (tbl as table, columns as list, culture as text) as table =>
        Table.TransformColumns(
            tbl,
            List.Transform(columns, each {_, (v) => ParseDate(v, culture), type nullable date})
        )

in
    [
        ParseNumber = ParseNumber,
        ParseDate = ParseDate,
        ParseDateTime = ParseDateTime,
        ParseNumericColumns = ParseNumericColumns,
        ParseDateColumns = ParseDateColumns,
        DateFormatRegistry = DateFormatRegistry
    ]

Consuming this library is clean:

let
    Lib = LocaleParsingLib,
    Source = CombinedRawSales,
    
    ParseRevenue = Table.TransformColumns(
        Source,
        {{"Revenue", each Lib[ParseNumber](_, [SourceLocale]), type nullable number}}
    )
in
    ParseRevenue

---

Performance Considerations

Locale-aware parsing at the row level — particularly when done in Table.AddColumn or Table.TransformColumns with complex conditional logic — has real performance implications.

Query folding: Row-by-row M parsing never folds to the data source. If you're pulling from SQL Server or another foldable source, the parsing step will force evaluation in the M engine. This is unavoidable when parsing locale-specific text formats, but it means you should understand query folding well enough to know exactly where your fold boundary is, and structure your query so that filters and row reductions happen before the parsing step.

Column selection before parsing: Only bring the columns you need into the parsing step. Use Table.SelectColumns or Table.RemoveColumns to drop irrelevant columns before the expensive transformation pass.

Table.Buffer before repeated passes: If your combined dataset is large and you're applying multiple transformation steps (number parsing, then date parsing, then further transformations), consider buffering the intermediate result:

let
    Combined = Table.Buffer(CombinedRawSales),  // Materialize once
    ParsedNumbers = ParseNumericColumns(Combined, {"Revenue", "Quantity"}, ...),
    ParsedDates = ParseDateColumns(ParsedNumbers, {"OrderDate", "ShipDate"}, ...)
in
    ParsedDates

Table.Buffer forces full evaluation and caches the result in memory, preventing the M engine from re-evaluating the upstream chain for each subsequent transformation. The tradeoff is memory usage — don't buffer 50 million rows.

---

Hands-On Exercise

In this exercise you'll build a complete locale-aware parsing pipeline for a simulated multinational order dataset.

Setup: Create a new Power BI Desktop file or Excel workbook. In the Advanced Editor, create four blank queries with the following data, using #table to simulate the raw inputs:

// Query: RawDE (German office data)
let
    Source = #table(
        {"OrderID", "CustomerName", "Revenue", "OrderDate", "Quantity"},
        {
            {"DE-001", "Müller GmbH", "1.234,50", "15.01.2024", "10"},
            {"DE-002", "Bauer AG", "98.765,00", "03.02.2024", "250"},
            {"DE-003", "Schreiber KG", "2.100,75", "28.02.2024", "15"},
            {"DE-004", "Hoffmann & Co", null, "01.03.2024", "5"}
        }
    ),
    AddLocale = Table.AddColumn(Source, "SourceLocale", each "de-DE", type text)
in
    AddLocale

// Query: RawUS (US office data)
let
    Source = #table(
        {"OrderID", "CustomerName", "Revenue", "OrderDate", "Quantity"},
        {
            {"US-001", "Acme Corp", "1,234.50", "1/15/2024", "10"},
            {"US-002", "Globex Inc", "98,765.00", "2/3/2024", "250"},
            {"US-003", "Initech LLC", "", "2/28/2024", "15"},
            {"US-004", "Umbrella Co", "2,100.75", "3/1/2024", "5"}
        }
    ),
    AddLocale = Table.AddColumn(Source, "SourceLocale", each "en-US", type text)
in
    AddLocale

// Query: RawBR (Brazil office data)
let
    Source = #table(
        {"OrderID", "CustomerName", "Revenue", "OrderDate", "Quantity"},
        {
            {"BR-001", "Empresa Ltda", "1.234,50", "15/01/2024", "10"},
            {"BR-002", "Comercial SA", "98.765,00", "03/02/2024", "250"},
            {"BR-003", "Distribuidora ME", "2.100,75", "28/02/2024", "15"},
            {"BR-004", "Indústria Eireli", "5.500,00", "01/03/2024", null}
        }
    ),
    AddLocale = Table.AddColumn(Source, "SourceLocale", each "pt-BR", type text)
in
    AddLocale

Your tasks:

1. Create the LocaleParsingLib query from the library code in the previous section.

2. Create a CombinedRaw query that appends RawDE, RawUS, and RawBR using Table.Combine, keeping all columns as text types.

3. Create a ParsedOrders query that:

   • Parses Revenue using Lib[ParseNumber] with the SourceLocale column as the culture argument
   • Parses OrderDate using Lib[ParseDate] with the SourceLocale column as the culture argument
   • Parses Quantity as an integer (use Int64.From with appropriate null handling)
   • Removes the original text columns and replaces them with the parsed versions
   • Adds an IsRevenueNull boolean flag column for QA purposes

4. Verify your results: DE-001, US-001, and BR-001 should all have Revenue = 1234.5 and OrderDate = January 15, 2024 despite having completely different source representations.

5. Bonus challenge: The Brazilian date 03/02/2024 looks identical to the German format 03/02/2024 but means different things if you apply the wrong locale. Confirm that BR-002's OrderDate parses as February 3rd (not March 2nd) when using pt-BR culture, and demonstrate what happens if you accidentally apply de-DE parsing to the Brazilian data.

---

Common Mistakes & Troubleshooting

Mistake 1: Letting Power Query Auto-Detect Types on Import

When you use the Power Query UI to import a CSV, it generates a "Changed Type" step immediately after "Promoted Headers." This step uses your ambient locale — which is correct for local testing but wrong in production or when data has a different locale than your machine.

Fix: Delete the auto-generated "Changed Type" step in the Applied Steps pane. Establish all types explicitly later in your pipeline after locale-aware parsing.

Mistake 2: Using `Table.TransformColumnTypes` with Mixed-Locale Columns

// WRONG: applies en-US to everything, corrupts German numbers
Table.TransformColumnTypes(Combined, {{"Revenue", type number}}, "en-US")

Fix: Use Table.TransformColumns with explicit per-row locale routing as shown in the architecture section.

Mistake 3: Forgetting the `Format` Parameter in `Date.FromText`

// Dangerous: relies on culture's default format, which may not match your actual data
Date.FromText("03/15/2024", [Culture="en-US"])

// Safe: explicit format always wins
Date.FromText("03/15/2024", [Format="MM/dd/yyyy", Culture="en-US"])

Culture alone doesn't guarantee successful parsing if the data format deviates from that culture's default. Always specify the format when you know it.

Mistake 4: French and Swedish Non-Breaking Spaces in Numbers

French numbers use a narrow non-breaking space (Unicode \u202F) as the thousands separator, and regular non-breaking space (\u00A0) appears in some older French documents. Standard Text.Remove handles these only if you include them explicitly:

Text.Remove(t, {" ", "\u00A0", "\u202F"})

If you're seeing Number.FromText failures on French data that looks fine visually, paste the value into a hex viewer or use Text.ToList and check for unexpected Unicode code points.

Mistake 5: Two-Digit Year Ambiguity

Date.FromText("15/01/24", [Format="dd/MM/yy", Culture="en-GB"]) — what year does this produce? In .NET (and therefore M), two-digit years are interpreted relative to a sliding window: years 00-29 map to 2000-2029, years 30-99 map to 1930-1999. So 24 becomes 2024 currently, but this will change over time, and your pipeline assumptions will silently break.

Fix: Always use four-digit year formats (yyyy) in your format strings. If the source data uses two-digit years, add an explicit correction step:

// Correct two-digit years to four-digit before parsing
FixYearFormat = (d as text) as text =>
    let
        parts = Text.Split(d, "/"),
        year = List.Last(parts),
        fullYear = if Text.Length(year) = 2 
                   then Text.PadStart(year, 2, "0")  // This alone isn't enough
                   else year
        // Proper fix: use Number.From and add century
        // yearNum = Number.From(year),
        // correctedYear = if yearNum >= 0 and yearNum < 30 then yearNum + 2000 else yearNum + 1900
    in
        // Reconstruct date string with corrected year
        ...

For production code, write a complete year correction function and document the century assumption explicitly in comments.

Mistake 6: Not Handling Locale Parsing Errors During Development

When you first run a locale-aware pipeline against real data, you will encounter parsing failures. Set up a diagnostic step that isolates failures:

// Add error-surfacing column alongside parsed column during development
DiagnosticsMode = Table.AddColumn(
    Source,
    "Revenue_ParseError",
    each
        let result = try Number.FromText(Text.Trim([Revenue]), [SourceLocale])
        in if result[HasError] then result[Error][Message] else null,
    type nullable text
)

This lets you filter to rows where Revenue_ParseError is not null and examine exactly what strings are failing — invaluable during the initial development phase of a multi-locale pipeline.

---

Summary & Next Steps

You've now built a complete conceptual and practical framework for handling locale conflicts in multi-country data pipelines. The core principles to carry forward:

Never trust ambient locale. Every type conversion in a production pipeline should carry an explicit culture string. The ambient locale is a development convenience, not a production guarantee.

Tag before combining. Add SourceLocale metadata to each regional source before appending. This turns locale management from a guessing game into a data problem.

Combine as text, parse deliberately. The two-phase architecture — raw text ingestion, then typed parsing — is the only approach that scales cleanly to multiple locales.

Encapsulate in a library. The LocaleParsingLib pattern keeps your format registry and parsing functions in one place, making them easy to update when a new regional source joins your pipeline.

Document your assumptions. For ambiguous formats like 01/02/2024, your code must explicitly document which interpretation was chosen and why. This documentation isn't optional — it's part of your data quality story.

For your next steps, consider how these parsing patterns integrate with a more complete ETL architecture. The Building Multi-Stage Staging Architectures in Power Query lesson will help you structure the raw, cleansed, and conformed layers that naturally separate the ingestion, locale normalization, and business logic phases of your pipeline. If you're building this for Power BI Service, understanding how to manage refresh failures and diagnostics becomes critical — locale parsing failures are a common refresh failure mode that needs alerting and monitoring. And if your regional data sources include date dimension lookups, the patterns in Building a Dynamic Date Dimension Table in Power Query Using Pure M Code pair naturally with locale-aware date parsing to build complete, internationalized temporal analytical models.