cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
danmcauley
New Member

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:

power bi warning.PNG

 

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

3 REPLIES 3
rsarapalli
Frequent Visitor

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
Community Champion
Community Champion

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:
Removing text after character or between characters.png

 

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:

 

Removing text after character or between characters - Result.png

 

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

Specializing in Power Query Formula Language (M)
MFelix
Super User
Super User

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 )

spLIT.png

 

Regards,

MFelix

 

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!