在Power BI中能否解码URL？求可行实现方案

需要将URL编码的字符串（例如 %7b%22pageSize%22:500,%22previousIds%22:%5b3%5d,%22nextIds%22:%5b593%5d%7d&search=%7b%22filter%22:%5b%7b%22op%22:%22gte%22,%22field%22:%22id%22,%22value%22:%220%22%7d%5d%7d）转换为人类可读的JSON格式：{"pageSize":500,"previousIds":[3],"nextIds":[593]}&search={"filter":[{"op":"gte","field":"id","value":"0"}]}。

尝试过的失败方法 #

自定义字符替换方法（报错） #

这种方法仅手动替换部分编码字符，无法覆盖所有场景，维护成本高：

// Custom function to decode a URL-encoded string
DecodeUrlEncodedString = (url as text) =>
    let
        // Replace URL-encoded characters
        decodedString = Text.Replace(url, "%7b", "{", Replacer.ReplaceText),
        decodedString1 = Text.Replace(decodedString, "%22", "\"", Replacer.ReplaceText),
        decodedString2 = Text.Replace(decodedString1, "%3a", ":", Replacer.ReplaceText),
        decodedString3 = Text.Replace(decodedString2, "%2c", ",", Replacer.ReplaceText),
        decodedString4 = Text.Replace(decodedString3, "%5b", "[", Replacer.ReplaceText),
        decodedString5 = Text.Replace(decodedString4, "%5d", "]", Replacer.ReplaceText),
        decodedString6 = Text.Replace(decodedString5, "%7d", "}", Replacer.ReplaceText)
    in
        decodedString6,

基于Uri.Parts的方案（失败） #

该方法依赖完整URL结构，仅输入查询参数部分时无法解析：

= (data as text) as text =>
let 
    DecodedText = Uri.Parts("https://webservices4.autotask.net/ATServicesRest/V1.0/Projects/query/next?paging=" & data)[Query][paging]
in
    DecodedText

更新后的Uri.Parts方法（仍报错：Expression.Error: The field 'paging' of the record wasn't found） #

错误原因是输入字符串不含指定分隔符，逻辑未适配查询参数结构：

converturl = (uri as text) as text => 
let
    textBeforePaging = Text.BeforeDelimiter(uri, "?paging"),
    textAfterPaging = Text.BetweenDelimiters(uri, "paging=", " "),
    out = Uri.Parts(textBeforePaging & Text.Replace(textAfterPaging , "&", "%26"))[Query][paging]
in
    out,

正确解决方法 #

Power Query内置的*Uri.UnescapeDataString*函数可直接解码标准URL编码字符，结合HTML实体处理即可解决问题：

自定义解码函数 #

DecodeUrlQuery = (encodedText as text) as text =>
    let
        // 先将HTML实体&转换为标准&，再解码整个字符串
        replaceAmp = Text.Replace(encodedText, "&", "&"),
        decoded = Uri.UnescapeDataString(replaceAmp)
    in
        decoded

使用示例 #

将目标编码字符串传入函数：

DecodeUrlQuery("%7b%22pageSize%22:500,%22previousIds%22:%5b3%5d,%22nextIds%22:%5b593%5d%7d&search=%7b%22filter%22:%5b%7b%22op%22:%22gte%22,%22field%22:%22id%22,%22value%22:%220%22%7d%5d%7d")

输出结果：
{"pageSize":500,"previousIds":[3],"nextIds":[593]}&search={"filter":[{"op":"gte","field":"id","value":"0"}]}

说明 #

• *Uri.UnescapeDataString*自动处理所有标准URL编码字符，无需手动替换单个字符。
• 额外处理&是因为输入中存在HTML实体编码的&，需先转换为标准符号再解码。

内容的提问来源于stack exchange，提问作者Terran Brown