Forum Discussion

JChris's avatar
JChris
Icon for Helper II rankHelper II
9 years ago
Solved

Remove text in one column based on another column

I have a table like this:   CarName | Year | Maker Civic | Civic 2010 | Honda Civic | Civic 2012 | Honda Civic | Civic 2013 | Honda Focus | Focus 2009 | Ford Focus | Focus 2017 | Ford Santa Fe | Sa...
Need Help
  • MarcelBeug's avatar
    MarcelBeug
    9 years ago

    This is a nice example for the new Text,BetweenDelimiters function that was introduced with the April 2017 Update of Power BI Desktop.

     

    You can add a column with the following formula:

     

    Text.Trim(Text.BetweenDelimiters([Version]," ",[Software],{1,RelativePosition.FromEnd},{0,RelativePosition.FromEnd}))

     

     

    It will look for the the second space from the end and then - in the preceding part of the string - to the first [Software] string from the end, and returns the part in between.

     

     

    I created a 14 minute video about the new Text.AfterDelimiter, Text,BeforeDelimiter and Text.BetweenDelimiters functions.

Anonymous's avatar
Anonymous
Not applicable
9 years ago

Hi JChris,

 

You can try to add a custom column with Text.ReplaceRange function to achieve your requirement.

AddCustom = Table.AddColumn(#"Renamed Columns", "Custom", each Text.ReplaceRange([Year],0,Text.PositionOf([Year], " ", Occurrence.Last),""))

 

Full query:

let
    Source = Csv.Document(File.Contents("C:\Users\xxx\Desktop\New Text Document.txt"),[Delimiter="|", Columns=3, Encoding=1252, QuoteStyle=QuoteStyle.None]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]),
    #"Renamed Columns" = Table.RenameColumns(#"Promoted Headers",{{" Year ", "Year"}}),
    #"Added Custom" = Table.AddColumn(#"Renamed Columns", "Custom", each Text.ReplaceRange([Year],0,Text.PositionOf([Year], " ", Occurrence.Last),""))
in
    #"Added Custom"

 

 

After these steps, remove the original year column.

 

In addition, Text.End function also suitable for your requirement.

= Table.AddColumn(#"Added Custom", "Custom", each Text.End([Year],4))

 

 

Regards,

Xiaoxin Sheng