Power Query 密码转换的问题

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