Parse CODESSTRING in Power BI Using Power Query (M)

📘 Context

The CODESSTRING field returned from Oracle contains serialised key-value pairs using non-printing ASCII characters:

• Unit Separator  (ASCII 31) separates the key and value within a pair.
• Record Separator  (ASCII 30) separates each pair from the next.

Example value:

ACCOUNTCODE_B10201CODE_C200CODE_DCODE_E2011072

---

✅ Goal

Convert this encoded string into structured columns in Power BI, e.g.:

ACCOUNT	CODE_B	CODE_C	CODE_D	CODE_E
	10201	200		2011072

---

🛠️ Solution: Power Query Function

Step 1: Create the Parsing Function

1. Open Power BI Desktop
2. Go to Transform Data > New Source > Blank Query
3. Open the Advanced Editor and paste the following:

let
  parseCodestring = (codestring as text) as record =>
    let
        recordSeparator = Character.FromNumber(30),  // ASCII 30: ''
        fieldSeparator = Character.FromNumber(31),   // ASCII 31: ''
        rawPairs = Text.Split(codestring, recordSeparator),
        kvPairs = List.Transform(
            List.Select(rawPairs, each Text.Contains(_, fieldSeparator)),
            each Text.Split(_, fieldSeparator)
        ),
        keys = List.Transform(kvPairs, each _{0}),
        values = List.Transform(kvPairs, each if List.Count(_) > 1 then _{1} else null),
        output = Record.FromList(values, keys)
    in
        output
in
  parseCodestring

4. Rename the query to fxParseCodestring

---

Step 2: Apply the Function

In your main query (that contains the CODESSTRING field):

1. Go to Add Column > Invoke Custom Function
2. Choose:
   • Function name: fxParseCodestring
   • Input column: CODESSTRING
3. This will create a column of records. Click the expand icon (🡒) to flatten it into structured fields.

---

💡 Tips

• Empty or missing values will be returned as null
• All keys become column headers
• You can trim/rename/clean field names after expansion

---

🔄 Reuse

This function can be reused for:

• Any similar field format
• Other projects using ASCII 30/31 delimiters