PQ多重替换操作再探

实例下载：链接：http://pan.baidu.com/s/1jIgIHdO 密码：p30j

昨天我分享了两种PQ中多重替换的方法（PQ中的多重替换尝试），excel120群(453524740)中的@上海-SEM-施阳 网友提供了另一种通过List.Accumulate来实现目的的方法，这对我来说是一种启发，我由此想到了另外的几种实现方法，效率上来说可能会更高一些，在此呢做一介绍吧。

多重替换这个操作，从代码表操作的角度理解，应该是一个迭代(iterate)的过程，PQ可进行迭代的函数有List.Accumulate、List.Generate等，其过程和求数列前n项和差不多，就是依次迭代，不过List.Generate相比List.Accumulate来说的一个好处就是它可设置中断规则，因为我这个例子的情况代码和其所代表的选项之间是严格的一一对应的关系，既是单射又是满射是为双射，那么在代码表中找到特定行了以后其实是不必再往下继续查找了，因此这时候中断的话可以提高效率，我做这个表的初衷是处理大型的代码表，比如说国民经济行业分类与代码（GB/4754-2011），企业在填报数据的时候只填所属的四位行业代码，我们需要在行业代码表中找到相应的行业名称把四位代码替换掉，要在1642个代码中找到特定的那一个，而企业数量有上百万之多，确实没必要每个企业都遍历所有的代码，我们只要找到特定的那一个然后终止对该企业的查询，然后就可以处理下一个企业了，这样效率会提高不少。

但是我再一想，这个需求其实也没必要用迭代或是递归的，因为目标值在代码表中是精确匹配的，只需选定相应行即可。List.Accumulate、List.Generate这一类迭代函数只有在处理多次部分替换的时候才能体现出它的优势所在，比如说要把下面左侧表中的各项按照右侧表中的对应关系进行批量替换：

原始表和替换对照表

替换结果如下：

替换结果

像这种情况下我们用递归或是迭代函数效率会高一些，参见国外网友的介绍：Using List.Generate() To Make Multiple Replacements Of Words In Text In Power Query。

不过我这个情况暂时还用不着递归，在这里有高射炮打蚊子的感觉，并且那个网友也介绍说递归函数是要尽量避免的。所以我在这里是另寻他法的。

下面是代码介绍：

let
    源 = 性格调查表,

  // 方法1：Table.SelectRows
  // 以"性别"为依据在性别列表中选择相关行，返回该行的[性别]列
    性别 = Table.TransformColumns(源,{
     {"性别", each let yz=_,
      sx = Table.SelectRows(性别列表,each [选项]=yz)
      in if Table.RowCount(sx)=0 then _ else sx[性别]{0}}}),

  // 方法2：Table.PositionOf
  // 以原始表中第1题的代码为依据，在第1题代码表中查找相关行序号，返回该行的[颜色]列
  // 和方法1相比，方法2只查找相关行序号而不必返回筛选出的整张表
    第1题 = Table.TransformColumns(性别,{
     {"1你最喜欢的颜色是", each 
      let n = Table.PositionOf(第1题,[选项=_],0,"选项")
      in  if n=-1 then _ else 第1题[颜色]{n}
     }}),

  // 方法3：recursive function
  // 用递归函数，这种方法可处理文本的多次部分替换，
  // 比如要把this is an apple替换为that is an orange
    第2题 = Table.TransformColumns(第1题,{
     {"2你最想去旅游的国家是", each 
      let A = 第2题[选项], B = 第2题[国家],
       Replacement = (input, n)=> 
        //下面通过两层if语句实现目的
        if input= A{n} then B{n} else 
           //如果输入值等于第2题[选项]中的某个值，那么结束循环，返回相应的[国家]值，否则进行下一个判断
        if n=List.Count(A)-1 then input
         else @Replacement(input, n+1)
           //如果循环已经到达第2题代码表中的最后一行，则结束循环，返回原始输入值，否则把n+1进行下一次循环
      in Replacement(_, 0)}})

in
    第2题

顺便说一下，如果在PQ查询中遇到

Formula.Firewall: 查询“xx”(步骤“xx”) 将引用其他查询或步骤，因此可能不会直接访问数据源。请重新生成此数据组合。

这样的问题，那是文件的隐私设置级别的原因：

查询选项

如此这般设置即可。

隐私设置

.
.
.
.
.
.
..