cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper I
Helper I

Remove last occurence of specific character?

Hi

 

Wondering if anybody here can help me out in, I'm trying to trim the a column so that the last occurrence of a specific character is removed. The example is: domain.com/OU/OU2/OU3/User1

 

I want to remove the last part /User1

 

Is it possible?

 

/Alx

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Microsoft
Microsoft

Re: Remove last occurence of specific character?

@baatch

 

You can use the Split Column function in Query Editor to get your expected result. Assuming we have a table as below. Select the column and click split column by delimiter.

Remove last occurence of specific character_1.jpg

 

Select custom delimiter / as below. At last you only need to delete the unnecessary Column.2.

Remove last occurence of specific character_2.jpg

 

Best Regards,

Herbert

View solution in original post

5 REPLIES 5
Highlighted

Re: Remove last occurence of specific character?

There is a function Text.PositionOf(input, "\", Occurrence.Last) that will return the position of the last instance.  From there you can trim it off with Text.Start(input, lengthFromAboveFormula)



* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.
Highlighted
Helper I
Helper I

Re: Remove last occurence of specific character?

Sorry Matt, I'm a litte new to this.

 

Would it be possible to have a more detailed answer?

 

I found out how I achive what I want to do in Excel with this formula: =LEFT([canonicalName],FIND("@",SUBSTITUTE([canonicalName],"/","@",LEN([canonicalName])-LEN(SUBSTITUTE([canonicalName],"/",""))))-1)

 

But when I try it in PowerBI as a new Column it does not work and says: "Expression.Error: The name 'LEFT' wasn't recognized. Make sure it's spelled correctly."

 

Any ideas?

Highlighted

Re: Remove last occurence of specific character?

You can't use Excel formulas when you get data in Power Query.  Check out my demo workbook.

 

https://www.dropbox.com/s/qacnvgy522fl7i0/Position%20of%20Last.pbix?dl=0

 

Go to edit queries to see what I did.



* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.
Highlighted
Microsoft
Microsoft

Re: Remove last occurence of specific character?

@baatch

 

You can use the Split Column function in Query Editor to get your expected result. Assuming we have a table as below. Select the column and click split column by delimiter.

Remove last occurence of specific character_1.jpg

 

Select custom delimiter / as below. At last you only need to delete the unnecessary Column.2.

Remove last occurence of specific character_2.jpg

 

Best Regards,

Herbert

View solution in original post

Highlighted
Helper I
Helper I

Re: Remove last occurence of specific character?

Thank you  @v-haibl-msft !!!

 

That was so easy Smiley Very HappySmiley Very HappySmiley Very Happy

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Upcoming Events

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

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

Top Solution Authors
Top Kudoed Authors