cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper II
Helper II

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 | Santa Fe 2016 | Hyundai

What I need to do is create another column (or modify the existing one) with the year only, removing the car name, as I already have the "CarName" column. How is that possible? To make it very clear, the final result should be something like this:

 

CarName | Year | Maker
Civic | 2010 | Honda
Civic | 2012 | Honda
Civic | 2013 | Honda
Focus | 2009 | Ford
Focus | 2017 | Ford
Santa Fe | 2016 | Hyundai

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted

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.

 

Remove text in one column based on another column.png

 

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

Specializing in Power Query Formula Language (M)

View solution in original post

6 REPLIES 6
Highlighted
Anonymous
Not applicable

Capture1.PNG

 

 

Capture2.PNG

 

 

Capture3.PNG

 

 

Capture4.PNG

 

 

 

Highlighted
Community Champion
Community Champion

@JChris

 

Hi, please follow this steps:

 

Split.gif




Lima - Peru
Highlighted
Community Support
Community Support

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"

 

Capture.PNG

 

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
Highlighted
Helper II
Helper II

Thank you guys for all the help, but I was looking back at my data and my example doesn't really reflect the case, it was a bad example from my part, sorry. What I do have here is:

 

Software | Version
Foo Bar XYZ | Foo Bar XYZ 2016 64 Bits
Foo Bar XYZ | Foo Bar XYZ 2017 64 Bits
Dat Amazing Tool | Dat Amazing Tool 10.5.4.23 32 Bits
Dat Amazing Tool | Dat Amazing Tool 10.5.4.23 64 Bits
@Cool App | @Cool App 1.5 32 Bits
@Cool App | @Cool App 1.5 64 Bits

The entries inside my Software column my have 1 word, 2 words, N words and symbols. The entries inside my Version column will always have the [Software name] + [version] + [32/64 Bits]. The [version] may use year (1999, 2010, 2017, etc) or numbers (5, 10, 2, 11.2, 1.0, 5.2.3, 10.2...N...5). That's why I need to get whatever is inside the Software column and replace for nothing inside my Version colum for each and all rows.

 

Not only that, I also need to remove the Bits, but I believe I can do that replacing "Bits" to "" and them doing a split using the last SPACE so I end up getting the 32/64 part.

Highlighted

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.

 

Remove text in one column based on another column.png

 

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

Specializing in Power Query Formula Language (M)

View solution in original post

Highlighted

Following up on this example...

 

What if I wanted to remove text from a string in one column based on the text in another column using functions, when there is no other clear delineation such as a space

 

Using JChris's original data as an example: What if I wanted to remove the CarName text from the Year column based on it appearing in the CarName column? Is there a programmatic way to do that, or would it require a clear delimiter such as a space, a hyphen, etc.?

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors