How-To: Unusual Formatted String to Record in Power Query

[Nolock]

Recently, I was faced with an interesting problem. I tried to request a web service endpoint which responds with JSON data. In the root of the JSON was only unimportant data, at least for my use case. The valuable part was hidden in a property in an unusual format. Let's find out how to parse the following JSON data.

{
    "irrelevant property 1": "foo",
    "irrelevant property 2": "bar",
    "irrelevant property 3": "foo bar",

    "data": "id=123|firstname=foo|lastname=bar"
}

Thanks to Power Query’s internal function Json.Document I was able to parse the root of the document. But what to do with the complex string value in the data property? I only needed values of some keys. The first idea was to parse these few values. But the requirements could have changed before long, and I would have had to modify the code. Not so good. Then the idea came to me: Let’s convert the string into a format that can be parsed directly to a record. If I have a record, I can do anything I can imagine with the data.
After checking some examples, I found some exceptions which complicate the conversion. I have summed up these challenges into one example. My test input string is:

a=1|b c=2|foo "bar"="3"|d=|e=foo|

Keys can contain spaces and quotes. Values can be empty. It uses a pipe as a delimiter.

The solution is a custom function called fnStringToRecord which converts

a=1|b c=2|foo "bar"="3"|d=|e=foo|

to

[#"a"="1",#"b c"="2",#"foo ""bar"""="""3""",#"d"="",#"e"="foo"]

Now you can see the key/value pairs which are used in a record, right?

The code

The function fnStringToRecord has 3 parameters:

• the inputString is the input string,
• the itemDelimiter is a delimiter between 2 key/value pairs (comma in our case),
• and the assignmentChar is a char used as an assignment operator between key and value (equal sign in our case)

(
    inputString as text,
    itemDelimiter as text,
    assignmentChar as text
) as record =>
    let 
        // remove item delimiter from the end of the string (if any)
        RemoveItemDelimiterAtTheEndOfTheString = Text.TrimEnd(inputString, {itemDelimiter}),
        // duplicate all quotes in the string (if any)
        DuplicateQuotes = Text.Replace(RemoveItemDelimiterAtTheEndOfTheString, """", """"""),
        // replace assignment character with =
        AddQuotesAroundAssignmentChar = Text.Replace(DuplicateQuotes, assignmentChar, """="""),
        // add quotes around item delimiters
        AddQuotesAroundItemDelimiter = Text.Replace(AddQuotesAroundAssignmentChar, itemDelimiter, """,#"""),
        // prepend and append quotes
        AddQuotesAroundString = "#""" & AddQuotesAroundItemDelimiter & """",
        // add brackets and create a record from a string
        AsRecord = Expression.Evaluate("[" & AddQuotesAroundString & "]")
    in
        AsRecord

You can also use this function for more exotic formats like fnStringToRecord("a:1,b c:2,foo ""bar"":""3"", d:, e:foo", ",", ":"). Or you can convert the input string only into the JSON format and then use the function Json.Document. The only thing that can stop you is your imagination!