Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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
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
Thanks Latimera works well!
Sorry I forgot to mention I want to do it directly in power Query. If not possible then using DAX formula. Thank you
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.