iview table增加一行减少一行_PQ入门函数：Table.ReplaceValue

一、基础介绍

Table.ReplaceValue函数的官方说明如下：
Table.ReplaceValue(table as table, oldValue as any, newValue as any, replacer as function, columnsToSearch as list)

该函数实现的功能是将Table中的值替换成新值，语法翻译成汉语大致如下：

Table.ReplaceValue(要替换的Table, 要被替换的旧值oldValue, 要替换成的新值newValue, 替换规则函数, 在Table的哪列里替换)

我们首先可以通过基础操作来看一下替换效果。如下有一个Table，是不同的店仓编号对应的库存数量，要求将第一列“店仓编号”中的字母A替换成“仓库”两个字。

选中第一列，点击【转换】-【替换值】，第一个对话框输入A，也就是要查找A，第二个对话框中输入“仓库”，也就是要被替换成的值，点击确定后可以看到第一列中的A都被替换成了“仓库”两个字。

生成的函数如下：

= Table.ReplaceValue(Source,"A","仓库",Replacer.ReplaceText,{"店仓编号"})

其意思是，在Source这个Table的{"店仓编号"}里查找“A”这个字母，替换成“仓库”，替换方式为Replacer.ReplaceText，也就是替换文本。

因为刚才要替换的值是文本，所以替换方式为Replacer.ReplaceText，字符串中的A都被替换成“仓库”。如果在刚才的替换界面勾选【单元格匹配】(上图中的步骤4)，替换方式会变成Replacer.ReplaceValue，由于没有完全匹配的，因此替换不会成功。如果要替换的是null等非文本的，就必须要用Replacer.ReplaceValue了。如果单元格中仅有A这个字符，则可被替换为“仓库”，如下图所示：

如果想在多列里替换呢，在最后一个参数中添加对应的列名即可。如下图，要在第一列、第三列里都把A替换成“仓库”，最后一个参数改成{"店仓编号",  "店仓编号2"}就可以了。

= Table.ReplaceValue(Source,"A","仓库",Replacer.ReplaceText,{"店仓编号",  "店仓编号2"})

二、基础细节进一步介绍

对基础用法了解之后，通过下面的例子介绍一些细节内容。如下图，第一列是级别，后面是不同子级别，分别为级别后面加上001、002、003等，没有的子级别为空(null)。

以下函数会将各个子级别中为null的替换成“无内容”字符串。替换逻辑为：第二参数被替换值为null，第三参数为要替换成的新值“无内容”这三个字。其中Table.ColumnNames(Source)获得标题名称的List，用List.Skip去掉第一个标题后获得包含所有子级别名称的List。

= Table.ReplaceValue(Source,null,"无内容",Replacer.ReplaceValue,List.Skip(Table.ColumnNames(Source)))

以下函数会将各个子级别中为空的替换成“级别”对应的文本。替换逻辑为：被替换值为null，要替换成的内容为each [级别](也即每一行对应的[级别]内容)。

= Table.ReplaceValue(Source,null,each [级别],Replacer.ReplaceValue,List.Skip(Table.ColumnNames(Source)))

将null改成each null结果和上面的一样：

= Table.ReplaceValue(Source,each null,each [级别],Replacer.ReplaceValue,List.Skip(Table.ColumnNames(Source)))

如下代码将子级别1的每个元素替换成“级别”中对应的值，相当于将级别那一列复制到子级别1的那一列。

= Table.ReplaceValue(Source,each [子级别1],each [级别],Replacer.ReplaceValue,{"子级别1"})

如下代码将子级别1的每个元素替换成“替换测试”字符串。

= Table.ReplaceValue(Source,each [子级别1],"替换测试",Replacer.ReplaceValue,{"子级别1"})

三、进阶内容介绍

重新回顾一下Table.ReplaceValue函数的各个参数：

Table.ReplaceValue(table as table, oldValue as any, newValue as any, replacer as function, columnsToSearch as list)

可以看到，被替换值可以是任意类型，替换的新值也可以是任意类型，并且替换规则是个函数，这样Table.ReplaceValue的函数应用就不仅仅限于替换个别字符串或文本了。

首先来看一下第四参数，上面用到的是Replacer.ReplaceValue这个函数，这个函数属于替换器函数，语法如下：

Replacer.ReplaceValue(value as any, old as any, new as any) as any

