PowerQuery清理非文件名字符(清除指定列表中的所有字符)

     今天我讲的这个案例的场景是：我在Excel表格里保存了一些列信息，如下左图所示。这些列将会在我的程序中用于自动生成文件。我们都知道能作为文件名的字符是有限制的，Windows中不予许在文件名出现部分字符，这些字符如下右图所示。

    为了防止我的程序在运行过程中不会因为文件名混入以上的非法字符而中途退出，我需要预先处理那些我需要作为文件名的列。我的文件名的命名格式如下：

ID-省市自治区-城市-公司

将我们的目标用一句话来描述：

将表中省市自治区，城市和公司列中所有单元格中的非文件名字符清理掉

 

操作步骤如下：

1. 首先将数据导入到PowerQuery中，该步骤很简单，不再给出图片演示，不清楚的话，可查阅我之前的实例；
2. 我在PowerQuery中将这个导入的表命名为Data，如下图；

   

3. 选择“关闭并上载/关闭并上载至/仅创建链接”，退出PowerQuery编辑器，回到Excel界面，在Excel中输入非法文件名字符集列表：

    

4. 类似第1步，将非法文件名字符导入到PowerQuery中，并将该列表命名为“SearchList”

   

5. 接下来我们需要将SearchList转换为List，这是因为后面我们需要使用List的遍历函数来遍历其中所有的项，操作很简单，只需要右击标题单元格，选择“深化”即可

   

6. 为了在PowerQuery保留以上已经导入的数据，我在PowerQuery中新建一个空白查询，来做后续的处理。在左侧导航窗格的空白区域右击，依次找到空白查询项

   

7. 接下来的思路是：遍历列表SearchList中的所有项，依次清理Data表中所有想要处理的列。这里实际会有2层遍历，外面一层是遍历列表项，里面一层是遍历所有的列。我们首先考虑最外面一层。对列表项进行遍历，需要使用到函数List.Accumulate，下图是官方对该函数的解释。

   

8. 照着List.Accumulate函数的格式，在空查询中输入如下所示的内容，以下内容还未写完，请不要输入回车检查运算结果！

   = List.Accumulate(SearchList,Data,(t,i)=>                    )

   (t,i)=>符号后面我留了空，因为后面的部分是要对表进行处理，是后续步骤的内容。我们先理清当前这个函数的含义：

    第一个参数是SearchList：函数将遍历SearchList所有的项

    第二个参数是Data：这是函数最初进行处理的表，意即函数遍历SearchList每一项，首先在处理SearchList的第一项时，是针对Data这个表进行操作，操作完毕后我们得到Data表被处理后的表Data1(内存中)；接下来处理SearchList的第二项，它针对的是Data1表，处理完毕后Data1变化为Data2；然后处理SearchList的第三项，其针对的是Data2，处理完毕后Data2变化为Data3；依次类推，直到最后处理完毕SearchList中的所有项，我们就将最初的Data表进行了对应次数的变换处理，得到最终处理完毕的Data表。

    第三个参数是一个函数：它告诉List.Accumulate函数，在每一次使用SearchList中某一项操作Data表时，其操作的方式是如何的。因而这个函数工作的时候，必须知道当前的SearchList项是什么，以及Data表。参数的顺序是固定的，List的当前项current应该放在后面，状态值也就是Data表要放前面，我的公式中使用t(table)，i(item)标识这2个参数。

9. 在以上List.Accumulate函数第三个参数的主体部分，我们需要对Data表的特定列进行替换值操作，需要用到Table.ReplaceValue函数，下图是该函数的简单介绍

 第一个参数是表对象：此处就是当前的表对象

 第二个参数是被替换的值：此处就是当前SearchList列表中需要处理的项i

 第三个参数是替换后的值：我们是需要清除，所以就是变成空字符串””，2个引号间中间不要有空格！

 第四个参数是一个函数：它告诉Table.ReplaceValue函数，替换操作使用哪种操作方式来替换，我们使用已有Replacer.ReplaceText即可

 第五个参数是需要对表的哪些列进行操作：这个参数是一个字符串列表

最终我们书写好的公式代码如下：

= List.Accumulate(SearchList,Data,(t,i)=> Table.ReplaceValue(t,i,"",Replacer.ReplaceText,{"省/市/自治区","城市","公司"}))

 

最后处理的结果如下图所示