Power BI数据清洗求助：提取并格式化2/3字母代码

需求说明 #

从格式杂乱的每行数据中，提取所有纯字母组成的2位、3位代码，最终按「3字母代码→2字母代码→3字母代码→2字母代码...」的交替格式拼接成字符串；若某类代码有剩余，直接追加到末尾。

解决方案（Power Query M 自定义函数） #

通过正则提取+分组排序的方式实现，以下是可直接复用的函数：

let
    ExtractAndSortCodes = (inputText as text) as text =>
    let
        // 提取文本中所有纯字母字符，拆分后筛选2/3位代码
        CleanText = Text.Remove(inputText, Text.ToList(Text.Remove(inputText, {"a".."z", "A".."Z"}))),
        ExtractCodes = List.Select(
            List.Generate(
                () => [Idx=0, Code=""],
                each [Idx] <= Text.Length(CleanText),
                each [Idx=[Idx]+1, Code=Text.Range(CleanText, [Idx], if [Idx]+2 <= Text.Length(CleanText) then 3 else if [Idx]+1 <= Text.Length(CleanText) then 2 else 0)],
                each [Code]
            ),
            each Text.Length(_) = 2 or Text.Length(_) = 3
        ),
        // 拆分3位、2位代码列表并去重
        ThreeLetter = List.Distinct(List.Select(ExtractCodes, each Text.Length(_)=3)),
        TwoLetter = List.Distinct(List.Select(ExtractCodes, each Text.Length(_)=2)),
        // 交替拼接两类代码
        AlternateList = List.Generate(
            () => [T3Idx=0, T2Idx=0, Output=[]],
            each [T3Idx] < List.Count(ThreeLetter) or [T2Idx] < List.Count(TwoLetter),
            each if [T3Idx] < List.Count(ThreeLetter) and [T2Idx] < List.Count(TwoLetter) then
                [T3Idx=[T3Idx]+1, T2Idx=[T2Idx]+1, Output=List.Combine({[Output], {ThreeLetter{[T3Idx]}, TwoLetter{[T2Idx]}}})]
            else if [T3Idx] < List.Count(ThreeLetter) then
                [T3Idx=[T3Idx]+1, T2Idx=[T2Idx], Output=List.Combine({[Output], {ThreeLetter{[T3Idx]}}})]
            else
                [T3Idx=[T3Idx], T2Idx=[T2Idx]+1, Output=List.Combine({[Output], {TwoLetter{[T2Idx]}}})]
        ),
        // 拼接成最终字符串
        FinalResult = Text.Combine(List.Last(AlternateList)[Output], " ")
    in
        FinalResult
in
    ExtractAndSortCodes

使用步骤 #

1. 打开Power Query编辑器，点击「主页」→「新建源」→「空查询」；
2. 将上述代码粘贴到高级编辑器中，命名为ExtractAndSortCodes；
3. 返回目标数据查询，添加自定义列，调用函数：= ExtractAndSortCodes([你的文本列名称])；
4. 加载处理后的数据到Power BI模型即可。

示例验证 #

输入杂乱文本后，输出符合要求的格式：

ABC XY DEF YZ GHI WX
XYZ AB ZXY

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