Table.ReplaceValue

畅心 / 2017年8月9日2017年8月10日 / Table类

官方说明：

在table的指定列中将oldValue替换为newValue。
Table.ReplaceValue( table as table, oldValue as any, newValue as any, replacer as function, columnsToSearch as list) as table

解读：

该函数为表的替换函数，简单的说就是把表中指定的对象替换为指定的值。

选中[二级]列后点击转换-替换值，把null替换为"一级"：

生成的公式为：
= Table.ReplaceValue(源,null,"一级",Replacer.ReplaceValue,{"二级"})

若是对多列或全表所有列进行替换则指定第五参数即可。
= Table.ReplaceValue(源,null,"一级",Replacer.ReplaceValue,{"一级", "二级", "三级"})

那么问题来了，如果我要将二级列为null的填充为一级列的值咋办？
由于是替换为第一列的值，看语法第三参数是替换为的值可以是any对象，那能不能是相对引用呢？
联想添加列会有上下文针对每一行对象的引用，于是我尝试着写：
= Table.ReplaceValue(源,null,each [一级],Replacer.ReplaceValue,{"二级"})

居然成功了！

由于替换可以针对多列进行，于是我又改了第五参数：
= Table.ReplaceValue(源,null,each [一级],Replacer.ReplaceValue,{"二级","三级"})

再来看第四参数，类型又是function，默认的有Replacer.ReplaceValue和Replacer.ReplaceText两种。
两个函数的选择主要看作用对象的类型，比如上面是替换null，而null不是text，所以只能用replacevalue。
但如果现在是要把[一级]中的"南"替换为"北"，当勾选第二张图中高级选项中的"单元格匹配"时，会使用replacevalue，也就是只有作用对象="南"时才会替换，而图中没有值等于"南"，不会发生任何作用。
如果不勾选，则使用的是replacetext，只要文本中包含"南"，就会被替换成"北"，所以"湖南"会被替换为"湖北"。

类型为function意味着我们又可以对其改造，先看下语法结构：
Replacer.ReplaceValue( value as any, old as any, new as any) as any
需要3个参数，分别为作用对象、待替换值、替换为的值，于是乎有了下面的改写：
= Table.ReplaceValue(源,null,each [一级],(value,old,new)=>if value=old then new else value,{"二级"})
或者简单点就用xyz来表示：
= Table.ReplaceValue(源,null,each [一级],(x,y,z)=>if x is null then z else x,{"二级"})

有了这个思路，我们可以使用各种其他函数对x也就是作用对象进行条件限定，实现一些复杂条件下的替换，就不一一举例了。

打赏赞(28)

39 Replies to “Table.ReplaceValue”

Leo说道：

2018年3月10日上午9:38

这个函数强大到没朋友，第二、第三参数可以为any，对待替换和替换后的内容不设限，第四参数支持私人定制的条件(公式)，第五参数又支持一列或多列，大大滴提高了自由度:
比如，修改某个指定"单元格"(姑且这么叫吧)的值；
按默认规则修改某一列或某几列的值；
按私人定制的规则修改某一列或某几列的值；
待修改的这一列竟然还可以是本尊……
多谢博主！

回复
凌晨说道：

2018年3月21日下午4:13

请问，如果想将一列中的多个不同的文本同时替换成相应的内容，应该怎么写呢

回复
畅心说道：

2018年3月21日下午5:19

Table.ReplaceValue(源,{"A","B"},"畅心",(x,y,z)=>if List.Contains(y,x) then z else x,{"对象"})

回复
1. 李穆说道：
  
  2018年5月11日上午8:59
  
  大神帮帮我看看，公式是这样，= Table.ReplaceValue(删除的列1,{"1、本区域内共有可控终端门店","家"},"",(x,y,z)=>if List.Contains(x,y) then z else x,{"可控终端数"})
  
  但是出现错误提示，Expression.Error: 无法将值 "　　1、本区域内共有可控终端门店 ..." 转换为类型 List。
  详细信息:
  Value=　　1、本区域内共有可控终端门店 300 家
  Type=Type
  
  回复
  1. 畅心说道：
    
    2018年5月11日上午10:54
    
    List.Contains(y,x) x与y写反了
    
    回复
    1. 李穆说道：
      
      2018年5月13日下午9:41
      
      大神，这样X,Y换了，没有报错，但是也没有替换啊，我就是想一次性替换多个字符，不新增列
      
      = Table.ReplaceValue(删除的列1,{"1、本区域内共有可控终端门店","家"},"",(x,y,z)=>if List.Contains(y,x) then z else x,{"可控终端数"})
      
      这个公式没有报错，也没有别的任何变化，就跟没有这公式一样
      
      回复
      1. 畅心说道：
        
        2018年5月14日上午8:43
        
        同一对象的多次替换需要迭代，可以使用List.Accumulate,有兴趣可以来QQ群545814382讨论。
      2. vicki说道：
        
        2021年5月10日上午9:31
        
        我也常用这个ReplaceValue，发现它如果二三参数写的不正确时候，是不会和一般函数一样报错的，而是直接返回原表……
