cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
JollyRoger01
Helper II
Helper II

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
Rocco_sprmnt21
Super User II
Super User II

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
Rocco_sprmnt21
Super User II
Super User II

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

Rocco_sprmnt21
Super User II
Super User II

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
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Get Ready for Power BI Dev Camp

Power BI Dev Camp - June 24th

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors