Implementing Cross-Source Currency and Unit Normalization Pipelines in Power Query M: Mapping Heterogeneous Measurement Systems to a Single Canonical Schema Across Multi-Origin Datasets

Introduction

Picture this: you've just inherited a global operations dataset. Sales figures from your North American ERP come in USD. Revenue from your European subsidiary arrives in EUR with commas as decimal separators. Your APAC distribution partner sends flat files with amounts in JPY, SGD, and AUD depending on the market — often in the same file, sometimes with the currency code embedded in the value string itself ("$1,250.00", "¥145,000"). And the weight measurements? Your US warehouse uses pounds, your German plant uses kilograms, your UK logistics partner uses stones for some reason that nobody can explain, and your Indian manufacturing facility sends dimensions in centimeters while your CAD files reference millimeters.

This isn't a hypothetical. It's Tuesday morning for most enterprise data teams. The naive approach — manually reformatting each source and hardcoding conversion factors — works until the third source arrives, at which point it becomes a maintenance nightmare. When the EUR/USD rate changes (which is always), you're hunting through a dozen query steps to update a constant buried three levels deep. When a new supplier comes onboard with a new currency, you're refactoring rather than extending.

By the end of this lesson, you'll have built a production-grade normalization pipeline that handles currency and unit conversion as a first-class architectural concern: lookup-table driven, parameterized, testable, and extensible. We'll go from heterogeneous raw sources to a single canonical output schema without hardcoding a single conversion factor in the transformation logic itself.

What you'll learn:

• How to design a canonical schema for multi-origin measurement data and why schema design decisions made here propagate downstream
• How to build lookup-table-driven conversion maps in M and apply them via merge rather than nested if/else logic
• How to implement currency normalization with live or periodically refreshed exchange rate feeds, including handling historical rates vs. spot rates
• How to create a reusable unit conversion function library that handles ambiguous or missing unit identifiers gracefully
• How to architect the pipeline across multiple query stages so each layer has a clear, testable responsibility

---

Prerequisites

This is an expert-level lesson. You should already be comfortable with:

• Writing M code directly in the Advanced Editor, not just using the GUI steps
• Understanding how Table.NestedJoin, Table.AddColumn, and List.Contains work
• The difference between query folding and in-engine evaluation — if that distinction is fuzzy, read Power Query Performance: Master Folding, Buffering & Optimization Techniques first
• Using try...otherwise patterns for defensive coding — covered in Debugging and Error Handling in M: Building Robust try-otherwise Logic and Diagnostic Workflows in Power Query
• Basic understanding of M's functional model and how each works as a lambda shorthand, covered in Understanding the M Formula Language: Syntax, Data Types, and Expression Basics

---

Designing the Canonical Schema First

The single most important decision you'll make in this pipeline isn't a code choice — it's a schema choice. Before you touch a single data source, you need to define what "normalized" means for your organization. Every downstream report, every DAX measure, every SQL view will depend on these column names and semantics. Get this wrong and you'll be patching it across twenty places later.

A robust canonical schema for mixed-currency, mixed-unit data typically looks like this:

Column	Type	Description
SourceSystem	Text	Origin identifier (e.g., "SAP-EU", "Oracle-NA")
TransactionDate	Date	Normalized date, UTC where applicable
Amount_Raw	Number	Original amount as received
Currency_Raw	Text	Original currency code (ISO 4217)
Amount_USD	Number	Converted to USD at applicable rate
ExchangeRate_Used	Number	The rate applied (for audit trail)
ExchangeRate_Date	Date	Date of the rate used
Quantity_Raw	Number	Original quantity
Unit_Raw	Text	Original unit string (may be "lbs", "LBS", "pounds", "lb")
Quantity_Canonical	Number	Converted to canonical unit (e.g., kg)
Unit_Canonical	Text	Always the same canonical unit identifier

Notice the deliberate pattern: always keep raw values alongside derived values. This gives you an audit trail and makes it possible to re-derive the canonical value if your conversion factors change. Never overwrite raw data in a normalization pipeline — you'll regret it during the first audit.

---

Building Your Conversion Lookup Tables

The architectural pivot point of this entire pipeline is moving conversion logic out of M expressions and into lookup tables. Instead of writing:

if [Currency] = "EUR" then [Amount] * 1.0842
else if [Currency] = "GBP" then [Amount] * 1.2654
else if [Currency] = "JPY" then [Amount] * 0.00673
// ... 40 more lines ...

...you maintain a table where the conversion factors live, and your M code simply does a lookup. This means adding a new currency requires adding a row to a table, not editing M code. And when rates change, you update one place.

