从另一个列表中的一个列表中查找字符串并返回找到的字符串 [英] Find String from One List within Another List and Return String Found

问题描述

我在 Matchlists/tables in power query，但我需要更多.

I found part of what I was looking for at Matchlists/tables in power query, but I need a bit more.

使用 Matchlists/tables in power query 中提供的仅标志"示例，我正在比较两个列表 ListA 和 ListB，以检查 ListB 的行内容是否出现在 ListA 的行内容中.我不能对两行的内容进行一对一的匹配(例如使用 List.Intersect)，因为 ListB 中一行的内容可能只是 part 的内容ListA 中的一行.

Using the "Flags only" example provided at Matchlists/tables in power query, I’m comparing two lists, ListA and ListB, to check if ListB’s row content appears in ListA’s row content at all. I can’t do a one-for-one match of both rows’ contents (like with List.Intersect) because the content of a row in ListB might only be part of the content of a row in ListA.

请注意，在下面的查询中，ListB 包含roo"，它是单词 room 中的前三个字母.我想知道roo"在 ListA 的in my room"所在的行中.

Note that, in the query below, ListB includes "roo", which is the first three letters in the word room. I would want to know that "roo" is in ListA’s row that has "in my room."

Matchlists/tables in power query 已经提供了仅标志"示例确定roo"是 ListA 的in my room"行的一部分.当 ListA 和 ListB 之间存在这样的匹配时，我在示例的基础上分配是"而不是 true.

The "Flags only" example provided by Matchlists/tables in power query already determines that "roo" is part of ListA’s row that has "in my room." I built on the example to assign "yes," instead of true when there is such a match between the ListA and ListB.

我想做的是用 ListB 中的实际值替换yes"——例如，值roo".我试图简单地将 wordB 替换为yes"，但我收到了 wordB 无法识别的错误.

What I’d like to do is to replace "yes" with the actual value from ListB — the value "roo," for instance. I tried to simply substitute wordB for "yes" but I got an error that wordB wasn’t recognized.

let
    ListA = {"help me rhonda",  "in my room", "good vibrations", "god only knows"},
    ListB = {"roo", "me", "only"},
    contains_word=List.Transform(ListA, (lineA)=>if List.MatchesAny(ListB, (wordB)=>Text.Contains(lineA, wordB)) = true then "yes" else "no")
in
    contains_word

当前查询结果如下:

    List
1   yes
2   yes
3   no
4   yes

我希望查询结果是:

    List
1   roo
2   me
3   
4   only

知道怎么做吗?

(p.s. 我对 Power Query/M 非常陌生)

(p.s. I'm extremely new to Power Query / M)

谢谢

推荐答案

我会这样做:

let
    ListA = {"help me rhonda",  "in my room", "good vibrations", "god only knows"},
    ListB = {"roo", "me", "only"},
    contains_word=List.Transform(ListA, (lineA)=>List.Select(List.Transform(ListB, (wordB)=>if Text.Contains(lineA, wordB) = true then wordB else null), (x)=>x <> null){0}?)
in
    contains_word

这个想法是使用 List.Transform 两次:内部一次更改列表 B 以仅保留匹配的值.然后最新的第一个非 null 替换列表 A(外部 List.Tramsform)中的字符串.

The idea is to use List.Transform twice: inner one changes list B to leave only matching values. Then 1st non-null of latest replaces string from list A (outer List.Tramsform).

这篇关于从另一个列表中的一个列表中查找字符串并返回找到的字符串的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！