2. wangwb说道：
  
  2019年5月6日下午6:15
  
  这个是不是要先定义xyz，能给个完整的吗
  
  回复
3. 赵涵熙说道：
  
  2020年10月10日上午9:41
  
  如果我想把A 换成AX, B换成BX，也就是换成不同的值，应该怎么写呢
  
  回复
  1. 畅心说道：
    
    2020年10月11日下午12:44
    
    你这种多个对应的替换，肯定有个替换对照表，有这个对照表用不用replace也无所谓了，直接查找就可以，如：List.Select({{对象1,替换为1},{对象2,替换2},{对象3,替换3}},each _{0}=对象){0}{1}
    
    回复
    1. 赵涵熙说道：
      
      2020年10月13日上午11:57
      
      尝试了一下
      已添加条件列=List.Select({{"2088",20},{"3051C",1},{"3051D",33},{"3051SC",8},{"3051ST",10},{"3051T",6}},each _{0}=[Category]){0}{1}
      如下报错
      Expression.Error: 无法对类型 List 应用字段访问。
      详细信息:
      Value=[List]
      Key=Category
      
      回复
      1. 畅心说道：
        
        2020年10月13日下午2:02
        
        List.Select({{"2088",20},{"3051C",1},{"3051D",33},{"3051SC",8},{"3051ST",10},{"3051T",6}},(x)=>x{0}=[Category]){0}{1}
余波说道：

2018年5月10日下午2:20

非常好！

回复
余波说道：

2018年5月10日下午2:22

请教一下：如果我想把一列文本类型的字段中字符长度小于7的，前面+“0”，否则保留原数据，请问怎么写函数？谢谢！

回复
畅心说道：

2018年5月10日下午2:39

Table.TransformColumns(表,{"需要处理的列",each (if Text.Length(_)<7 then Text.PadStart(_,7,"0") else _)})

回复
余波说道：

2018年5月10日下午2:49

_是表示需要处理的列吗？谢谢！

回复
余波说道：

2018年5月10日下午3:02

Table.TransformColumns(替换的值35,{[商品编号],each (if Text.Length([商品编码])<8 then Text.PadStart([商品编码],7,"0") else [商品编码])})
是这样吗？谢谢！好象不行，提示有未知标识符

回复
1. 畅心说道：
  
  2018年5月10日下午3:04
  
  Table.TransformColumns(替换的值35,{"商品编号",each (if Text.Length(_)<8 then Text.PadStart(_,7,"0") else _)})
  
  回复
  1. Vivian说道：
    
    2018年8月21日下午3:08
    
    请问大神，我想套用这个公式，对特定一列中文本长度大于5的只保留前2位，第三位改为W，但是系统提示出错，原因是求值期间遇到循环引用，请问是那里有问题呢？
    公式是这样的：Table.TransformColumns(表1,{"后缀3",each (if Text.Length(_)>5 then Text.PadStart(_,2,"W") else _)})
    
    回复
余波说道：

2018年5月10日下午3:11

OK！成功了！非常感谢！

回复
CherishT说道：

2018年10月29日下午8:31

这个函数我有点不太懂，主函数Table.ReplaceValue第二第三参数都已经规定好了old text和new text，第四个参数Replacer.RepalceText(text,old,new)三个参数的意义是什么，它想要达到的效果主函数已经完成了。如果这三个参数与主函数有冲突会是什么结果？

回复
1. 畅心说道：
  
  2019年5月6日下午6:50
  
  默认替换函数处理这三个参数，如果你换成自定义的函数将按照你的意思进行替换，而不仅仅限于把A里面的B换成C，有可能处理重组，能更细化处理参数。
  
  回复
畅心说道：

2019年5月6日下午6:41

那个是默认的替换函数，如果你用自定义函数接入三个传过来的参数你就可以自己发挥怎么替换你做主。

回复
韩先生说道：

2020年5月2日上午9:58

所有列，null值被替换为固定支付，比如“O”。公式该怎么写？

