Forum Discussion

carlpaul153's avatar
carlpaul153
Helper I
5 years ago
Solved

How to remove text strings stored in a list from a column

I'm trying to remove all the substrings from the "substring" list, in column [column1] by adding the following custom column: List.Accumulate(substring, [Column1],(state, current) => Text.Replace(st...
Need Help
  • Jimmy801's avatar
    Jimmy801
    5 years ago

    Hello carlpaul153 

     

    check out this solution. this code replaces all substrings defined in the step SubstringList transforms the column1 into a list with two items. first is new value, second is the removed part. Then duplicated the column and transform them again. Extracting in the column1 the first item, in the changes-column the 2nd item

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WckxKVorViVZKSU0D0+kZmWC6AgTArCoQUIqNBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
    SubstringList = {"bc", "xx", "zzz"},
    TranformColumn1 = Table.TransformColumns
    (
        #"Changed Type",
        {
            {
                "Column1",
                (cell)=> List.Accumulate(SubstringList, {cell, ""}, (old, current)=> 
                let 
                    CreateNew = Text.Replace(old{0},current,""),
                    CreateSubstringsRemoved = try Text.Combine(List.Difference(Text.ToList(old{0}), Text.ToList(CreateNew))) otherwise ""
                in 
                    {CreateNew, if CreateSubstringsRemoved <> "" and old{1}<> "" then old{1} & ", " & CreateSubstringsRemoved else if old{1} <>"" and CreateSubstringsRemoved = "" then  old{1} else CreateSubstringsRemoved }
                )
            }
        }
    ),
    Duplicated = Table.DuplicateColumn(TranformColumn1, "Column1", "changes"),
    
    Final = Table.TransformColumns
    (
        Duplicated,
        {
            {
                "Column1",
                each _{0}
            },
            {
                "changes",
                each _{1}
            }
        }
    )
in
    Final

     

    Copy paste this code to the advanced editor in a new blank query to see how the solution works.

    If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
    Kudoes are nice too

    Have fun

    Jimmy

carlpaul153's avatar
carlpaul153
Helper I
5 years ago
the problem is that in each cell of the column I want to delete a list of strings, not a single string.
For example, I would like Text.Replace ([Column1], {"100", "This", "The"}, "") to return "Is Number" in your example.
Of course, this is not possible because text.replace does not support lists as a parameter.
CNENFRNL's avatar
CNENFRNL
Community Champion
5 years ago

The procedure to replace each substring in a list is to be defined as a function. Pls refer to the code as follows,

 

let
    fn = (Str as text, SubstrList as list) =>

    let
        Replacing = List.Generate(
            () => [str = Str, counter = 0],
            each [counter] <= List.Count(SubstrList),
            each [str = Text.Replace([str], SubstrList{[counter]}, ""), counter = [counter] + 1],
            each Text.Trim([str])
        )
    in
        Replacing,

    SubstrList = {"Is", "This", "The"},
    Str = "This Is The Number 100.",
    Invocation = fn(Str, SubstrList)
in
    Invocation

 

result reads like this,

List
This Is The Number 100.
This  The Number 100.
The Number 100.
Number 100.