Using conditional statement with regex

[Anonymous]

Hello,

I am using powerbi desktop to process my monthly bank transactions, and i am trying to categorise transactions.

I can do this with a conditional matching a single value, eg.

= Table.AddColumn(#"Lowercased Text", "Category", each if Text.Contains([Description], "marini") then "food" else null )

This is nice, but i am looking for a more robust way to do this, along the lines of 

= Table.AddColumn(#"Lowercased Text", "Category", each if Text.Contains([Description], ("marini" OR "zeebra" OR "baker" OR "mcdonalds")) then "food" else null )

eg, i want to look for multiple strings, and if matches any, then update the field..?

[Rickmaurinus]

Old topic, but the easiest solution you could do is use List.Contains, and then reverse the arguments. So first put in the list with the words you are looking for. And then as value, put in the [Description] column. Hope that helps 🙂 

= Table.AddColumn(ProperCaseFirst2Rows, "Category", each if List.Contains( { "marini" , "zeebra" , "baker" , "mcdonalds"} , [Description] ) then "food" else null)

--------------------------------------------------

@ me in replies or I'll lose your thread

Master Power Query M? -> https://powerquery.how

Read in-depth articles? -> BI Gorilla

Youtube Channel: BI Gorilla

If this post helps, then please consider accepting it as the solution to help other members find it more quickly.

[MarcelBeug · Accepted solution]

You can use Splitter.SplitTextByAnyDelimiter to split texts on any delimiter with multiple characters (unlike Text.SplitAny).

= Table.AddColumn(#"Lowercased Text", "Category", each if List.Count(Splitter.SplitTextByAnyDelimiter({"marini","zeebra","baker","mcdonalds"})([Description])) > 1 then "food" else null)

[stretcharm]

I've used this solution from @KenPuls  to do a Vlookup from a list of category keyworks in M in to categorise Packages in my SSIS Dashboard (http://community.powerbi.com/t5/Data-Stories-Gallery/SSIS-Catalog-DB-Dashboard/m-p/244677#M1110)

https://www.excelguru.ca/blog/2015/01/28/creating-a-vlookup-function-in-power-query/

Another option is to use groups however you may need to process the values first if there are lots of changable details such as tran reference.

https://docs.microsoft.com/en-us/power-bi/desktop-grouping-and-binning