回复
1. 畅心说道：
  
  2020年5月5日上午10:47
  
  Table.TransformColums(表,{},each if _ is null then "O" else _)
  
  回复
Bill Yang说道：

2020年6月8日下午3:59

Table.ReplaceValue, 第5个参数如何设定为整个工作表的列，即工作表的列是在Power Query转置过来的，转置后其列数，列名是不固定的？有哪位大神能帮忙解出这个难题吗。

回复
1. vicki说道：
  
  2021年5月10日上午9:27
  
  动态取列名？如果不是嵌套在中间层公式里，就太简单了：Table.ColumnNames(你上一个步骤的名字）
  
  回复
李建华说道：

2020年6月9日上午11:02

我想问一下，我有一个表里面有些列里面有空值，我用替换值把他们全部都替换成0了，但是如果我后面数据新增了列里面的空值它并不会自动替换成0，要手动重新选择新列才可以，有什么办法了让他新增列如果有空值的话能自动替换成0。

回复
1. 慕容君少说道：
  
  2020年6月12日下午3:45
  
  如果列不是确定的话，就不能再用Table.ReplaceValue了，应该用Table.TransformColumns
  如下:
  
  let
  源 = #table({"a","b"},{{1,null},{null,2},{3,null},{null,4}}),
  转换 = Table.TransformColumns(源,{},each if _=null then 0 else _)
  in
  转换
  
  回复
  1. vicki说道：
    
    2021年5月10日上午9:28
    
    这个空list用法真妙！
    
    回复
lala说道：

2020年8月24日下午5:29

请问通过判断 Column a 的值改变Column b的值，例如当a=124时，b=“集团”，其他的保留原来b的值

回复
1. 魅影说道：
  
  2022年10月21日上午11:13
  
  Table.ReplaceValue(源,null,each [Column a],(x,y,z)=>if z=124 then x="集团" else x,{Column b} )
  
  回复
海帆说道：

2021年2月9日下午10:35

这个函数能不能实现：根据“纠错经验表”，修改不规范表[不规范列n]中的不规范记录，然后返规范表

链接：https://pan.baidu.com/s/1_CkR46blJ6L3cRQJjeU-2A
提取码：42ud

回复
安吉拉说道：

2021年4月12日下午1:43

请问若是替换多列中的值如何可操作吗?
比如有个list{100,200,300 }作为替换后的新值,条件是若表中列名[item]=code 则分别替换表中的列金额1,金额2,金额3各列的值为 100,200,300 ?如下Table.ReplaceValue 转换不成功,请问书写是否正确?若不不行,那么如何批量替换一行中多个列数值呀?

Table.ReplaceValue( 源, each [item] , each {100,200,300}, ( x,y,z) => if y=code then List.ReplaceMatchingItems(x, {{x{0}, z{0}}, {x{1}, z{1}}, {x{2}, z{2}} }, ) else x , {"金额1","金额2","金额3" } )

回复
Chalet Lee说道：

2021年4月16日下午5:24

Comments里提到多个值替换成多个值用List.Select是不是有错误？
List.Select({{"2088",20},{"3051C",1},{"3051D",33},{"3051SC",8},{"3051ST",10},{"3051T",6}},(x)=>x{0}=[Category]){0}{1}
这行函数语句里并没有提到上一个步骤，那怎么替换呢？结尾的“{0}{1}”并不是很理解是什么意思

回复
1. vicki说道：
  
  2021年5月10日上午9:24
  
  Select结果是一个双层新旧值对照list，{0}剥去外衣，{1}获得匹配的New值。
  [Category]这里就是对上个步骤内容的引用了，但是你肯定不是在一个表处理过程中直接写一个List函数啊，这一套完全可以嵌在Table.AddColumns里面，然后注意在适当地方加上步骤名就好了
  
  回复
Chalet Lee说道：

2021年4月16日下午5:26

我替换的话肯定是针对上一个步骤中的某列进行替换，但是List.Select中并没有提到上一个步骤

回复
卖女孩的小火柴说道：

2022年10月11日下午12:17

基于这个思路，可以自定义一些通用型的Replace函数，例如按两个列表的映射关系进行替换：
let
ReplaceWithListMap =
(text as nullable text, old as list, new as list) =>
let position = List.PositionOf(old, text) in if position = -1 then text else new{position}
in
ReplaceWithListMap
之后就可以在List/Table.ReplaceValue中调用：
Table.ReplaceValue(Table, MappingTable[old] , MappingTable[new], ReplaceWithListMap,{"old"})

回复

官方说明：

解读：

39 Replies to “Table.ReplaceValue”

回复 余波 取消回复

回复余波取消回复