topic Re: Keep text between // and / in Power Query

Keep text between // and /

daniegajohnson — Wed, 29 Jan 2020 20:21:48 GMT

I have this as my entry:

Referrer: https://junebugweddings.com/wedding-blog/tropical-chic-garden-wedding-at-the-vanderbilt-museum/

I would like to keep just what's between // and the first /, so in this case the output desired would be junebugweddings.com

Any help would be greatly appreciated.

Best,

Dan

Re: Keep text between // and /

Jimmy801 — Wed, 29 Jan 2020 20:36:16 GMT

Hello daniegajohnson

check out this solution. you can apply Text.PositionOf to identify the position of // and / and then cut the string twice.

Here the complete solution

let Quelle = "https://junebugweddings.com/wedding-blog/tropical-chic-garden-wedding-at-the-vanderbilt-museum/", Pos1 = Text.PositionOf(Quelle, "//"), DeleteFirstPart = Text.End(Quelle,Text.Length(Quelle)-Pos1-2), Pos2 = Text.PositionOf(DeleteFirstPart,"/"), DeleteLastPart = Text.Start(DeleteFirstPart,Pos2) in DeleteLastPart

Copy paste this code to the advanced editor in a new blank query to see how the solution works. If this solution fits your need, copy and past a part of it and implement it in your query

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

Re: Keep text between // and /

daniegajohnson — Wed, 29 Jan 2020 21:08:57 GMT

Jimmy801thanks! That worked for the scenario I described. As I looked through the data I was getting errors because not all the entries in that referral column are alike. For example, "Referrer:" (when the visitor comes from an internal page and there's no data after the word refferrer) or "Referrer: android-app://com.google.android.googlequicksearchbox" (no single /).

Re: Keep text between // and /

v-lid-msft — Thu, 30 Jan 2020 06:58:20 GMT

Hi daniegajohnson ,

We can create a custom column to meet your requirement:

let d1 = Text.PositionOf([Referrer],"//"), d2 = Text.PositionOf(Text.End([Referrer],d1),"/") in Text.Range([Referrer],d1+2,if d2=-1 then Text.Length([Referrer])-d1-2 else d2)

All the queries are here:

let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("NY09EoIhDETvQm2g9yrMVwTIQBQI8qMeXwos3+y+XWsV1tCFA2Brd2O8FB1FYiZ9goOvxf45CLtPTr7qulmV5mxjO49Vya34oRC4xqH3hjkALks0s0tjjxl8Yg8Re6AK/wZOmIngve+oO84Tyhq0ilHX9QM=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Referrer = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Referrer", type text}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each let d1 = Text.PositionOf([Referrer],"//"), d2 = Text.PositionOf(Text.End([Referrer],d1),"/") in Text.Range([Referrer],d1+2,if d2=-1 then Text.Length([Referrer])-d1-2 else d2)) in #"Added Custom"

Best regards,

Re: Keep text between // and /

Jimmy801 — Thu, 30 Jan 2020 07:40:40 GMT

Hello daniegajohnson

then we can add 2 if-statements to check wheter the string is found or not

let Quelle = "android-app://com.google.android.googlequicksearchbox", Pos1 = Text.PositionOf(Quelle, "//"), DeleteFirstPart = if Pos1 = -1 then Quelle else Text.End(Quelle,Text.Length(Quelle)-Pos1-2), Pos2 = Text.PositionOf(DeleteFirstPart,"/"), DeleteLastPart = if Pos2 = -1 then DeleteFirstPart else Text.Start(DeleteFirstPart,Pos2) in DeleteLastPart

Re: Keep text between // and /

Anonymous — Thu, 30 Jan 2020 11:42:29 GMT

Try Text.BetweenDelimiters

let Line= "Referrer: android-app://com.google.android.googlequicksearchbox", Between= Text.BetweenDelimiters(Line,"//","/") in Between

Re: Keep text between // and /

v-lid-msft — Wed, 12 Feb 2020 06:24:49 GMT

Hi daniegajohnson ,

How about the result after you follow the suggestions mentioned in my original post?Could you please provide more details about it If it doesn't meet your requirement?

Best regards,