Search code examples
excelpowerbidaxpowerquerym

SWITCH Function with multiple hits - Power Query

I recently started using Power BI a month ago. I'm currently trying to use SWITCH to sort through a column of text. The main problem that I am running into is that when a row has multiple words that satisfy the criteria for a case that I've coded for, the output column only contains the first case being satisfied, instead of all of the cases being satisfied.

Here's the code I have so far:

SWITCH Hits Test =
SWITCH(
TRUE(),
CONTAINSSTRING('SWITCH Test'[Raw Data], "cat"), "cat",
CONTAINSSTRING('SWITCH Test'[Raw Data], "dog"), "dog",
CONTAINSSTRING('SWITCH Test'[Raw Data], "fox"), "fox",
"Not Target")

And here's what my table looks like:

Raw Data SWITCH Hits Test Ideal Result
dog cat cat cat, dog
dog dog dog dog
dog fox dog dog, fox
cat dog cat cat, dog
cat cat cat cat
cat fox cat cat, fox
test Not Target Not Target

The solution that I've come up with is to make separate columns for each SWITCH case, but I was wondering if there was another workaround/potential function I could use before doing this as it seems time intensive for larger SWITCH functions. Any help would be greatly appreciated.

Solution

  • enter image description here

    Add a custom column:

    let
a={"cat", "dog", "fox"},
b =  List.Distinct(  List.RemoveNulls( Text.Split( Text.Trim( [Raw Data]), " "))),
c = List.Intersect({a,b}),
d = Text.Combine(c, ", ")
in if List.IsEmpty(c) then "Not Target" else d

    Full code

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSslPV0hOLFFQ0lFCUDoKQGGlWB2INAgDxREUXCYtvwJVRgckBJYGGQYVx2YuSAzDWrgM1FxkjTBzS1KLwYJ++SUKIYlF6aloPKXYWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Raw Data" = _t, #"SWITCH Hits Test" = _t, #"Ideal Result" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Raw Data", type text}, {"SWITCH Hits Test", type text}, {"Ideal Result", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each let
a={"cat", "dog", "fox"},
b =  List.Distinct(  List.RemoveNulls( Text.Split( Text.Trim( [Raw Data]), " "))),
c = List.Intersect({a,b}),
d = Text.Combine(c, ", ")
in if List.IsEmpty(c) then "Not Target" else d)
in
    #"Added Custom"