Forum Discussion

daniegajohnson's avatar
daniegajohnson
Frequent Visitor
6 years ago
Solved

Keep text between // and /

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

Need Help
  • v-lid-msft's avatar
    v-lid-msft
    6 years ago

    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,

     

  • Jimmy801's avatar
    Jimmy801
    6 years ago

    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

6 Replies

  • Jimmy801's avatar
    Jimmy801
    Community Champion
    6 years ago

    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's avatar
      daniegajohnson
      Frequent Visitor
      6 years ago

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

      • v-lid-msft's avatar
        v-lid-msft
        Community Support
        6 years ago

        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's avatar
    v-lid-msft
    Community Support
    6 years ago

    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,