Power Query 密碼轉換的問題

QQ群裡看到一個問題，要求根據數找出對應的字母：

每個數字在4個分割槽中匹配最接近的值，然後找出這個值對應的位置，前後各一個字串。

在數字匹配時需要考慮分割槽匹配，字串選擇時可以忽略分割槽。

以上就是這個問題的具體要求。

使用Power Query來處理，上面的資料排列方式不是很方便，需要先稍作整理：

密碼錶：

明碼暗碼我給弄反了，不想改了，就這麼看吧。

原資料：

新增索引後逆透視，新增索引是為了出結果後恢復原來的排列方式，逆透視後我們只要寫一次公式就可以了。

第一步：計算絕對值

我們計算原資料中的每個值與密碼錶的明碼之間的絕對值：

t=Table.AddColumn(密碼錶,"a",(x)=>Number.Abs([值]-x[明碼]))

第二步：計算區域最小值

我們用Table.Group函式計算出每個區域對應的最小值：

t1=Table.Group(t,{"區域"},{{"b",(y)=>List.Min(y[a])}})

第三步：合併表格

把t、t1兩個表格用區域以及絕對值列匹配合並表格：

t2=Table.NestedJoin(t,{"區域","a"},t1,{"區域","b"},"c")

第四步：展開最小值列

在t2表中展開c列表中的b列，得到我們在分組時得到分割槽域最小絕對值：

t3=Table.ExpandTableColumn(t2,"c",{"b"},{"b"})

第五步：提取對應的暗碼

這一步要麻煩一點，因為要考慮取值的過程中出現角標越界的問題，對應絕對值最小值位置，在暗碼列前後取值，有可能出現取值在第一個值之前或最後一個值之後的情況，這時就會報錯，下標越界。

tx=List.Transform({1..List.Count(t3[b])},(x)=>if t3[b]{x-1}<>null then List.RemoveNulls({if x-2<0 then null else t3[暗碼]{x-2},if x=List.Count(t3[b]) then null else t3[暗碼]{x}}) else null),

第六步：合併文字

在取值的過程中有很多的null，我們需要去除null之後然後合併文字：

re=Text.Combine(List.Combine(List.Zip(List.RemoveNulls(tx))),",")

如果要每個格子一個字串也不難，資料排列變來變去本來就是Power Query的強項：

我們用索引列分組，所有行，Text.Combine合併暗碼列，然後再分列就可以了：

我是在Power BI Desktop中做的，載入後看看結果：

上面的過程放到Excel中是一樣的。

這個問題的解決過程與之前我們寫的Power Query新增列計算稍有不同：

我們在自定義列的時候，使用let，in相當於好多步驟的表格處理過程當成了一個函式，沒有引數的函式，直接獲得結果。

是多個關於密碼錶操作的步驟合併到一起，我們當然也可以在外部定義一個函式，在這裡直接引用，我們來嘗試一下：

首先把上面的步驟複製下來，定義一個函式：

把原來的值用引數value替換。

然後我們來引用這個函式：

就能得到同樣的結果。

附上資料檔案：

連結: https://pan.baidu.com/s/1AvAUiardC88VRp2lOOkR_Q 提取碼: d32e