Merqe Queries with text.contains Power BI

Question

i have to queries which I want to merge.

these are two sample columns i want to compare.

i need to find the string from the second list, in the first one. If there is a match, append a list with the matching string to the first list.

Those lists are from two different queries

Answer 1

Those lists are from two different queries

That's fine, you can reference another query using its name. That could be Source1 and Source2 below.

need to find the string from the second list, in the first on append a list with the matching string to the first list.

I wasn't sure if you meant

forall B in A =>
    Text.Combine( {_} & {A}, "_")

I thought you meant to accumulate a list, that's what I did.

let
    json1 = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wysw1NjIxNTMz0isoKlGK1QGJmJqhiiQnlsRD1SGpAvLj0TSiG1Wc9mH+hI1pWUBxHCowLcNqEUF7oBJA67YZG5sCqQ7jrDSIfCwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [File = _t]),
    Source1 = Table.TransformColumnTypes(json1,{{"File", type text}}),

    json2 = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSkzLyjEyNskyVYrViVYyNjKBMMzMjMD0h/kTtsEYHWBGZi6YgqlTio0FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Substring = _t]),
    Source2 = Table.TransformColumnTypes(json2,{{"Substring", type text}}),

    /*
    find all matching values in the source text
    then merge the matching values with separator
    
    optional params with defaults:
    
        separator: string to join on, "_" by default
        comparer: case sensitivity, off by default
    */
    SummarizeMatchingText = (source as text, patterns as list, optional options as nullable record) as any =>
        let             
            separator = options[separator]? ?? "_",
            comparer = options[comparer]? ?? Comparer.OrdinalIgnoreCase,
            select_matching = List.Select(
                patterns,
                (item) =>
                    Text.Contains( source, item, comparer)
            ),
            merged = Text.Combine( select_matching, separator)            
        in 
            merged,

    col_matches = Table.AddColumn(
        Source1, 
        "Matches",
        (row) =>
            SummarizeMatchingText(
                row[File], Source2[Substring],
                [ separator = "—" ]
            ),
        Text.Type
    ),

    // summarize multiple steps
    Summary = [
        Source1 = Source1,
        Source2 = Source2,
        col_matches = col_matches,
        expectEmpty = SummarizeMatchingText(
            "sf?fj324", Source2[Substring]  ),

        expectMatch = SummarizeMatchingText(
            "cat_im32456.prt", Source2[Substring]  )
    ],
    col_matches1 = Summary[col_matches]
in
    col_matches1