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.

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