CODEWITHSUNDEEP
powerbipowerquery
Stu Guerin
Stu Guerin

Reputation: 21

Power Query select records by multiple strings OR by a string in a different field

Using the answer on this page How to search multiple strings in a string? I managed to write the following query -

let

Source = Csv.Document(File.Contents("P:\DMWORK\all_donations_for_last_10_years.txt"),[Delimiter="   ", Encoding=1252]),

#"Promoted Headers" = Table.PromoteHeaders(Source),

#"Defined Table" = Table.TransformColumnTypes(#"Promoted Headers",
{{"Donation Date", type date}, {"Amount", type number}, {"Company", type text}, {"Extra Codes", type text}}),

Text.ContainsAny = (string as text, list as list) as logical =>
    List.AnyTrue(List.Transform(list, (substring) => Text.Contains(string, substring))),

FirstFilter = Table.SelectRows(#"Defined Table", each Text.ContainsAny([Company], {"Assoc","Band","Baseball"})),

SecondFilter = Table.SelectRows(#"Defined Table", each [Extra Codes] = "FUNDRAIS"),

FinalResult = FirstFilter or SecondFilter

in

FinalResult

So what I'm trying to do, is SelectRows that match either the FirstFilter OR the SecondFilter. Yet it's giving me a "Expression.Error: The value isn't a single-character string. Details: Value= " error.

If someone could put me in the right direction to resolve this, I would really appreciate it.

Thanks in advance.

Upvotes: 0

Views: 777

Answers (1)

Mike Honey
Mike Honey

Reputation: 15037

I would simplify this using the secret "or" operator. I'm not being sarcastic - it actually is secret as in totally obscured in their documentation. Try searching for it...

Anyway I would replace the code from FirstFilter down with something like:

Filter = Table.SelectRows(#"Defined Table", each Text.ContainsAny([Company], {"Assoc","Band","Baseball"}) or [Extra Codes] = "FUNDRAIS" ),

in

Filter

Upvotes: 1

Related Questions