看不懂系列:Table.ReplaceValue函數

一、基礎介紹

Table.ReplaceValue函數實現的功能是將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函數也屬於一個相對強的函數了，在某些時候使用恰當的話能起到奇效。
作者：超人Clark
原載：超人一籌高效辦公