Forum Discussion
Remove text in one column based on another column
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.
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.
I created a 14 minute video about the new Text.AfterDelimiter, Text,BeforeDelimiter and Text.BetweenDelimiters functions.
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.?
