Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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êsUser | Count |
---|---|
97 | |
88 | |
76 | |
70 | |
63 |
User | Count |
---|---|
112 | |
96 | |
95 | |
67 | |
65 |