How can I search for multiple strings?

0 votes

I want to check in a power query new column if a string like "This is a test string" contains any of the strings list items {"dog", "string", "bark"}.

I already tried Text.PositionOfAny("This is a test string",{"dog","string","bark"}), but the function only accepts single-character values

Expression.Error: The value isn't a single-character string.

Any solution for this?

Oct 24, 2018 in Power BI by Upasana
• 160 points
1,249 views

1 answer to this question.

0 votes

A simple solution is this:

List.ContainsAny(Text.SplitAny("This is a test string", " "), {"dog","string","bark"})

It transforms the text into a list because there we find a function that does what you need.

answered Oct 24, 2018 by Upasana
• 160 points
Hi,  instead of return true,   it is possible to return the string,  for e.g.  if string is "This is a dog"  then return as dog

Thank you !
Hey, @Christopher,

You want the selected value function. This does exactly what you want. It returns a value only if there is a single value, otherwise, it returns blank (or an optional alternate).

Related Questions In Power BI

0 votes
1 answer

how can I connect my web api with Power BI Reports?

Go to Home > Edit Queries > ...READ MORE

answered Oct 18, 2018 in Power BI by Annie97
• 2,200 points
5,148 views
0 votes
1 answer

How can i see my plugin name in the Power BI Visual plugin List ?

there is a couple of errors in your ...READ MORE

answered Nov 8, 2018 in Power BI by Upasana
• 8,620 points
164 views
0 votes
1 answer

How can I concatenate grouped values in PowerQuery ?

If your table is Source, and if ...READ MORE

answered Feb 27, 2019 in Power BI by Shubham
• 13,450 points
1,258 views
0 votes
1 answer

PowerQuery : How can I reference a cell's value?

You can do so using a named ...READ MORE

answered Feb 27, 2019 in Power BI by Shubham
• 13,450 points
8,108 views
0 votes
1 answer

How can I get a column value from previous row in Power Query?

Hi Sindhu, add this line as your ...READ MORE

answered Mar 18, 2019 in Power BI by Cherukuri
• 32,490 points
4,605 views
0 votes
1 answer

How to export Power Queries from One Workbook to Another with VBA?

Try solving it using the Workbook. Query ...READ MORE

answered Oct 22, 2018 in Power BI by Annie97
• 2,200 points
1,212 views
0 votes
1 answer

Excel Power Query: Using List.MatchAny on a column value

try this. let TableA = ...READ MORE

answered Oct 22, 2018 in Power BI by Annie97
• 2,200 points
1,549 views
0 votes
1 answer

Power Query Web request results in “CR must be followed by LF” Error

What I think is, it might look ...READ MORE

answered Oct 29, 2018 in Power BI by Shubham
• 13,450 points
506 views
0 votes
1 answer

Select Columns from table instead of removing afterwards in Power Query

Try this. let db = Sql.Databases("sqlserver.database.url"){[Name="DatabaseName"]}[Data], Sales_vDimCustomer = ...READ MORE

answered Oct 31, 2018 in Power BI by Shubham
• 13,450 points
1,104 views