The Currency Rates Table

Your currency rates table should come from an authoritative source. The options in ascending order of sophistication are:

1. A manually maintained Excel table — fine for static or slow-moving rates, terrible for live reporting
2. A scheduled data flow or SharePoint list that a finance analyst updates weekly
3. A live API feed — the most powerful option, which we'll cover in the section on live rates

For now, let's define the structure. In Power Query, create a query called CurrencyRates:

let
    // In production this would come from an Excel table, SharePoint list, or API
    // We define it here for illustration — replace Source with your actual connection
    Source = Table.FromRows(
        {
            {"EUR", "USD", 1.0842, #date(2024, 11, 1)},
            {"GBP", "USD", 1.2654, #date(2024, 11, 1)},
            {"JPY", "USD", 0.006730, #date(2024, 11, 1)},
            {"AUD", "USD", 0.6521, #date(2024, 11, 1)},
            {"SGD", "USD", 0.7448, #date(2024, 11, 1)},
            {"CAD", "USD", 0.7312, #date(2024, 11, 1)},
            {"INR", "USD", 0.01198, #date(2024, 11, 1)},
            {"USD", "USD", 1.0000, #date(2024, 11, 1)}  // Identity row — critical
        },
        {"FromCurrency", "ToCurrency", "Rate", "RateDate"}
    ),
    TypedTable = Table.TransformColumnTypes(Source, {
        {"FromCurrency", type text},
        {"ToCurrency", type text},
        {"Rate", type number},
        {"RateDate", type date}
    })
in
    TypedTable

Note the identity row for USD → USD at a rate of 1.0. This matters. When you do a left join to pull rates, any USD-denominated row needs to find a match, or you'll get nulls where you least expect them.

The Unit Conversion Table

Units are messier than currencies because the same physical unit has dozens of textual representations in the wild. Your lookup table needs to handle the normalization of unit strings before applying conversion factors. This is a two-stage problem:

1. Map raw unit strings → canonical unit codes (e.g., "lbs", "LB", "pound", "pounds" → "LB")
2. Convert canonical unit codes → target unit (e.g., "LB" → "KG" at factor 0.453592)

Create a query called UnitAliasMap:

let
    Source = Table.FromRows(
        {
            // Weight aliases
            {"lbs", "LB"},
            {"lb", "LB"},
            {"pound", "LB"},
            {"pounds", "LB"},
            {"LBS", "LB"},
            {"LB", "LB"},
            {"kg", "KG"},
            {"KG", "KG"},
            {"kilogram", "KG"},
            {"kilograms", "KG"},
            {"g", "G"},
            {"gram", "G"},
            {"grams", "G"},
            {"G", "G"},
            {"stone", "ST"},
            {"stones", "ST"},
            {"st", "ST"},
            {"ST", "ST"},
            // Length aliases
            {"mm", "MM"},
            {"millimeter", "MM"},
            {"millimeters", "MM"},
            {"cm", "CM"},
            {"centimeter", "CM"},
            {"centimeters", "CM"},
            {"m", "M"},
            {"meter", "M"},
            {"meters", "M"},
            {"in", "IN"},
            {"inch", "IN"},
            {"inches", "IN"},
            {"ft", "FT"},
            {"foot", "FT"},
            {"feet", "FT"}
        },
        {"RawUnit", "CanonicalCode"}
    ),
    TypedTable = Table.TransformColumnTypes(Source, {
        {"RawUnit", type text},
        {"CanonicalCode", type text}
    })
in
    TypedTable

And a separate UnitConversionFactors table:

let
    Source = Table.FromRows(
        {
            // Weight: all to KG
            {"LB", "KG", 0.453592},
            {"KG", "KG", 1.000000},
            {"G",  "KG", 0.001000},
            {"ST", "KG", 6.350293},
            // Length: all to MM
            {"MM", "MM", 1.000000},
            {"CM", "MM", 10.00000},
            {"M",  "MM", 1000.000},
            {"IN", "MM", 25.40000},
            {"FT", "MM", 304.8000}
        },
        {"FromUnit", "ToUnit", "Factor"}
    ),
    TypedTable = Table.TransformColumnTypes(Source, {
        {"FromUnit", type text},
        {"ToUnit", type text},
        {"Factor", type number}
    })
in
    TypedTable

---

Building the Reusable Conversion Functions

Now we build the M functions that will be called from within your data transformation queries. These functions are pure lookups — they take a value and a unit string, consult the tables, and return a result. They should never have side effects.

The Unit Normalization Function

Create a new blank query called fnNormalizeUnit. The goal: given a raw unit string and a quantity, return a record containing the canonical quantity and the canonical unit code.

let
    fnNormalizeUnit = (rawValue as nullable number, rawUnit as nullable text) as record =>
        let
            // Defensively handle nulls
            SafeUnit = if rawUnit = null then "UNKNOWN" else Text.Trim(rawUnit),
            
            // Look up canonical code from alias map
            AliasMatch = Table.SelectRows(
                UnitAliasMap,
                each [RawUnit] = SafeUnit
            ),
            
            CanonicalCode = if Table.RowCount(AliasMatch) > 0
                then AliasMatch{0}[CanonicalCode]
                else "UNKNOWN",
            
            // Look up conversion factor
            FactorMatch = Table.SelectRows(
                UnitConversionFactors,
                each [FromUnit] = CanonicalCode
            ),
            
            HasFactor = Table.RowCount(FactorMatch) > 0,
            Factor = if HasFactor then FactorMatch{0}[Factor] else null,
            ToUnit = if HasFactor then FactorMatch{0}[ToUnit] else "UNKNOWN",
            
            // Compute converted value
            ConvertedValue = if rawValue <> null and Factor <> null
                then rawValue * Factor
                else null,
            
            Result = [
                Quantity_Canonical = ConvertedValue,
                Unit_Canonical = ToUnit,
                Unit_CanonicalCode = CanonicalCode,
                ConversionFactor = Factor,
                ConversionStatus = if CanonicalCode = "UNKNOWN" then "UnmappedUnit"
                    else if Factor = null then "NoConversionFactor"
                    else "OK"
            ]
        in
            Result
in
    fnNormalizeUnit

Notice that this function returns a record, not a scalar. This is a deliberate design choice: you can expand this record into multiple columns with one operation, rather than calling a separate function for each output column. As discussed in Building Reusable Power Query Function Libraries: Parameters, Recursion, and Modular M Code Patterns, this record-return pattern is far more efficient than multiple parallel function calls on the same row.

The ConversionStatus field is your diagnostic output. When you load this data and find Quantity_Canonical is null, the status tells you exactly why: was the unit string unrecognized, or was the unit recognized but we had no factor for it?

The Currency Conversion Function

let
    fnConvertCurrency = (
        rawAmount as nullable number,
        fromCurrency as nullable text,
        toCurrency as text,
        optionalRateDate as nullable date
    ) as record =>
        let
            SafeCurrency = if fromCurrency = null 
                then "UNKNOWN" 
                else Text.Upper(Text.Trim(fromCurrency)),
            
            // Filter rate table — if a date is provided, try to match it
            // Otherwise take the most recent rate available
            RatesFiltered = if optionalRateDate <> null
                then Table.SelectRows(
                    CurrencyRates,
                    each [FromCurrency] = SafeCurrency 
                        and [ToCurrency] = toCurrency
                        and [RateDate] <= optionalRateDate
                )
                else Table.SelectRows(
                    CurrencyRates,
                    each [FromCurrency] = SafeCurrency 
                        and [ToCurrency] = toCurrency
                ),
            
            // Take the most recent matching rate
            RatesSorted = Table.Sort(RatesFiltered, {{"RateDate", Order.Descending}}),
            
            HasRate = Table.RowCount(RatesSorted) > 0,
            MatchedRate = if HasRate then RatesSorted{0}[Rate] else null,
            MatchedRateDate = if HasRate then RatesSorted{0}[RateDate] else null,
            
            ConvertedAmount = if rawAmount <> null and MatchedRate <> null
                then rawAmount * MatchedRate
                else null,
            
            Result = [
                Amount_Converted = ConvertedAmount,
                ExchangeRate_Used = MatchedRate,
                ExchangeRate_Date = MatchedRateDate,
                Currency_Target = toCurrency,
                ConversionStatus = if SafeCurrency = "UNKNOWN" then "UnknownCurrency"
                    else if not HasRate then "NoRateAvailable"
                    else if rawAmount = null then "NullAmount"
                    else "OK"
            ]
        in
            Result
in
    fnConvertCurrency

---

Parsing Embedded Currency and Unit Strings

Before you can normalize, you often have to parse. Real-world data doesn't always come with clean separate columns for amount and currency. You'll encounter strings like:

• "$1,250.00" — dollar sign prefix, comma thousands separator
• "1.250,00 EUR" — European format with trailing currency code
• "¥145000" — yen symbol prefix, no decimal
• "1,250.50 kg" — quantity and unit concatenated

This is where Working with Dates and Text in Power Query: Advanced Transformation Techniques techniques become essential. Let's build a parser for the most common patterns.

Extracting Currency from Embedded Strings

let
    fnParseCurrencyString = (rawString as nullable text) as record =>
        let
            Safe = if rawString = null then "" else Text.Trim(rawString),
            
            // Symbol-to-ISO mapping
            SymbolMap = #table(
                {"Symbol", "ISOCode"},
                {
                    {"$",  "USD"},
                    {"€",  "EUR"},
                    {"£",  "GBP"},
                    {"¥",  "JPY"},
                    {"₹",  "INR"},
                    {"A$", "AUD"},
                    {"S$", "SGD"},
                    {"C$", "CAD"}
                }
            ),
            
            // Check for trailing ISO code (e.g., "1.250,00 EUR")
            // ISO codes are exactly 3 uppercase letters
            TrailingISO = try 
                let 
                    Parts = Text.Split(Text.Trim(Safe), " "),
                    LastPart = List.Last(Parts),
                    IsISO = Text.Length(LastPart) = 3 
                        and LastPart = Text.Upper(LastPart)
                        and not List.Contains(
                            {"KG","LB","CM","MM","ST","FT","IN"},
                            LastPart
                        )
                in
                    if IsISO then LastPart else null
            otherwise null,
            
            // Check for leading symbol
            LeadingSymbolMatch = List.First(
                List.Select(
                    SymbolMap[Symbol],
                    each Text.StartsWith(Safe, _)
                ),
                null
            ),
            
            DetectedCurrency = if TrailingISO <> null then TrailingISO
                else if LeadingSymbolMatch <> null then
                    SymbolMap{
                        List.PositionOf(SymbolMap[Symbol], LeadingSymbolMatch)
                    }[ISOCode]
                else null,
            
            // Strip currency markers from the numeric string
            Stripped = if LeadingSymbolMatch <> null 
                then Text.Middle(Safe, Text.Length(LeadingSymbolMatch))
                else if TrailingISO <> null
                    then Text.Trim(Text.Start(Safe, Text.Length(Safe) - 3))
                else Safe,
            
            // Normalize decimal separator
            // Heuristic: if both . and , present, the last one is decimal
            HasDot = Text.Contains(Stripped, "."),
            HasComma = Text.Contains(Stripped, ","),
            LastDot = if HasDot then Text.PositionOfAny(Stripped, {"."}, Occurrence.Last) else -1,
            LastComma = if HasComma then Text.PositionOfAny(Stripped, {","}, Occurrence.Last) else -1,
            
            DecimalChar = if HasDot and HasComma 
                then (if LastDot > LastComma then "." else ",")
                else if HasDot then "."
                else if HasComma then ","
                else ".",
            
            ThousandsChar = if DecimalChar = "." then "," else ".",
            
            NumericString = Text.Replace(
                Text.Replace(Stripped, ThousandsChar, ""),
                DecimalChar, "."
            ),
            
            ParsedAmount = try Number.From(NumericString) otherwise null,
            
            Result = [
                ParsedAmount = ParsedAmount,
                DetectedCurrency = DetectedCurrency,
                ParseStatus = if ParsedAmount = null then "ParseFailed"
                    else if DetectedCurrency = null then "NoCurrencyDetected"
                    else "OK"
            ]
        in
            Result
in
    fnParseCurrencyString

This function handles the most common patterns in the wild. The decimal separator heuristic (last separator wins) correctly handles European format "1.250,00" where the comma is the decimal and "1,250.50" where it's the period.

---

Integrating Live Exchange Rates via API

For many teams, a manually maintained rate table is insufficient. Here's how to wire up the European Central Bank's free exchange rate API, which provides daily rates for major currencies relative to EUR.

The ECB publishes rates at https://data-api.ecb.europa.eu/service/data/EXR/D.USD+GBP+JPY+AUD+SGD+CAD+INR.EUR.SP00.A?lastNObservations=1&format=csvdata. You can connect to this using Power Query's web connector.

let
    // Fetch daily rates from ECB
    // Note: ECB rates are EUR-based (EUR to X), so we invert for X to USD
    ECBUrl = "https://data-api.ecb.europa.eu/service/data/EXR/"
        & "D.USD+GBP+JPY+AUD+SGD+CAD+INR.EUR.SP00.A"
        & "?lastNObservations=1&format=csvdata",
    
    RawData = Csv.Document(
        Web.Contents(ECBUrl),
        [Delimiter = ",", Columns = null, Encoding = 65001, QuoteStyle = QuoteStyle.None]
    ),
    
    // Promote headers and select relevant columns
    Promoted = Table.PromoteHeaders(RawData, [PromoteAllScalars = true]),
    
    Relevant = Table.SelectColumns(Promoted, 
        {"CURRENCY", "TIME_PERIOD", "OBS_VALUE"}, 
        MissingField.UseNull
    ),
    
    Typed = Table.TransformColumnTypes(Relevant, {
        {"CURRENCY", type text},
        {"TIME_PERIOD", type date},
        {"OBS_VALUE", type number}
    }),
    
    // ECB gives EUR per unit of currency (inverted from what we want)
    // OBS_VALUE for USD means "how many USD per EUR"
    // We want "how many USD per X" where X is not EUR
    // For X→USD: rate = (1 / EUR_per_X) * 1.0  ... actually ECB gives X per EUR
    // So to get X→USD: USD_per_X = (1/ECB_USD_rate) * ECB_X_rate
    WithEURBase = Table.AddColumn(Typed, "FromCurrency", each [CURRENCY]),
    
    // We need the USD rate as a scalar to compute cross-rates
    USDRow = Table.SelectRows(WithEURBase, each [CURRENCY] = "USD"),
    USDperEUR = if Table.RowCount(USDRow) > 0 then USDRow{0}[OBS_VALUE] else null,
    
    // Rate from currency X to USD = ECB_X_rate / ECB_USD_rate
    WithUSDRate = Table.AddColumn(WithEURBase, "Rate_to_USD", 
        each if USDperEUR <> null then [OBS_VALUE] / USDperEUR else null
    ),
    
    // Add EUR itself (always 1/USDperEUR rate relative to USD)  
    EURRow = #table(
        {"CURRENCY", "TIME_PERIOD", "OBS_VALUE", "FromCurrency", "Rate_to_USD"},
        {{"EUR", Date.From(DateTime.LocalNow()), 1.0, "EUR", 
          if USDperEUR <> null then 1.0 / USDperEUR else null}}
    ),
    
    Combined = Table.Combine({WithUSDRate, EURRow}),
    
    // Shape to match our CurrencyRates schema
    Final = Table.SelectColumns(
        Table.RenameColumns(Combined, {
            {"CURRENCY", "FromCurrency_Raw"},
            {"TIME_PERIOD", "RateDate"},
            {"Rate_to_USD", "Rate"}
        }),
        {"FromCurrency", "RateDate", "Rate"}
    ),
    
    WithToCurrency = Table.AddColumn(Final, "ToCurrency", each "USD"),
    
    // Add identity row for USD
    USDIdentity = #table(
        {"FromCurrency", "RateDate", "Rate", "ToCurrency"},
        {{"USD", Date.From(DateTime.LocalNow()), 1.0, "USD"}}
    ),
    
    FinalRates = Table.Combine({WithToCurrency, USDIdentity})
in
    FinalRates

---

Applying Normalization Across Multi-Source Datasets

Now let's put it all together. Assume you've ingested three sources following the Building Multi-Stage Staging Architectures in Power Query: Separating Raw, Cleansed, and Conformed Layers for Scalable ETL Pipelines pattern — raw, cleansed, and conformed layers. Your cleansed sources have already had obvious data quality issues resolved (null handling, type casting, date parsing). Now you're in the conformed layer.

Source A: North American ERP (Clean, Straightforward)

let
    Source_NA_Cleansed = /* your cleansed NA source query */,
    
    // This source already has separate Amount and Currency columns
    // Currency is always "USD", units are in LB and FT
    WithCurrencyResult = Table.AddColumn(
        Source_NA_Cleansed,
        "CurrencyResult",
        each fnConvertCurrency([Amount], [Currency], "USD", [TransactionDate])
    ),
    
    WithUnitResult = Table.AddColumn(
        WithCurrencyResult,
        "UnitResult",
        each fnNormalizeUnit([Weight_Raw], [WeightUnit])
    ),
    
    // Expand both result records
    ExpandCurrency = Table.ExpandRecordColumn(
        WithCurrencyResult,
        "CurrencyResult",
        {"Amount_Converted", "ExchangeRate_Used", "ExchangeRate_Date", "ConversionStatus"},
        {"Amount_USD", "ExchangeRate_Used", "ExchangeRate_Date", "CurrencyStatus"}
    ),
    
    // Rename Amount_Converted to Amount_USD in schema
    ExpandUnit = Table.ExpandRecordColumn(
        Table.AddColumn(ExpandCurrency, "UnitResult2", 
            each fnNormalizeUnit([Weight_Raw], [WeightUnit])
        ),
        "UnitResult2",
        {"Quantity_Canonical", "Unit_Canonical", "ConversionFactor", "ConversionStatus"},
        {"Weight_KG", "Weight_Unit_Canonical", "Weight_ConversionFactor", "UnitStatus"}
    ),
    
    // Select and rename to canonical schema
    Canonical = Table.SelectColumns(ExpandUnit, {
        "SourceSystem", "TransactionDate", "TransactionID",
        "Amount", "Currency", "Amount_USD", 
        "ExchangeRate_Used", "ExchangeRate_Date",
        "Weight_Raw", "WeightUnit", "Weight_KG",
        "CurrencyStatus", "UnitStatus"
    })
in
    Canonical

Source B: European Subsidiary (Embedded Currency Strings)

let
    Source_EU_Cleansed = /* your cleansed EU source query */,
    
    // This source has "AmountString" like "€1.250,00" or "1.250,00 EUR"
    WithParsed = Table.AddColumn(
        Source_EU_Cleansed,
        "ParseResult",
        each fnParseCurrencyString([AmountString])
    ),
    
    ExpandParsed = Table.ExpandRecordColumn(
        WithParsed,
        "ParseResult",
        {"ParsedAmount", "DetectedCurrency", "ParseStatus"},
        {"Amount_Raw", "Currency_Raw", "ParseStatus"}
    ),
    
    // Now apply currency conversion using the parsed fields
    WithCurrencyResult = Table.AddColumn(
        ExpandParsed,
        "CurrencyResult",
        each fnConvertCurrency([Amount_Raw], [Currency_Raw], "USD", [TransactionDate])
    ),
    
    ExpandCurrency = Table.ExpandRecordColumn(
        WithCurrencyResult,
        "CurrencyResult",
        {"Amount_Converted", "ExchangeRate_Used", "ExchangeRate_Date", "ConversionStatus"},
        {"Amount_USD", "ExchangeRate_Used", "ExchangeRate_Date", "CurrencyStatus"}
    )
    
    // ... continue shaping to canonical schema
in
    ExpandCurrency

Combining Sources with Append

Once each source is in canonical schema shape, combining them is straightforward. Since Combining Data from Multiple Sources with Append and Merge Queries in Power Query explains this pattern in detail, we'll focus on the one gotcha specific to normalization pipelines: status column rollup.

let
    All_Sources = Table.Combine({
        Source_NA_Canonical,
        Source_EU_Canonical,
        Source_APAC_Canonical
    }),
    
    // Create a composite status flag
    WithOverallStatus = Table.AddColumn(
        All_Sources,
        "NormalizationStatus",
        each if [CurrencyStatus] = "OK" and [UnitStatus] = "OK" and [ParseStatus] = "OK"
            then "Clean"
            else if List.Contains({"UnknownCurrency", "ParseFailed"}, [CurrencyStatus])
                then "CriticalError"
            else "Warning"
    ),
    
    // Optionally filter out critical errors for the main output
    // and route them to a separate diagnostics query
    CleanRows = Table.SelectRows(
        WithOverallStatus,
        each [NormalizationStatus] <> "CriticalError"
    )
in
    CleanRows

---

Performance Considerations for Large Datasets

The lookup approach has real performance implications you need to understand before deploying to production.

The Per-Row Function Call Problem

Every call to fnNormalizeUnit and fnConvertCurrency inside Table.AddColumn is evaluated for every row. Inside those functions, Table.SelectRows scans the lookup table on each invocation. For a dataset with 500,000 rows and a lookup table with 30 rows, that's 15 million row comparisons just for unit aliases.

The solution is to use Table.NestedJoin instead of per-row function lookups wherever possible:

let
    // Instead of per-row function calls, use a join-based approach for simple lookups
    Source = /* your cleansed source */,
    
    // Step 1: Normalize unit strings via join (no per-row function call)
    JoinedAliases = Table.NestedJoin(
        Source,
        {"WeightUnit"},
        UnitAliasMap,
        {"RawUnit"},
        "AliasMatch",
        JoinKind.Left
    ),
    
    ExpandedAliases = Table.ExpandTableColumn(
        JoinedAliases,
        "AliasMatch",
        {"CanonicalCode"},
        {"Weight_CanonicalCode"}
    ),
    
    // Step 2: Join conversion factors
    JoinedFactors = Table.NestedJoin(
        ExpandedAliases,
        {"Weight_CanonicalCode"},
        UnitConversionFactors,
        {"FromUnit"},
        "FactorMatch",
        JoinKind.Left
    ),
    
    ExpandedFactors = Table.ExpandTableColumn(
        JoinedFactors,
        "FactorMatch",
        {"Factor", "ToUnit"},
        {"Weight_ConversionFactor", "Weight_Unit_Canonical"}
    ),
    
    // Step 3: Apply conversion as a simple column multiplication
    WithConverted = Table.AddColumn(
        ExpandedFactors,
        "Weight_KG",
        each if [Weight_Raw] <> null and [Weight_ConversionFactor] <> null
            then [Weight_Raw] * [Weight_ConversionFactor]
            else null,
        type number
    )
in
    WithConverted

This join-based approach is dramatically faster for large datasets because the join operation is vectorized rather than per-row. The tradeoff is that the code is more verbose and less encapsulated. Use the function approach during development and testing (easier to reason about), then optimize to the join approach for production if performance is a concern.

Query Folding Implications

If your primary data sources support query folding (SQL Server, Synapse, PostgreSQL, etc.), be aware that the moment you join against an in-memory M-generated table (like your lookup tables), you break query folding for all downstream steps on that query. This means the entire dataset is pulled into the M engine before filtering is applied.

The mitigation: apply all filter and projection steps (date ranges, relevant columns, WHERE clause equivalents) before the join operations. This way, folding reduces the dataset as much as possible before it enters the M engine.

---

Handling Edge Cases and Production Hardening

Ambiguous Unit Identifiers

The letter "m" is potentially a meter or a minute. "t" is a metric ton or a troy ounce depending on the industry. Your UnitAliasMap needs to be scoped to the domain — add a Domain column if your pipeline spans multiple measurement domains:

// Extended UnitAliasMap with domain scoping
Source = Table.FromRows(
    {
        {"m",  "M",   "Length"},  // meter in length context
        {"m",  "MIN", "Time"},    // minute in time context
        {"t",  "MT",  "Weight"},  // metric ton in logistics context
        {"t",  "TOZ", "Precious"} // troy ounce in precious metals context
    },
    {"RawUnit", "CanonicalCode", "Domain"}
)

Then pass the domain as a parameter to your lookup function. The domain comes from the source system identifier — your EU manufacturing plant always sends weight data, so its "t" is always a metric ton.

Handling Historical vs. Spot Rates

For financial reporting, you often need historical rates for closed periods and spot rates for open periods. The fnConvertCurrency function already handles this via the optionalRateDate parameter. But you need to decide the policy:

• Closed period transactions: Use the rate that was in effect on the transaction date (point-in-time accuracy)
• Open period transactions: Use today's spot rate (current value reporting)
• Restatement scenarios: Use a specific "as-of" date for all transactions in a reporting period

Document whichever policy you choose in a comment at the top of your canonical pipeline query. This is the kind of decision that gets forgotten and causes audit findings two years later.

Case Sensitivity in Currency Codes

ISO 4217 says currency codes are uppercase, but real data isn't always ISO-compliant. Your function already handles Text.Upper(Text.Trim(fromCurrency)), but make sure your CurrencyRates table stores only uppercase codes. If you receive "eur" or "Eur", the uppercase normalization in the function will find the match. If your table has "eur" in the FromCurrency column, the lookup fails for "EUR" input. Enforce case conventions at the table level, not the lookup level.

---

Hands-On Exercise

This exercise builds the complete pipeline end to end. Use Excel or Power BI Desktop.

Setup: Download or create two CSV files representing two different source systems:

File 1: na_sales.csv

TransactionID,TransactionDate,Amount,Currency,Weight,WeightUnit
NA-001,2024-10-15,5250.00,USD,150,lbs
NA-002,2024-10-16,3100.00,USD,75,pounds
NA-003,2024-10-17,8750.00,USD,220,LB

File 2: eu_sales.csv

TransactionID,TransactionDate,AmountString,Weight,WeightUnit
EU-001,2024-10-15,"€4.200,00",68,kg
EU-002,2024-10-16,"3.800,00 EUR",42,KG
EU-003,2024-10-17,"€9.100,00",95,kilogram

Tasks:

1. Create the CurrencyRates, UnitAliasMap, and UnitConversionFactors tables as shown in this lesson, loaded as queries that do not load to the data model (connection only).

2. Create the fnParseCurrencyString, fnConvertCurrency, and fnNormalizeUnit functions.

3. Create a NA_Canonical query that:

   • Loads na_sales.csv
   • Applies fnConvertCurrency (USD→USD, which should return the identity rate)
   • Applies fnNormalizeUnit to convert pounds to kilograms
   • Outputs the canonical schema with status columns

4. Create an EU_Canonical query that:

   • Loads eu_sales.csv
   • Applies fnParseCurrencyString to extract amount and currency
   • Applies fnConvertCurrency to convert EUR→USD
   • Applies fnNormalizeUnit to standardize weight to kilograms
   • Outputs the canonical schema with status columns

5. Create a Combined_Canonical query that appends NA_Canonical and EU_Canonical.

6. Verify that Weight_KG for NA-001 is approximately 68.04 (150 × 0.453592) and for EU-001 is 68.0 (68 × 1.0).

7. Challenge: Add a third source — a text file where amounts are formatted as "¥145,000" and weights are in stones. Extend the pipeline to handle it without modifying any existing queries.

---

Common Mistakes & Troubleshooting

Mistake 1: Not Including the Identity Row in Currency Rates

If your CurrencyRates table doesn't have a USD→USD row at rate 1.0, then USD-denominated transactions will produce null amounts after the join. This is particularly deceptive because your pipeline "works" for all foreign currency rows but silently nullifies your domestic transactions. Always add identity rows for your base currency.

Mistake 2: Calling Lookup Functions Before Buffering Lookup Tables

If you forget to buffer UnitAliasMap and CurrencyRates, Power Query may re-evaluate these source queries on every function call. If those tables come from external sources (SharePoint, web APIs), you'll hammer those endpoints with thousands of requests. Buffer small lookup tables aggressively.

Mistake 3: Treating ConversionStatus as a Binary OK/Error

In production data, you'll almost always have a mix of "OK", "Warning", and "Error" rows. If you filter to only "OK" rows without routing the others somewhere, you create silent data loss. Build a diagnostics output from day one.

Mistake 4: Hardcoding the Target Currency

Your canonical target currency is "USD" in these examples, but what if your organization's reporting currency changes? Or what if you need EUR-denominated output for European regional reports? Parameterize the target currency as a query parameter. This is a one-line change if you've built the pipeline correctly, and a multi-hour refactor if you haven't. See Parameterized Queries and Dynamic Data Sources in Power Query for the mechanics.

Mistake 5: Assuming Unit Strings Are Consistent Within a Source

Even within a single source system, the same operator will enter "kg", "KG", "Kg", "kilogram", and "kilo" across different records. Never assume a source is self-consistent. Always route through the alias normalization step even for "clean" sources. Add new aliases to UnitAliasMap as you discover them rather than adding special cases to transformation code.

Troubleshooting: Unexpected Nulls in Canonical Quantities

If Quantity_Canonical is null, check these in order:

1. Is Unit_Raw null? The function handles this, returning ConversionStatus = "UnmappedUnit"
2. Is the unit string in UnitAliasMap? Check for whitespace, case differences, or special characters
3. Is the canonical code in UnitConversionFactors? You might have the alias but not the factor
4. Is Quantity_Raw null? A null input always produces a null output

The ConversionStatus column tells you which of these is happening for each row. If you're seeing widespread nulls, add a Table.Group on ConversionStatus plus Unit_Raw to quickly see which unit strings are unmapped.

---

Summary & Next Steps

You've built a production-grade normalization pipeline that treats currency and unit conversion as first-class architectural concerns rather than ad-hoc transformations. The key design principles to carry forward:

• Lookup tables own the conversion logic. M code owns the orchestration. Never mix these responsibilities.
• Always preserve raw values. The canonical columns are derived; the raw columns are truth.
• Return records from functions, not scalars. The multi-column record pattern is both more efficient and more expressive than multiple single-value function calls.
• Status columns are not optional. They're your early warning system for data quality drift.
• Buffer your lookup tables. Small tables, huge performance difference.

The natural next step is scaling this pattern to handle schema variability across sources — what happens when a new source arrives with columns named differently, or with extra columns you don't have in your canonical schema? That challenge is tackled in Handling Dynamic Schema Changes in Power Query: Strategies for Evolving Source Data Structures.

You should also consider how this pipeline fits into a broader incremental refresh architecture. When exchange rates update daily, you don't want to re-process years of historical transactions. The patterns for handling that tradeoff are covered in Automating Incremental Data Refreshes in Power Query with Persistent State and Change Tracking.

Finally, as your organization adds more source systems, the folder-based combination pattern — where new sources are automatically ingested when a new file appears in the right location — becomes essential. Combining Multiple Files from a Folder shows how to make your normalization pipeline automatically extend to new files without any manual query additions.