cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
CrisCros23
Regular Visitor

Find text of one column into another column

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

 

CrisCros23_0-1637915637428.png

 

1 ACCEPTED SOLUTION
latimeria
Frequent Visitor

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

 

View solution in original post

3 REPLIES 3
latimeria
Frequent Visitor

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

 

View solution in original post

Thanks Latimera works well!

CrisCros23
Regular Visitor

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

Helpful resources

Announcements
2022 Release Wave 1 760x460.png

2022 Release Wave 1 Plan

Power Platform release plan for the 2022 release wave 1 describes all new features releasing from April 2022 through September 2022.

Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors