Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
115 | |
99 | |
86 | |
70 | |
62 |
User | Count |
---|---|
151 | |
120 | |
103 | |
87 | |
68 |