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,
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:
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
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
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:
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:
If this is not what you are looking for, then it might still help you to reformulate your requirements.
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 )
Regards,
MFelix
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsCovering 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 |
---|---|
109 | |
98 | |
80 | |
64 | |
57 |
User | Count |
---|---|
145 | |
110 | |
91 | |
84 | |
66 |