Forum Discussion

JollyRoger01's avatar
JollyRoger01
Helper III
5 years ago
Solved

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)?

Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    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

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    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's avatar
    Anonymous
    Not applicable
    5 years ago

    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"