Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
JChris
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

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
JChris
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.

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)

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.?

v-shex-msft
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.
Vvelarde
Community Champion
Community Champion

@JChris

 

Hi, please follow this steps:

 

Split.gif




Lima - Peru
Anonymous
Not applicable

Capture1.PNG

 

 

Capture2.PNG

 

 

Capture3.PNG

 

 

Capture4.PNG

 

 

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.