How to search a text column using a column list in another table and output first instance

[rchamberlain]

Hello -

This situation seems so simple, but I'm struggling to get it to work.

I have two seperate tables and need to search a column in Table 1 using a list of values from a column in Table 2.

Example:

In Table1 I have a column called "Tags" with the following values listed in each.

Table1[Tags]

Tags

condition-4; condition-4.2; condition-3

condition-9; condition-5; condition-10

condition-8; condition-7; condition-6

I'm trying to create a calculated column that searches this field using a column in Table2 like this.

Table2[Condition_Name]

Condition_Name

condition-10

condition-9.1

condition-9

condition-8

condition-7

condition-6

condition-5

condition-4.2

condition-4.1

condition-4

What I need the calculated column to do is search Table1 using the values in Table2 and output the first instance starting from Top to bottom.

Expected output for the new calculated column in Table1

Tags	New Calculated Column
condition-4; condition-4.2; condition-3	condition-4.2
condition-9; condition-5; condition-10	condition-10
condition-8; condition-7; condition-6	condition-8

I hope this makes sense and would be very greatful if someone can help!

Thanks,

Rich

[DataInsights · Accepted solution]

@rchamberlain,

Try this solution.

1. Create an Index column in Power Query for Table2 (Condition_Name).

2. Create calculated column in Table1 (Tags).

New Calculated Column = 
VAR vTag = Tags[Tags]
// get the minimum index (multiple matches may occur)
VAR vMinIndex =
    MINX (
        FILTER (
            Condition_Name,
            CONTAINSSTRING ( vTag, Condition_Name[Condition_Name] )
        ),
        Condition_Name[Index]
    )
VAR vResult =
    // get the condition name corresponding to the minimum index
    MAXX (
        FILTER ( Condition_Name, Condition_Name[Index] = vMinIndex ),
        Condition_Name[Condition_Name]
    )
RETURN
    vResult

[rchamberlain]

Thanks for the quick reply!  This worked perfectly!

[lbendlin]

and output the first instance starting from Top to bottom.

Power BI has no concept of that.  You need to indicate what you mean by "top" or "bottom".  Usually by adding an index column

Your request doesn't seem to depend on user input, so instead of doing it as a calculated column you could also decide to do it in Power Query.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSs7PS8ksyczP0zWxVkDi6Bkhc42VYnWQ1VoiS5oicwwN0JRaIMuaI3PMlGJjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Tags = _t]),
    #"Added Custom" = Table.AddColumn(Source, "Custom Column", each List.Intersect({Text.Split([Tags],"; "),#"Condition_Name"[Condition_Name]})),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom", (k)=> List.First(Table.Sort(Table.SelectRows(#"Condition_Name",each List.Contains(k[Custom Column],[Condition_Name])),"Index")[Condition_Name])),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Custom Column"})
in
    #"Removed Columns"