Removing text after character or between characters

[danmcauley]

Hi,

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.

Example data:

Page

homepage/toolA/en?code=12OP90

contactus/toolC/en?code=FV45TY

homepage?code=H786GH

toolF/homepage/en

toolF/homepage?code=987234

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?

Thanks

[rsarapalli]

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

Hope this helps.

Thanks

[MarcelBeug]

It looks like you are mixing up DAX, Power Query and Excel.

Also your requirements are not very clear.

Just a wild shot:

In the Query Editor, I created a Parameter SearchValues:

And I created the following query that adds a column with found keywords:

let
    Source = Table1,
    #"Added Custom" = Table.AddColumn(Source, "FoundKeyWords", (This) => Text.Combine(List.Select(Text.Split(SearchValues,","),each Text.Contains(This[Page],_)),", "))
in
    #"Added Custom"

With adjusted example data, the result looks like:

If this is not what you are looking for, then it might still help you to reformulate your requirements.

[MFelix]

Hi @danmcauley,

Although many formulas may seem similar to Excel we cannot use them exactly as we do in Excel, I have made this small change to your formula  and it gives the expected result:

Column
Split = LEFT( 'Pages'[Page]; 
           SEARCH("/";'Pages'[Page]; 1; 
               LEN('Pages'[Page])-1
             )-1
         )

Regards,

MFelix