PowerFx: Parse a provided JSON string to retrieve a list of keys and their values.

parse-json.yaml

// Made with ♥ by @ohthreesixfive
// Output: A two-column collection of keys and values parsed from the provided json string
ClearCollect(
    colJsonParseResults,
    With(
        {jsonString: varYourJSONString},// your json string goes here
        With(
            {
                jsonStringFormatted: If(
                    Char(10) in jsonString,// json string is indented
                    jsonString,// then use the json string as is
                    Substitute(// else add newlines to allow for regular expression to match appropriately
                        Substitute(
                            Substitute(
                                jsonString,
                                "{",
                                "{" & Char(10)
                            ),
                            "}",
                            "}" & Char(10)
                        ),
                        ",",
                        "," & Char(10)
                    )
                )
            },
            ShowColumns(
                AddColumns(
                    MatchAll(// use a regular expression formula with named subgroups to capture different types of json values and their keys
                        jsonStringFormatted,
                        "(?<arrayElement>(?:""(?<arrayKey>[a-zA-Z0-9_\s]*)"":\s*\[)\s*(?<arrayValue>[^-]*)])|(?<stringElement>(?:""(?<stringKey>[a-zA-Z0-9_\s]*)"":\s*[^\[])(?<stringValue>(?:.*))"")|(?<numberElement>(?:""(?<numberKey>[a-zA-Z0-9_\s]*)"":\s*[^\[])(?<numberValue>[0-9]*),)"
                    ),
                    "Key",
                    Coalesce(// return the first non-blank key result from the named subgroups
                        arrayKey,
                        stringKey,
                        numberKey
                    ),
                    "Value",
                    Coalesce(// return the first non-blank value result from the named subgroups
                        arrayValue,
                        stringValue,
                        numberValue
                    )
                ),
                "Key",
                "Value"
            )
        )
    )
);

Updated to allow underscores (_) and spaces ( ) in keys.