Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
JollyRoger01
Helper III
Helper III

How to discretize the If Text.Contains values and Then values from one big IF statement

I recently tried to create hundreds of IF statements in a single line of Power Query code to try and categories my bank transactions, only to realise I should have done it a completely different way. But I don't want to waste all the work I did, so I now want to extract the text it searches for and the text it uses into two columns. For example, for the following line of code:

 

 

#"Added Custom2" = Table.AddColumn(#"Removed Columns1", "Who", each if Text.Contains([Transaction Description], "PAYPALAU") then "PayPal" else if Text.Contains([Transaction Description], "BUNNINGS") then "Bunnings" else if Text.Contains([Transaction Description], "BENLEIGH VENDING") then "Benleigh Vending Machines" else null)

 

 

I'd like to output two columns like so:

PAYPALAUPayPal
BUNNINGSBunnings
BENLEIGH VENDING

Benleigh Vending Machines

 

Does anyone know how I would achieve this (preferably without regex)?

1 ACCEPTED SOLUTION
Anonymous
Not applicable

forse quesat si adatta meglio alle tue esigenze:

 

let
    Origine = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("nY/BCsIwEER/ZYkXBRH07qHWUgUNRasiTQ+xXW0g3YhJRf/exIN41dswzL7ZKQrWEyyqa6wh7qwz7UQwmEIuTxpH3o+N7lrq+9AGW3MPsbdjx4INQbBDY4JAWTWgzpDjw41iQ04qsv0iv0mysnLKEMzRVjd1DboMl1l0zKJVtBNsAK5BCpZ8ZlL7B1Bb/BE323G+5On2CzfriBRd7J/AhK+SZbqAfcLnnvwNRtKoLg3skWrfAGs/XxF+mqjTesDK8gU=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Colonna1 = _t]),
    tad = Table.AddColumn(Origine, "splt", each Table.FromColumns(List.Zip(List.Transform(Text.Split([Colonna1], " else"), (t)=>Text.Split( List.Last(Text.Split(t,", ")),") then "))),{"From","To"}))
in
    tad

View solution in original post

2 REPLIES 2
Anonymous
Not applicable

forse quesat si adatta meglio alle tue esigenze:

 

let
    Origine = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("nY/BCsIwEER/ZYkXBRH07qHWUgUNRasiTQ+xXW0g3YhJRf/exIN41dswzL7ZKQrWEyyqa6wh7qwz7UQwmEIuTxpH3o+N7lrq+9AGW3MPsbdjx4INQbBDY4JAWTWgzpDjw41iQ04qsv0iv0mysnLKEMzRVjd1DboMl1l0zKJVtBNsAK5BCpZ8ZlL7B1Bb/BE323G+5On2CzfriBRd7J/AhK+SZbqAfcLnnvwNRtKoLg3skWrfAGs/XxF+mqjTesDK8gU=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Colonna1 = _t]),
    tad = Table.AddColumn(Origine, "splt", each Table.FromColumns(List.Zip(List.Transform(Text.Split([Colonna1], " else"), (t)=>Text.Split( List.Last(Text.Split(t,", ")),") then "))),{"From","To"}))
in
    tad
Anonymous
Not applicable

try this

 

 

let
    Origine = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("nY/BCsIwEER/ZYkXBRH07qHWUgUNRasiTQ+xXW0g3YhJRf/exIN41dswzL7ZKQrWEyyqa6wh7qwz7UQwmEIuTxpH3o+N7lrq+9AGW3MPsbdjx4INQbBDY4JAWTWgzpDjw41iQ04qsv0iv0mysnLKEMzRVjd1DboMl1l0zKJVtBNsAK5BCpZ8ZlL7B1Bb/BE323G+5On2CzfriBRd7J/AhK+SZbqAfcLnnvwNRtKoLg3skWrfAGs/XxF+mqjTesDK8gU=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Colonna1 = _t]),
    tad = Table.AddColumn(Origine, "splt", each List.Transform(Text.Split([Colonna1], "else"), (t)=>Text.Split( List.Last(Text.Split(t,",")),") then "))),
    #"Tabella splt espansa" = Table.ExpandListColumn(tad, "splt"),
    #"Valori estratti" = Table.TransformColumns(#"Tabella splt espansa", {"splt", each Text.Combine(List.Transform(_, Text.From), ":"), type text}),
    #"Suddividi colonna in base al delimitatore" = Table.SplitColumn(#"Valori estratti", "splt", Splitter.SplitTextByDelimiter(":", QuoteStyle.Csv), {"splt.1", "splt.2"})
in
    #"Suddividi colonna in base al delimitatore"

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.

Top Kudoed Authors