Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
Solved! Go to Solution.
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
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
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)
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?
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.
User | Count |
---|---|
101 | |
90 | |
79 | |
70 | |
70 |
User | Count |
---|---|
110 | |
96 | |
95 | |
74 | |
71 |