Remove Some Text for certain Observations

[Anonymous]

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/07/2021	CANADA	03A01- Candy
12/07/2021	Mexico	Water

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.

[CNENFRNL]

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"

[Anonymous]

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

[Migasuke · Accepted solution]

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.