数据不规范？Power Query中这个M函数你应该掌握
文/Davis 全栈BI工程师，数据分析爱好者，微软MCSE
Table.AddFuzzyClusterColumn是Power Query的表函数之一，它可以对数据进行模糊匹配并分组，从而规范数据源中的数据，什么意思呢？
一个简单的例子，比如地名“北京”，在数据源中它可能是“北京”，“北京市”，“Beijing”甚至“北平”，而该函数需要解决的，就是由数据录入不规范，数据本身的标准不统一等原因导致的这种数据杂乱的问题。
为解决此问题，多数情况下，简单替换的方法显然不切实际，而该M函数可以使其规范化，这在数据清洗中特别实用。
幸运的是，自PowerBI Desktop上次更新（2020年8月）以来，已增加了对该函数的支持，下文讲解其具体用法。
基本用法
对于基本用法，我想引用文档中的举例的数据进行说明。首先我们有一个简单的表，里面包含英文城市温哥华和西雅图（如下），你可以留意到其中的单词大小写不一，且存在拼写错误（如seattl）:
现在，我们回到Table.AddFuzzyClusterColumn函数，其用法如下：
Table.AddFuzzyClusterColumn（
源表， //【必要】
目标列列名， //【必要】
规整后的列名， //【必要】
其他参数 //【可选】
）
因此，针对以上数据，添加步骤，编写M语句如下：
= Table.AddFuzzyClusterColumn(
#"Changed Type",
"Location",
"Location_Cleaned"
)
这样，我们就可以得到如下结果，数据得到完美规范：
是不是很厉害！
上面只是基本的用法，利用该函数的可选参数，还有更强大的功能。
进阶用法
在进阶用法中，会在基本用法的基础上讲解参数的原理和使用方法，此外，处理的数据也改为文档中未涉及的中文文本。
首先利用以下M语句模拟一个火车站站名不规范的简易数据表：
--------
let
DATA =
Table.FromRecords(
{
[ID = 1, Location = "北京西站"],
[ID = 2, Location = "北京西站"],
[ID = 3, Location = "广州东站"],
[ID = 4, Location = "广州东站"],
[ID = 5, Location = "广州东火车站"],
[ID = 6, Location = "西九龙站"],
[ID = 7, Location = "西九龙站"],
[ID = 8, Location = "西九龙火车站"],
[ID = 9, Location = "香港西九龙站"],
[ID = 10, Location = "HK West Kowloon Railway Station"]
},
type table [ID = nullable number, Location = nullable text]
)
in
DATA
--------
数据如下，当然你也可以手动先做好这个数据：
按上文使用Table.AddFuzzyClusterColumn的方式，此处使用第一个可选参数【Culture】。
文档对该参数的定义是“允许根据区域性特定规则对记录进行分组”，其实就是该函数默认处理对象是英文字符，如果是其他语言，就应该使用Culture指明要处理的语言。
于是编写M代码如下：
= Table.AddFuzzyClusterColumn(
DATA,//源表名
"Location",
"Location_Cleaned",
[Culture="cn-ZH"]
)
这样我们得到如下效果：
现在北京西站这个名称得到了规范，空格问题得到处理。
但如何处理“广州东站zhan”这种混杂了拼音的记录呢？答案是使用强大的【Threshold】参数。
Threshold参数的范围为0到1.0，默认值是0.8，数值为1代表原数据不做任何处理，其越低代表其在纠正数据时，对数据本身的容错率越高。
此处将该参数设定为0.6后就解决了此问题。
注：此处你还可以增加IgnoreSpace= true参数显式地忽略空格。
= Table.AddFuzzyClusterColumn(
DATA,
"Location",
"Location_Cleaned",
[
Culture="cn-ZH",
Threshold=0.6
]
)
效果如下：
到此我们发现以上方法，还未能解决表中第六行至第十行的命名杂乱问题，PQ没有足够的依据去智能地给他们归类，因为PQ并不清楚西九龙是香港的一个地名，当然更不可能了解"HKWest Kowloon Railway Station"的含义。
为解决此问题，该函数引入了【TransformationTable】参数，这将允许我们自定义一个转换表，以使得PQ可以按照转换表的定义来规范数据，从而彻底解决此类问题。
首先，定义转换表TRANS_TABLE：
= Table.FromRecords(
{
[From = "西九龙站", To = "香港西九龙站"],
[From = "西九龙火车站", To = "香港西九龙站"],
[From = "HK West Kowloon Railway Station", To = "香港西九龙站"]
},
type table [From = nullable text, To = nullable text]
)
转换表如下：
然后参数中引用该表：
= Table.AddFuzzyClusterColumn(
DATA,
"Location",
"Location_Cleaned",
[
Culture="cn-ZH",
Threshold=0.6,
TransformationTable=TRANS_TABLE
]
)
这样我们就完美地解决了问题。效果如下：
总结
Table.AddFuzzyClusterColumn函数在数据源不规范时十分有用，掌握它的用法就可以轻松处理这类问题，但对于企业BI解决方案而言，通过ETL等方式从源头上解决数据杂乱的问题才是最规范的做法。
-精彩推荐-
采悟 from Power BI 星球
