Remove last occurence of specific character?

[baatch]

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

[v-haibl-msft · Accepted solution]

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

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

Best Regards,

Herbert

[baatch]

Thank you  @v-haibl-msft !!!

That was so easy 

[MattAllington]

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)

[baatch]

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?

[MattAllington]

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.