If a specific value/text exists in any of the columns then return a value/text

[Nigel_Mayhew1]

Hi there,

I need to look up a specific value/text in multiple columns at once, without having to list all the column names (the column names could change in the future). Then, if the value/text is found in any of the columns, I need to have the first 6 charachters of the contents of the cell.

Here is a basic example:

Column1Column2Column3Column4Column5Column6Column7Column8Column9Column10Column11Column12Result

Hello1

Bye

Bye

Bye

Bye

Bye

Bye

Bye

Bye

Bye

Bye

Bye

Hello1

Bye

Bye

Hello956

Bye

Bye

Bye

Bye

Bye

Bye

Bye

Bye

Bye

Hello9

Bye

Bye

Bye

Bye

Bye

Bye

Hello42

Bye

Bye

Bye

Bye

Bye

Hello4

Bye

Bye

Bye

Bye

Bye

Bye

Bye

Bye

Bye

Hello1

Bye

Bye

Hello1

Bye

Bye

Bye

Hello777

Bye

Bye

Bye

Bye

Bye

Bye

Bye

Bye

Hello7

Bye

Bye

Bye

Bye

Bye

Bye

Bye

Bye

Bye

Bye

Hello55

Bye

Hello5

Can someone help?

Many thanks,

Nigel

[Anonymous · Accepted solution]

Hi @Nigel_Mayhew1 ,

If you don't want list all the column names, then there is no way to achieve this by DAX or other function in Power BI Desktop. I'm afraid that you can only achieve this by Power Query.
First, you can use Ctrl + A to choose the whole table and Merge columns:

Then use this M function to add a custom column:

let
    TextToFind = "Hello",
    FoundIndex = Text.PositionOf([Merged], TextToFind, Occurrence.First)
    in
    if FoundIndex = -1 then null
    else Text.Middle([Merged], FoundIndex, 6)

Then split the previously merged column:

Click "Close & Apply":

And the final output is as below:

Here is the whole M function in the Advanced Editor:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8kjNyck3VNJRcqpMpYiM1YlGEQMbbGlqRiXjcJFga0yMqGQaDvMNiTAMrNDc3JxmPsXrQlNTmGmxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t, Column5 = _t, Column6 = _t, Column7 = _t, Column8 = _t, Column9 = _t, Column10 = _t, Column11 = _t, Column12 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}, {"Column7", type text}, {"Column8", type text}, {"Column9", type text}, {"Column10", type text}, {"Column11", type text}, {"Column12", type text}}),
    #"Merged Columns" = Table.CombineColumns(#"Changed Type",{"Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8", "Column9", "Column10", "Column11", "Column12"},Combiner.CombineTextByDelimiter(",", QuoteStyle.None),"Merged"),
    #"AddColumn" = Table.AddColumn(#"Merged Columns", "Custom Column", each let
    TextToFind = "Hello",
    FoundIndex = Text.PositionOf([Merged], TextToFind, Occurrence.First)
    in
    if FoundIndex = -1 then null
    else Text.Middle([Merged], FoundIndex, 6)),
    #"Split Column by Delimiter" = Table.SplitColumn(AddColumn, "Merged", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Merged.1", "Merged.2", "Merged.3", "Merged.4", "Merged.5", "Merged.6", "Merged.7", "Merged.8", "Merged.9", "Merged.10", "Merged.11", "Merged.12"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Merged.1", type text}, {"Merged.2", type text}, {"Merged.3", type text}, {"Merged.4", type text}, {"Merged.5", type text}, {"Merged.6", type text}, {"Merged.7", type text}, {"Merged.8", type text}, {"Merged.9", type text}, {"Merged.10", type text}, {"Merged.11", type text}, {"Merged.12", type text}})
in 
    #"Changed Type1"

Best Regards,
Dino Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.