它的功能是将old值替换成新值new，将其放入Table.ReplaceValue函数后可以将Table.ReplaceValue函数改写成以下形式：

Table.ReplaceValue(table as table, oldValue as any, newValue as any, (value,old,new)=>函数表达式, columnsToSearch as list)，其中(value,old,new)=>函数表达式是一个三参数的函数，一般也写成(x,y,z)=>函数表达式，其中z代表newValue，y代表oldValue，x代表包含要被替换值的数据，下面通过一些例子来说明。

示例一：成绩表

有如上两次考试成绩记录，第2次考试有些人缺考，成绩处为null。

下面的代码将第2次考试中为null的改为“缺考”，有成绩的改成“参考”。代码中的y代表第二参数each [2次]，也就是第2次考试中的每个值，如果y是数值(number)就返回“参考”，否则返回“缺考”(这里z代表“参考”但未引用z)。

= Table.ReplaceValue(Source,each [2次],"缺考",(x,y,z)=>if y is number then "参考" else "缺考",{"2次"})

将“缺考”改成z，返回结果一样。也就是说一定条件下替换时可以不去引用所有的xyz。

下面的代码第2次考试中有成绩的改成999，没有成绩的改成“缺考”。其中z代表第三参数“缺考”的文本，y代表999，x代表第2次那一列(因为最后一个参数为{"2次"}，代表要在2次那列里去判断，所以x就代表第2次那一列的值)每个要去判断是否要替换的值。替换逻辑是：x如果是null(也就是第2次那列是null)，就返回z(“缺考”)，否则返回y(999)。

= Table.ReplaceValue(Source,999,"缺考",(x,y,z)=>if x is null then z else y,{"2次"})

下面的代码中y=“不及格”，z=“缺考”，x代表1次、2次那两列中要判断是否替换的值。判断替换的逻辑是：如果x(第1次、2次中的值)是null就返回z(缺考)。如果不是null，如果x大于等于60就替换成“及格”，否则替换成y(不及格)。

= Table.ReplaceValue(Source,"不及格","缺考",(x,y,z)=>if x is null then z else if x >=60 then "及格" else y,{"1次","2次"})

下面再看个判断是否及格的例子，再次说明可以不引用y、z。有如下成绩表，科目大于等于60的返回T，小于60不及格的返回F。

具体代码如下，根据x的值是否大于等于60返回T或F。T和F具体在(x,y,z)=>函数规则中作出判断，y设置成0，z设置成1，或者其他的任何值都不影响结果。同时，即使引用y、z也不必原封不动的返回y和z，因为y和z可能是List、Table等(示例二中有说明)。

= Table.ReplaceValue(Source,0,1,(x,y,z)=>if x>=60 then "T" else "F",List.Skip(Table.ColumnNames(Source)))

示例二：两个表之间内容的替换

如下图，有一个仓库编号和仓库名称的对应表如下，定义为“对应表”：

有一个包含仓库编号、数量的表格如下，定义为“替换表”。要求将第一列的仓库编号替换成仓库名称。

替换代码如下：

= Table.ReplaceValue(替换表,each [店仓编号],对应表,(x,y,z)=>z[店仓名称]{List.PositionOf(z[店仓编号],x)},{"店仓编号"})

这里的y代表第2参数each [店仓编号]，z代表“对应表”，x代表的是替换表中的【店仓编号】列内容。替换逻辑是：分别判断x在z表的【店仓编号】列的位置，然后返回z表的【店仓名称】对应位置的值。

由于没引用到y，因此y设置成任意值，例如999等，都不影响替换结果：

示例三：将null填充为合计值

如下图，不同仓库数量有汇总行的位置，但是没有合计数，要求将【数量】列中的null替换为该仓库的合计数量。

首先，用Table.Group函数按仓库合并起来，并返回数量合计(表名定义为Group)。

= Table.Group(Source,"店仓编号",{"New",each List.Sum(_[数量])})

然后替换逻辑就和示例二中很相似了，将Group表中的数量填充到原表最后一列的null中即可，代码如下：

= Table.ReplaceValue(Source,each [店仓编号],Group,(x,y,z)=>if x is null then z[New]{List.PositionOf(z[店仓编号],y)} else x,{"数量"})

Table.ReplaceValue函数也属于一个相对强的函数了，在某些时候使用恰当的话能起到奇效。