Forum Discussion

Merleau's avatar
Merleau
Helper II
1 year ago
Solved

Converting list elements based on their lengths

Hello,

One column in my table is a list of strings. I would like to change the strings to lower case if their lenghts is greater than 1.

 

Name      Types
N1   A,ABC,B,CDF,DF
N2H,JKL,BG,DC,C

 

Convert to

Name      Types
N1   A,abc,B,cdf,df
N2H,jkl,bg,dc,C

 

Can somebody help?

Thank you

Need Help
Show and Tell
Tips and Tricks
  • jgeddes's avatar
    jgeddes
    1 year ago

    Here is one way to do this...

    = Table.TransformColumns(previousQueryStep, {{"Types", each Text.Combine(List.Transform(Text.Split(_, ","), each if Text.Length(_) > 1 then Text.Lower(_) else _), ","), type text}})

    Using your example table you end up with...

     

4 Replies

  • jgeddes's avatar
    jgeddes
    Super User
    1 year ago

    Here is one way to do this...

    = Table.TransformColumns(previousQueryStep, {{"Types", each Text.Combine(List.Transform(Text.Split(_, ","), each if Text.Length(_) > 1 then Text.Lower(_) else _), ","), type text}})

    Using your example table you end up with...

     

  • AlienSx's avatar
    AlienSx
    Super User
    1 year ago
    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8jNUUFBQ0lFy1HF0ctZx0nF2cdNxcVOK1QFKGQHFPXS8vH10nNx1XJx1nJViYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Name      " = _t, Types = _t]),
    chars = List.Buffer({"a".."z", "A".."Z"}), 
    result = Table.TransformColumns(
        Source, 
        {
            "Types", 
            (x) => Text.Combine(
                List.Transform(
                    Text.Split(x, ","), 
                    (w) => if List.Contains(chars, w) then w else Text.Lower(w)
                ),
                ","
            )
        }
    )
in
    result
  • ThxAlot's avatar
    ThxAlot
    Super User
    1 year ago

    For fun only, to incorporate the power of regex,

     

    import re
import numpy as np
df['Types']=np.vectorize(lambda string:re.compile(r'[a-z]{2,}(?=\b)',flags=re.I).sub(lambda match:str.lower(match.group(0)),string))(df['Types'])
    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8jNUUFBQ0lFy1HF0ctZx0nF2cdNxcVOK1QFKGQHFPXS8vH10nNx1XJx1nJViYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, Types = _t]),
    #"Run Python script" = Python.Execute("import re#(lf)import numpy as np#(lf)df['Types']=np.vectorize(lambda string:re.compile(r'[a-z]{2,}(?=\b)',flags=re.I).sub(lambda match:str.lower(match.group(0)),string))(df['Types'])",[df=Source]),
    df = #"Run Python script"{[Name="df"]}[Value]
in
    df