英文:
Is it possible to merge two tables in Power Query Editor (Power BI) with Python fuzzy matching?
问题
合并两个表格在Power Query Editor（Power BI）中基于字符串相似性与Python
考虑下面的表格：
Table1
| 名称 | ... |
|---|---|
| 苹果水果 A11 | ... |
| 香蕉水果 B12 | ... |
| ... | ... |
Table2
| 名称 | 值 |
|---|---|
| 苹果 A11R/T | 40 |
| B4n4n4 Fruit B12_T | 50 |
| 莓果 A11 | 60 |
| ... | ... |
我想要从Table2中获取Table1的值。但出于某种原因，当我使用内置的Power Query Editor进行模糊匹配时，它会将苹果水果 A11与莓果 A11匹配，而不是苹果 A11 R/T。我已阅读文档，文档表示内置函数最适用于单词。我尝试从**Table1[名称]和Table2[名称]**中删除空格，但结果并没有改善。
我尝试查找解决方案，但尚未找到。是否有一种使用Python来解决这个问题的方法？或者是否有更简单的解决方案？
我期望的结果：
Table1
| 名称 | ... | Table2.名称 | Table2.值 |
|---|---|---|---|
| 苹果水果 A11 | ... | 苹果 A11R/T | 40 |
| 香蕉水果 B12 | ... | B4n4n4 Fruit B12_T | 50 |
| ... | ... | ... | ... |
--- 由于某种原因，表格未像预览中那样显示，这就是为什么每个表格都有图像的原因。 免责声明：上述表格中的数据仅是我正在处理的数据模式的示例。对于示例数据，模糊匹配可能会产生正确的结果。
英文:
Merge two tables in power query editor (Power BI) based on string similarity with Python
Consider the tables bellow:
Table1
| Name | ... |
|---|---|
| Apple Fruit A11 | ... |
| Banana Fruit B12 | ... |
| ... | ... |
Table2
| Name | Value |
|---|---|
| Apple A11R/T | 40 |
| B4n4n4 Fruit B12_T | 50 |
| Berry A11 | 60 |
| ... | ... |
I want to get the Value from Table2 into Table1. But for some reason when I use the built-in power query editor merge with fuzzy matching. It will match Apple Fruit A11 with Berry A11 instead of Apple A11 R/T. I've read the documentation, and it says that the built-in function works best with single words. I tried to remove spaces both from Table1[Name] and Table2[Name] but it didn't improve results.
I looked around trying to find a solution, but wasn't able to figure out yet. Is there a way to do this using python? Or is there a simpler solution?
The results that I am expecting:
Table1
| Name | ... | Table2.Name | Table2.Value |
|---|---|---|---|
| Apple Fruit A11 | ... | Apple A11R/T | 40 |
| Banana Fruit B12 | ... | B4n4n4 Fruit B12_T | 50 |
| ... | ... | ... | ... |
--- For some reason the tables are not showing up like the preview, that's why there are also images for each table. Disclaimer: The data present in the tables above is just an example of the pattern of the data that I am working with. And fuzzy matching will probably give the right results for the example data.
答案1
得分: 2
模糊匹配在Power Query中对我有效。
设置您的选项如下：
英文:
Fuzzy matching in Power Query works fine for me.
Set your options to the following:
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论