Table.ReplaceValue

官方说明:

在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.ReplaceValueReplacer.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也就是作用对象进行条件限定,实现一些复杂条件下的替换,就不一一举例了。

39 Replies to “Table.ReplaceValue”

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

    1. 大神帮帮我看看,公式是这样,= 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. 大神,这样X,Y换了,没有报错,但是也没有替换啊,我就是想一次性替换多个字符,不新增列

          = Table.ReplaceValue(删除的列1,{"1、本区域内共有可控终端门店","家"},"",(x,y,z)=>if List.Contains(y,x) then z else x,{"可控终端数"})

          这个公式没有报错,也没有别的任何变化,就跟没有这公式一样

          1. 同一对象的多次替换需要迭代,可以使用List.Accumulate,有兴趣可以来QQ群545814382讨论。

          2. 我也常用这个ReplaceValue,发现它如果二三参数写的不正确时候,是不会和一般函数一样报错的,而是直接返回原表……

      1. 你这种多个对应的替换,肯定有个替换对照表,有这个对照表用不用replace也无所谓了,直接查找就可以,如:List.Select({{对象1,替换为1},{对象2,替换2},{对象3,替换3}},each _{0}=对象){0}{1}

        1. 尝试了一下
          已添加条件列=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. List.Select({{"2088",20},{"3051C",1},{"3051D",33},{"3051SC",8},{"3051ST",10},{"3051T",6}},(x)=>x{0}=[Category]){0}{1}

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

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

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

    1. Table.TransformColumns(替换的值35,{"商品编号",each (if Text.Length(_)<8 then Text.PadStart(_,7,"0") else _)})

      1. 请问大神,我想套用这个公式,对特定一列中文本长度大于5的只保留前2位,第三位改为W,但是系统提示出错,原因是求值期间遇到循环引用,请问是那里有问题呢?
        公式是这样的:Table.TransformColumns(表1,{"后缀3",each (if Text.Length(_)>5 then Text.PadStart(_,2,"W") else _)})

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

    1. 默认替换函数处理这三个参数,如果你换成自定义的函数将按照你的意思进行替换,而不仅仅限于把A里面的B换成C,有可能处理重组,能更细化处理参数。

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

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

    1. 动态取列名?如果不是嵌套在中间层公式里,就太简单了:Table.ColumnNames(你上一个步骤的名字)

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

    1. 如果列不是确定的话,就不能再用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
      转换

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

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

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

  11. 请问若是替换多列中的值如何可操作吗?
    比如有个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" } )

  12. 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. Select结果是一个双层新旧值对照list,{0}剥去外衣,{1}获得匹配的New值。
      [Category]这里就是对上个步骤内容的引用了,但是你肯定不是在一个表处理过程中直接写一个List函数啊,这一套完全可以嵌在Table.AddColumns里面,然后注意在适当地方加上步骤名就好了

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

  14. 基于这个思路,可以自定义一些通用型的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"})

发表回复

您的电子邮箱地址不会被公开。 必填项已用*标注