Forum Discussion
Using conditional statement with regex
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..?
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)
3 Replies
- MarcelBeug
Community Champion
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
Memorable Member
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
- Rickmaurinus
Helper V
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.
