如何在Power BI中高效移除多预定义字符串，仅保留自由文本？

问题背景 #

处理Microsoft Forms导出的表格时，多选问题的答案以分号分隔存储在单列。示例数据：

ID  Colors
1   Red;
2   Green;yellow-orange;
3   Red;Blue;
4   purple;
5   Red;gold;

已通过DAX公式（如Red = IF(CONTAINSSTRING([Colors],"Red;"),1,0)）统计预定义选项，但提取自定义的Other文本时，多层嵌套SUBSTITUTE在选项较多时过于繁琐，需要更简洁的实现方式。

方法1：Power Query批量预处理（推荐） #

适合在数据加载阶段完成清理，操作直观且可复用：

• 导入数据到Power Query，选中Colors列，执行拆分列→按分隔符（分号）→拆分到行
• 定义预定义选项列表：predefinedList = {"Red", "Green", "Blue"}
• 添加自定义列筛选非预定义值：

  Other Text = if List.Contains(predefinedList, [Colors]) then null else [Colors]
  

• 若需合并同一ID的多个Other文本，使用分组依据功能，按ID分组后合并Other Text列
• 加载回表格即可得到整理好的Other自由文本列

方法2：DAX实时计算（适合Power BI等工具） #

无需预处理，直接在模型中生成计算列，两种实现方式：

方式A：基于计算表的批量替换 #

1. 先创建预定义选项的计算表：

   PredefinedColors = DATATABLE(
       "Color", STRING,
       {{"Red;"}, {"Green;"}, {"Blue;"}}
   )
   

2. 生成Other文本列：

   Other Text = 
   VAR Original = [Colors]
   VAR Cleaned = 
       CONCATENATEX(
           PredefinedColors,
           Original,
           "",
           [Color],
           SUBSTITUTE([Value], [Color], "")
       )
   RETURN TRIM(Cleaned)
   

方式B：直接嵌入列表的REDUCE函数 #

无需单独创建计算表，用REDUCE遍历替换：

Other Text = 
VAR Predefined = {"Red;", "Green;", "Blue;"}
VAR Original = [Colors]
VAR Cleaned = REDUCE(Original, Predefined, (curr, item) => SUBSTITUTE(curr, item, ""))
RETURN TRIM(Cleaned)

REDUCE会自动遍历预定义列表，依次替换掉原文本中的对应内容，最后返回清理后的自由文本。

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