Power Query Help!

[BeccaEzekiel]

I have columns C-Z that contain various statements.  I'm looking for the formula that will look at columns C-Z and for each of the columns that contain one of the following words ("Highly", "Likely", "Unfamiliar"), I would like to return the full value/statement of that cell into the new cell.  

Example:

Column C: Johnny is highly capable of the job.

Column D   Sarah is likely to join.

Column E: Sally is unfamiliar to the project.

Column F: Tommy is having a great day.

End result needed for the new column:

Johnny is highly capable of the job.

Sarah is likely to join.

Sally is unfamiliar to the project.

I've tried doing a conditional column but it only returns the first result found within those columns instead of all three results.

I've tried doing an if text.contains for the specific columns containing "highly" and used OR to separate the various Text.Contains for the various columns and tell it to return the column. 

Example: (if Text.Contains([Column C], "highly") or Text.Contains([Column D, "highly") then [Column C][Column D] else null. 

(I've tried with "&" and "or" between the 'then' columns and still get an error.)

In doing so, I get the following error:

Expression.Error: We cannot convert the value "Finding" to type Logical.

Details:

Value=Finding

Type=[Type]

The column type for columns C-Z is text, as well as the source column.  The custom column type is coming in as alphanumeric.  I've changed the type of the custom column to text but that's not eliminating the error.

[AlexisOlson]

I think you want something like this:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("JY2xDoMwDAV/xcpc8SFd2w0xPKhJTB0bpWml/D2EznenG8dw92TWSD6UJCZttGDHrEy+Uk1Mm8/hFh4oSF1SefMpVT+B2HAh1av/2oosKigd93YvvvFSu/X0nP8X/MQigWJhVHqhDWGaDg==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [C = _t, D = _t, E = _t, F = _t]),
    ColsToSearch = {"C", "D", "E", "F"},
    Keywords = {"highly", "likely", "unfamiliar"},
    #"Added Custom" = Table.AddColumn(Source, "Custom", each
        Text.Combine(
            List.Select(
                Record.ToList(Record.SelectFields(_, ColsToSearch)),
                (txt) => List.ContainsAny(Text.Split(txt, " "), Keywords)
            )
            , "#(cr)"
        )
        , type text
    )
in
    #"Added Custom"

Custom column explanation provided by GPT4:

For each row in the original table, it does the following:

• It selects the values from the columns specified in ColsToSearch using Record.SelectFields(...).
• Converts the selected record to a list using Record.ToList(...).
• It checks each value in the list to see if it contains any of the specified keywords. This is done by splitting the text of each value by space (Text.Split(txt, " ")) and then checking if any of those split parts match the keywords using List.ContainsAny(...).
• Only values that contain one of the keywords are retained using List.Select(...).
• These filtered values are then combined into a single text string using Text.Combine(...), with each value separated by a carriage return (#(cr)).
• This resulting text is what gets placed in the new "Custom" column for that row.

[BeccaEzekiel]

Thank you for your quick response. I was using dummy data in my question so when I copy/paste that into my query it's returning it as noted in your screenshot, however, since it was dummy data I'm having a hard time converting it to match my query.

Is is possible to get the exact steps taken to get the resolution or perhaps a link to a good video that would explain these steps?  While the bulleted items in your response make sense to me, unfortunately, I'm not familiar with Json.Document and the first part of the formula.

IE: My true column headers range from AC Findings.1 through AC Findings.20.  Random columns may contain a blank on randow rows while others contain various data/values in which I would want to look for "Highly" "Significant" & "Critical".

[AlexisOlson]

The first line starting with Source is where I've defined the dummy data using the Enter data button in Power BI (it saves the table data in a compressed JSON format). This step will be replaced by whatever your actual source is or all the steps in your query so far.

Your query might look something like this:

let
    Source = [...],
    [...],
    #"Last Query Step" = [...],
    
    ColsToSearch = List.Transform({1..20}, each "AC Findings." & Number.ToText(_)),
    Keywords = {"highly", "likely", "unfamiliar"},
    #"Added Custom" = Table.AddColumn(#"Last Query Step", "Custom", each
        Text.Combine(
            List.Select(
                Record.ToList(Record.SelectFields(_, ColsToSearch)),
                (txt) => List.ContainsAny(Text.Split(txt, " "), Keywords)
            )
            , "#(cr)"
        )
        , type text
    )
in
    #"Added Custom"

where the part before ColsToSearch is whatever you already have. Be sure to reference the last step inside Table.AddColumn(<here>, ...).