Keep text between // and /

[daniegajohnson]

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

[v-lid-msft · Accepted solution]

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,

[v-lid-msft]

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,

[Jimmy801]

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

[daniegajohnson]

@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 /).

[Anonymous]

Try Text.BetweenDelimiters

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

[Jimmy801 · Accepted solution]

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

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

[v-lid-msft · Accepted solution]

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,