Removing text after character or between characters
I've imported page from Google Analytics and I only want one specific part from the string. I can write a formula in excel to remove all text after a character ("/") however when using the formula in Power Bi i get an error.
I'm intersted in the text "homepage" and "contactus" in the above example.
In PowerBi I've tried using the split column in query editor but it gets a bit messy as I need to split multiple times on multiple characters ("/" and "?").
Checking out other posts in the forum and general excel forums I used the below in excel (with M2 being the Page column in the above example data): =LEFT(M2,FIND("/",M2)-1)
However, when using LEFT with FIND within it in Power Bi I get the below:
The column then returns #ERROR. I've seen that this may be caused because some cells may not contain the "/" character. however, when adjusting the formula to output a value if "/" is not present I still get the same result.
I was wondering if the above is possible or if I have to split columns and write a formula to check multiple columns to get the data in a single column?
I had a similar requirement to select text after the last "/(slash)" (example: /BR/1Dev/TextOfInterest and I needed to extract only the text after the last "/" Result: TextOfInterest). The method I used to do this is:
1. Select the column you are interested in and go to "Transform"
2. and click on "Extract" which is 1 of the options in "text column",
3. then choose "text after delimiter" (because I wanted text after the last "/")
4. Fill in the type of delimiter you have in your text (in my example it's / ), specify if you want to scan the text from start or end and number of delimiters to skip (in my example it is 0), then click OK and that's how I transformed the column of interest to get only the text after the last /.