Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Remove Some Text for certain Observations

Hello,

I want to clean a DB in query i'm trying to remove text in a specifit cells,

like this: 

DATECOUNTRY Code
12/07/2021

MEXICO

001- Chocolate
12/07/2021USA02- Sugar
12/07/2021CANADA03A01- Candy
12/07/2021MexicoWater

 

I put the simple example beacuse represent the problem, i need to extract Chocolate, Sugar and Candy and Water. Each observation has different long of codes, some has any code.

This was my tries:

  • Divede the colunm by delimiters
  • Transform colum

My idea is the following: Search for text who has a code ( all codes star with 0), then take the text after the delimiter "space", and if it doesn't have a code, just put the same value as in the column

 

I try the next M but i can't be able to make it works:

 

#"New Column" = Table.AddColumn(#"Origen", "New Column", each

                   if Text.StartsWith ([Code],"0" )
                         then Table.TransformColumns(#"Origen", {{"Code", each Text.AfterDelimiter(_, " "), type text}}) 

                   else [Code])

 

It always gives me a column with a table to expand or a function. i need a column,

 

I'm new in M so i can't find the way to resolve that, I apreacciate any idea.

Need Help
  • Migasuke's avatar
    Migasuke
    5 years ago

    Hi Anonymous ,

    Here is your Power Query step.


    #"Added Custom1" = Table.AddColumn(#"Changed Type", "Custom.1", each if Text.StartsWith([Code],"0") then Text.AfterDelimiter([Code]," ")
    else [Code])

    If it helps, please mark this as a solution.

3 Replies

  • Migasuke's avatar
    Migasuke
    Memorable Member
    5 years ago

    Hi Anonymous ,

    Here is your Power Query step.


    #"Added Custom1" = Table.AddColumn(#"Changed Type", "Custom.1", each if Text.StartsWith([Code],"0") then Text.AfterDelimiter([Code]," ")
    else [Code])

    If it helps, please mark this as a solution.

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    I would just append a " " to each value in the column, with the GUI function. Let's say your table or previous step is named LastStep:

     

    AddSpace = Table.TransformColumns(LastStep, {{"Code", each Text.Insert(_, 0, " ")}})

     

    Then you can use Text.AfterDelimiter to get everything after the first space from the end:

     

    Table.TransformColumns (AddSpace, {{"Code", each Text.AfterDelimiter(_, " ", {0,  RelativePosition.FromEnd})}})

     

    --Nate

     

     

     

  • CNENFRNL's avatar
    CNENFRNL
    Community Champion
    5 years ago
    let
    LookUp = {"Chocolate", "Sugar", "Candy", "Water", "Egg", "Oil"},
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjTSNzDXNzIwMlTSUfJ1jfB09gcyDAwMdRWcM/KT83MSS1KVYnXQFIYGO4JUGekqBJemJxZhKnB29HN0AasxdgSblZiXUompzDe1IjM5H8goB1oDNCYWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [DATE = _t, #"COUNTRY " = _t, Code = _t]),
    Split = Table.TransformColumns(Source, {"Code", each List.Select(Text.SplitAny(_, "- "), each List.Contains(LookUp, _, Comparer.OrdinalIgnoreCase))}),
    #"Extracted Values" = Table.TransformColumns(Split, {"Code", each Text.Combine(List.Transform(_, Text.From)), type text})
in
    #"Extracted Values"