Forum Discussion

danmcauley's avatar
danmcauley
New Member
9 years ago

Removing text after character or between characters

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

Need Help

3 Replies

  • rsarapalli's avatar
    rsarapalli
    Icon for Advocate I rankAdvocate I
    6 years ago

    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

     

  • MFelix's avatar
    MFelix
    Icon for Super User rankSuper User
    9 years ago

    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

     

     

  • MarcelBeug's avatar
    MarcelBeug
    Icon for Community Champion rankCommunity Champion
    9 years ago

    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.