PowerBI处理含嵌套逗号的混合JSON数组数据列求助

核心处理逻辑 #

针对你的混合数据场景，用Power Query（M语言）实现以下规则：

• 单个JSON数组：解析后提取第一个元素，保留元素内的逗号
• 多个JSON数组：先拆分每个独立数组，提取每个数组的第一个元素；只要有元素含逗号，就用分号连接所有元素，否则用逗号连接
• 非JSON数据直接保留原样

操作步骤 #

1. 进入Power Query编辑器
   加载数据后，点击「转换」选项卡 → 「进入Power Query编辑器」

2. 添加自定义列
   点击「添加列」→ 「自定义列」，粘贴以下M代码：

= let
    CurrentValue = [DataColumn],
    IsJsonArray = Text.StartsWith(CurrentValue, "[") and Text.EndsWith(CurrentValue, "]"),
    Processed = if not IsJsonArray then
        CurrentValue
    else
        let
            SplitArrays = Text.Split(Text.Trim(CurrentValue, "[]"), "],[")
        in
            if List.Count(SplitArrays) = 1 then
                Json.Document("[" & SplitArrays{0} & "]"){0}
            else
                let
                    ExtractFirstElements = List.Transform(SplitArrays, (arr) => Json.Document("[" & arr & "]"){0}),
                    JoinSeparator = if List.AnyTrue(List.Transform(ExtractFirstElements, (elem) => Text.Contains(elem, ","))) then ";" else ",",
                    JoinedResult = Text.Join(JoinSeparator, ExtractFirstElements)
                in
                    JoinedResult
in
    Processed

3. 验证并替换原列
   检查自定义列的输出是否符合预期，确认无误后右键自定义列 → 「替换原列」，最后点击「关闭并上载」完成数据转换。

代码关键说明 #

• IsJsonArray：通过首尾字符快速判断单元格内容是否为JSON数组格式
• SplitArrays：用"],["作为分隔符拆分多个数组，避免和元素内部的逗号混淆
• Json.Document：将拆分后的片段重新包装为合法JSON数组并解析，提取第一个元素
• JoinSeparator：自动判断连接符，只要有元素包含逗号就用分号，否则用逗号

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