🧩 Function: UrlEncode

Category: Utility Functions → String & Encoding
Author: Data Engineering
Language: Power Query (M)
Purpose: Converts a text string into a URL-safe format by encoding non-alphanumeric characters as percent-encoded hexadecimal values (%XX).
Compatibility: Power BI, Excel Power Query, and Fabric Dataflows (Gen2)

---

📝 Description

UrlEncode transforms text into a format suitable for inclusion in URLs or API query strings.
All characters except alphanumeric (A–Z, a–z, 0–9) are replaced with a % sign followed by two hexadecimal digits representing the ASCII value of the character.

This ensures data can be transported safely across systems that might otherwise misinterpret spaces, punctuation, or special characters.

---

💡 Example Usage

Input Text	Encoded Result
Hello world!	Hello%20world%21
5" bolts <M8> & café	5%22%20bolts%20%3CM8%3E%20%26%20caf%E9

PowerQuery

Table.TransformColumns(#”PreviousStep”, {{“json_text_column_name”, each UrlEncode(_), type nullable text}})

---

🧠 Function Code

Create Query: UrlEncode

(input as nullable text) as nullable text =>
    let
        // Handle nulls gracefully
        s = if input = null then "" else input,

        // Split text into a list of characters
        chars = Text.ToList(s),

        // Define the hexadecimal digits
        hex = "0123456789ABCDEF",

        // Encode each non-alphanumeric character
        encodeChar = (c as text) as text =>
            let
                code = Character.ToNumber(c),
                isAlphaNum =
                    (code >= 48 and code <= 57) or   // 0–9
                    (code >= 65 and code <= 90) or   // A–Z
                    (code >= 97 and code <= 122),    // a–z
                encoded =
                    if isAlphaNum then
                        c
                    else
                        "%" &
                        Text.Middle(hex, Number.IntegerDivide(code, 16), 1) &
                        Text.Middle(hex, Number.Mod(code, 16), 1)
            in
                encoded,

        // Apply encoding to each character
        encodedList = List.Transform(chars, each encodeChar(_)),

        // Combine encoded characters into a single string
        result = Text.Combine(encodedList)
    in
        result

---

⚙️ Integration Notes

• Save this function as a query in Power Query (e.g. name it fn_UrlEncode).
• Call it from other queries in the same Dataflow or Power BI file using: Table.AddColumn(Source, "EncodedText", each fn_UrlEncode([YourColumn]))
• For consistency, prefix reusable functions with fn_ and store them in a Utility or Common Functions group in the Dataflow.

---

🧭 Related Utilities

Function	Purpose
fn_HtmlEncode	Converts text to HTML-safe entities (<, >, etc.).
fn_HtmlDecode	Reverses HTML entity encoding.
fn_UrlDecode	Converts %XX codes back to their original text representation.