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:  DATE COUNTRY  Code 12/07/2021 MEXICO 001- Chocolate 12/07/2021 USA 02- Sugar 12/...
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.

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"