之前在《使用Power Query进行模糊匹配》一文中我们讨论了如何在Power Query中进行模糊匹配,今天我们来讨论下在Power Pivot中的模糊匹配。
还是之前的案例,图1为产品表,图2为颜色价格匹配表,需要根据颜色将颜色价格表中的价格匹配到产品表中,因为产品表中没有能与匹配表中的【颜色】字段直接匹配的列,所以我们需要用【产品】字段中的颜色进行模糊匹配。
我们先将两张表加载进Power BI中,分别命名为“产品”和“颜色价格”。
我们的思路是先在产品表中新建列【颜色】,获取到产品表中准确的颜色,然后再根据该字段去和颜色价格表中的颜色做关联,进而获取价格信息。
那么,怎么生成产品表中的【颜色】字段呢?有大神写出了如下公式:
颜色 = FIRSTNONBLANK(FILTER(VALUES('颜色价格'[颜色]),SEARCH('颜色价格'[颜色],'产品'[产品],1,0)),1)
现在我们来理解下这个公式。
其中Filter为筛选函数,筛选VALUES('颜色价格'[颜色])这张表。而VALUES('颜色价格'[颜色])返回的是颜色价格表中非重复的【颜色】列表,即Filter函数筛选的是下表,我们将其命名为颜色表。
那么Filter对颜色表的筛选条件是什么呢?
筛选条件是:
SEARCH('颜色价格'[颜色],'产品'[产品],1,0)
Search的用法如下:
SEARCH(<find_text>,<within_text>, [start_num])
以产品表中的第一行“ASD23809宝石红S码“来举例,公式即为:
FILTER(‘颜色’, SEARCH(‘颜色’[颜色], “ASD23809宝石红S码”,1,0))
即颜色表中的每一行都会遍历一遍,返回当前行的颜色在“ASD23809宝石红S码”这个字符串中的位置。
当返回的位置是0以上的数字时,代表“ASD23809宝石红S码”中含有这个颜色,该颜色就会被筛选出来;当返回的位置为0时,代表“ASD23809宝石红S码”中不包含这个颜色,便不会被筛选出来。
同理,产品表中的每二行、第三行…都会走一遍这个流程。
哎?有人可能会问,为什么不为0时就会被筛选出来,而为0时就不会被筛选出来呢?
关于这个问题,大家可以做个小测试,filter(表,0)返回一张空表,而filter(表,任意非零数字)则会返回整张表。所以,当上文提到的遍历‘颜色’表中的每一个颜色时,若search函数返回的是非0值,则这个颜色就会被筛选出来,否则不会被筛选出来。
外面嵌套的FIRSTNONBLANK函数返回当前上下文筛选列中的第一个不为空的值,即当”ASD23809宝石红S码“中包含两个颜色时,只取被筛选出来的第一个颜色。
因此,用此公式我们就可以在产品表中生成如下【颜色】字段。
但是……当我们想用此列与颜色价格表建立关系获取价格时,却被告有循环依赖关系。
其实这也比较好理解,毕竟我们是通过‘颜色价格’【颜色】得到的‘产品’【颜色】,现在再用这两列建关系,产生循环依赖也就不奇怪。那怎么办呢?
要解决这一问题,可以在查询编辑器中引用‘颜色价格’表,生成一个与之相同的辅助表,在生成‘产品’【颜色】字段时,利用这个辅助表生成,而建立关系时则选择‘颜色价格’这张表。
好啦,今天的分享就到这儿,有什么疑问或者建议文下留言呦~
* PowerPivot工坊原创文章,转载请注明出处!
延伸阅读:使用Power Query进行模糊匹配
组合产品销售模型分析(下篇)
组合产品销售模型分析(上篇)——新维度表的构建
Power BI 也能做这样的组合图?
在Power BI报表中创建超链接
柱形图根据数值自动改变颜色
如果您想深入学习微软Power BI,欢迎登录网易云课堂试听学习我们的“从Excel到Power BI数据分析可视化”系列课程。或者关注我们的公众号(PowerPivot工坊)后猛戳”在线学习”。
长按下方二维码关注“Power Pivot工坊”获取更多微软Power BI、PowerPivot相关文章、资讯,欢迎小伙伴儿们转发分享~