在电源查询中使用文本字符串查找所有代码组合

发布于 2025-01-19 02:37:22 字数 1087 浏览 3 评论 0原文

我需要的是使用文本字符串（A2 中的长文本字符串）从代码（表 1）中查找所有代码组合，并获取结果表（表 2）中的所有组合。例如，您有字符串的 CE1 部分，并且可能会出现多种组合，如您在结果中看到的

那样...我在 Power Query 中尝试过，但我无法弄清楚当它具有逗号值时如何在行级别上查找CE1、CE2、CE3、CE4 或其他。就我而言，它只能匹配一个第一个值，但不能匹配整个字符串。

我希望在 Power Query 中找到解决方案（如果可能的话）！

这是公式解决方案：

=UNIQUE(FILTER(Table3[Ausgang];LET(X;TRANSPOSE(FILTERXML("<t><s>"&SUBSTITUTE(SUBSTITUTE(A2;",";"_");"_";"</s><s>")&"</s></t>";"//s"));MMULT(IFERROR(FIND(","&X&",";","&Table3[Ausgang]&",");0);SEQUENCE(COUNTA(X);;;0)));"None Found"))

https://docs.google.com/spreadsheets/d/1bg7aodiJb-A1hOBUlBxc-25ERn2Pn5bY/edit?usp=sharing&ouid=101738555398870704584&rtpof=true&sd=true

原文

What I need is to find all the code combinations from Codes (table 1) using text string (long text string in A2) and to get all the combinations like on the Results table (table 2). For instance, you have CE1 part of string, and it can happen that has many combinations as you can see in Results...

I tried in Power Query but i cant figure out how to look for on row level when it has comma values like CE1,CE2,CE3,CE4 or else. In my case it can only match one and first value but not the whole string.

I would like to have solution in Power Query (when it is possible)!

This is formula solution:

=UNIQUE(FILTER(Table3[Ausgang];LET(X;TRANSPOSE(FILTERXML("<t><s>"&SUBSTITUTE(SUBSTITUTE(A2;",";"_");"_";"</s><s>")&"</s></t>";"//s"));MMULT(IFERROR(FIND(","&X&",";","&Table3[Ausgang]&",");0);SEQUENCE(COUNTA(X);;;0)));"None Found"))

https://docs.google.com/spreadsheets/d/1bg7aodiJb-A1hOBUlBxc-25ERn2Pn5bY/edit?usp=sharing&ouid=101738555398870704584&rtpof=true&sd=true

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

始于初秋 2025-01-26 02:37:22

在 powerquery 中，将查找单元加载到名为 Table3 的查询中（data..from table/range [x]columns ），然后关闭文件并加载。假设列名为 String

加载 T1 表（数据 .. 来自表/范围 [x] 列）并添加列 .. 自定义列 ... 使用公式，

=List.ContainsAny(Text.Split([Codes],","),Text.Split(Table.ReplaceValue(Table3,",","_",Replacer.ReplaceText,{"String"})[String]{0},"_"))

然后使用列顶部的箭头过滤 TRUE并右键单击以删除额外的列示例

完整代码，将其更好地拆分如下。假设其他查询名为 Table3，列为 String。将 T_1 替换为 powerquery 为表提供的名称，

let Source = Excel.CurrentWorkbook(){[Name="T_1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Codes", type text}}),
FindList = Text.Split(Table.ReplaceValue(Table3,",","_",Replacer.ReplaceText,{"String"})[String]{0},"_"),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each List.ContainsAny(Text.Split([Codes],","),FindList)),
#"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([Custom] = true)),
#"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Custom"})
in  #"Removed Columns"

技巧是 (a) 将 , 替换为源单元格中的 _ ，然后使用 Text.Split 使列表成为列表 (b) 对每个列表执行相同操作第二个表中的行，并使用 List.ContainsAny 查找匹配项

In powerquery, load your lookup cell into query named Table3 (data..from table/range [x]columns ) then file close and load. Assumes column is named String

Load your T1 table (data .. from table/range [x] columns ) and add column .. custom column ... with formula

=List.ContainsAny(Text.Split([Codes],","),Text.Split(Table.ReplaceValue(Table3,",","_",Replacer.ReplaceText,{"String"})[String]{0},"_"))

then use arrow atop column to filter for TRUE and right click to remove extra column

Sample full code, splitting that out a bit better is below. Assumes other query is named Table3 with column String. Replace T_1 with the name that powerquery gives to your table

let Source = Excel.CurrentWorkbook(){[Name="T_1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Codes", type text}}),
FindList = Text.Split(Table.ReplaceValue(Table3,",","_",Replacer.ReplaceText,{"String"})[String]{0},"_"),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each List.ContainsAny(Text.Split([Codes],","),FindList)),
#"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([Custom] = true)),
#"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Custom"})
in  #"Removed Columns"

the trick is to (a) replace , with _ in the source cell then Text.Split to make that a list (b) do the same with each row in the second table, and use List.ContainsAny to look for matches

回复收藏 0 原文

~没有更多了~