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 N2 H,JK...
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...

     

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