如何将含两种格式的数据转换/逆透视为键值对两列？

我有一列输入数据COL1，其中包含字段及其对应值，分为两种格式：

• 部分字段/值对以=分隔，字段和值在同一行
• 另一部分以文本表格形式呈现，表头为COUNTRY、CAPITAL、POPULATION、LANGUAGE，表头行之后是对应的值行

需要输出两列：字段和对应值。对于表格格式的内容，表头行作为字段，后续行的对应位置内容作为值，实现逆透视风格的输出。

当前输入与输出问题 #

现有代码将所有空格和=替换为@后拆分，导致表格内容被拆分为单个元素的行，无法实现表头与值的对应匹配，不符合逆透视需求。

修正后的Power Query M代码 #

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("lVDJcoQgEP0Vy3MO6sRlKpVDCx0kakOxHCbWVP7/L4KoI5mcwoV6zVuatyw5U+QkIbnsPUNvlMb8/rLkNwQTJlVRVREz5cmZW3YeBlo6mHaklfYTOKloRROQ8CA2K/aFbPg2qH0/SRbJBoTHw6gs6ktdFMVuu7KjjiPNYMY0MmxHQ/CNIU1Zvj5kGQeSdhP2OAnp50TYG28tTnYPLNNA7h0b3j4MUrgEmhkougyeBPz6ce85aLRuQ9dL257xG1vu2rRUsBISk+yp3vLP2/+qfirbDSDDgCdbh+dRHTU2dd1259rZyo9CSVwRprt+wgjGHbi6dl3RnMoHP/z4/gM=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [COL1 = _t]),
    // 去除每行多余空格，保留单个空格分隔
    CleanSpaces = Table.TransformColumns(Source, {{"COL1", each Text.Combine(List.Select(Text.Split(_, " "), each _ <> ""), " ")}}),
    // 添加标记：区分含=的行、表格表头行、表格值行
    AddRowType = Table.AddColumn(CleanSpaces, "RowType", each 
        if Text.Contains(_, "=") then "KeyValue"
        else if Text.StartsWith(_, "COUNTRY") then "TableHeader"
        else "TableValue"),
    // 提取表格表头字段
    TableHeader = List.First(Table.SelectRows(AddRowType, each [RowType] = "TableHeader")[COL1]),
    HeaderList = Text.Split(TableHeader, " "),
    // 处理键值对行：拆分字段和值
    ProcessKeyValue = Table.SelectRows(AddRowType, each [RowType] = "KeyValue"),
    SplitKeyValue = Table.SplitColumn(ProcessKeyValue, "COL1", Splitter.SplitTextByEachDelimiter({" = "}, QuoteStyle.Csv), {"字段", "值"}),
    // 处理表格值行：拆分值并与表头配对
    ProcessTableValue = Table.SelectRows(AddRowType, each [RowType] = "TableValue"),
    SplitTableValue = Table.TransformColumns(ProcessTableValue, {{"COL1", each Text.Split(_, " ")}}),
    ExpandTablePairs = Table.AddColumn(SplitTableValue, "Pairs", each List.Zip({HeaderList, [COL1]})),
    ExpandPairs = Table.ExpandListColumn(ExpandTablePairs, "Pairs"),
    SplitPairs = Table.SplitColumn(ExpandPairs, "Pairs", Splitter.SplitTextByDelimiter(","), {"字段", "值"}),
    // 清理字段和值的多余引号/括号
    CleanFields = Table.TransformColumns(SplitPairs, {
        {"字段", each Text.Trim(_, "()\"")},
        {"值", each Text.Trim(_, "()\"")}
    }),
    // 合并键值对结果和表格结果，保留需要的列
    CombineResults = Table.Combine({SplitKeyValue, CleanFields}),
    FinalTable = Table.SelectColumns(CombineResults, {"字段", "值"})
in
    FinalTable

代码逻辑说明 #

1. 空格清理：统一每行空格格式，将多个连续空格替换为单个空格，避免后续拆分出现异常
2. 行类型标记：为每行添加类型标签，区分键值对行、表格表头行、表格值行，便于分类处理
3. 键值对处理：直接按=分隔符拆分，快速得到字段与值的对应关系
4. 表格内容处理：
   • 提取表头并拆分为字段列表
   • 将每个表格值行拆分为值列表
   • 通过List.Zip将表头字段与对应值配对，展开为多行字段-值对
5. 结果合并：将键值对处理结果与表格处理结果合并，只保留字段和值两列，得到最终逆透视表格

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