Find text of one column into another column

[CrisCros23]

Hi All,

I want to search if the text of one column is existing in another column. Iferror then it returns a given text.

See excel formula below.

Thank you

[latimeria · Accepted solution]

You can do a merge

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcitKzEtOVdJRCi5PTUnNU4rViVbyLEnMqQQLZZZUpRblJOalgMXdU4tyE/NAMlBdIEFkRTpKoXmZJakpCt6Zeekp+blKsbEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Country = _t, Partner = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Country", type text}, {"Partner", type text}}),
    #"Merged Queries" = Table.FuzzyNestedJoin(#"Changed Type", {"Partner"}, #"Changed Type", {"Country"}, "Changed Type", JoinKind.LeftOuter, [IgnoreCase=true, IgnoreSpace=true, NumberOfMatches=1, Threshold=1]),
    #"Expanded Changed Type" = Table.ExpandTableColumn(#"Merged Queries", "Changed Type", {"Country"}, {"Changed Type.Country"}),
    #"Replaced Value" = Table.ReplaceValue(#"Expanded Changed Type",null,"Missing",Replacer.ReplaceValue,{"Changed Type.Country"})
in
    #"Replaced Value"

or use List.PositionOf

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcitKzEtOVdJRCi5PTUnNU4rViVbyLEnMqQQLZZZUpRblJOalgMXdU4tyE/NAMlBdIEFkRTpKoXmZJakpCt6Zeekp+blKsbEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Country = _t, Partner = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Country", type text}, {"Partner", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each try #"Changed Type"[Country]{List.PositionOf(#"Changed Type"[Country], [Partner])} otherwise "Missing")
in
    #"Added Custom"

O

[CrisCros23]

Thanks Latimera works well!

[CrisCros23]

Sorry I forgot to mention I want to do it directly in power Query. If not possible then using DAX formula. Thank you