Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Extract domain parts

hello all, I am trying to extract Domain and sub domain, so far this M query,

let splitUrlandTitle = Splitter.SplitTextByDelimiter("/", QuoteStyle.None)([UrlandTitle]), splitsplitUrlandTitle2 = List.Reverse(Splitter.SplitTextByDelimiter(".", QuoteStyle.None)(splitUrlandTitle{2}?)) in splitsplitUrlandTitle2{2}? 

gives the best result, it returns "lemoncity" but does not work on url's without a leading "/". I have url's like

https://desktop.lemoncity.ca.gov 

http://desktop.lemoncity.ca.gov 

desktop.lemoncity.ca.gov

lemoncity.ca.gov/more/more

lemoncity.ca.gov:404

Is there a way to insert a condition where if the text does not contain "/" then extract using another method?

thanks

Need Help
  • Vijay_A_Verma's avatar
    Vijay_A_Verma
    3 years ago

    Does your text always contain ca.gov as the domain extension? If yes, below will work.

    (But if your code contains various other domain extensions also, then we will need to think about other strategy)

     

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WyigpKSi20tdPSS3OLskv0MtJzc3PS84sqdRLTtRLzy9TitWBKCKkBq8kuqB+bn5RKpjAKm1lYmACtxif69DMgSnHpywWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [UrlandTitle = _t]),
    #"Added Custom" = Table.AddColumn(Source, "Domain Name", each List.Last(Text.SplitAny(Text.BeforeDelimiter([UrlandTitle], ".ca.gov"), "./"))),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Sub Domain Name", each List.Last(List.RemoveLastN(Text.Split(Text.AfterDelimiter(Text.BeforeDelimiter(if Text.Contains([UrlandTitle],"//") then [UrlandTitle] else "//" & [UrlandTitle], ".ca.gov"), "//"), "."), 1)))
in
    #"Added Custom1"

     

     

4 Replies

  • Vijay_A_Verma's avatar
    Vijay_A_Verma
    Most Valuable Professional
    3 years ago

    Does your text always contain ca.gov as the domain extension? If yes, below will work.

    (But if your code contains various other domain extensions also, then we will need to think about other strategy)

     

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WyigpKSi20tdPSS3OLskv0MtJzc3PS84sqdRLTtRLzy9TitWBKCKkBq8kuqB+bn5RKpjAKm1lYmACtxif69DMgSnHpywWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [UrlandTitle = _t]),
    #"Added Custom" = Table.AddColumn(Source, "Domain Name", each List.Last(Text.SplitAny(Text.BeforeDelimiter([UrlandTitle], ".ca.gov"), "./"))),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Sub Domain Name", each List.Last(List.RemoveLastN(Text.Split(Text.AfterDelimiter(Text.BeforeDelimiter(if Text.Contains([UrlandTitle],"//") then [UrlandTitle] else "//" & [UrlandTitle], ".ca.gov"), "//"), "."), 1)))
in
    #"Added Custom1"

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      Thank you for the M, I am getting great results, there are a few where the extract in not happening but in similar strings the extract is happening? like these stings below from the title column

       

      Anonymous OMG Name on "omg.ca.gov"
      Session Obsession on "workinfo.workinginfo.ca.gov"
      Open reversal Blahblah on somecity.ca.gov
      Open reversal on oops.ca.gov
      OEM Misunderstanding at oops.ca.gov

      Thanks for the help

      • Vijay_A_Verma's avatar
        Vijay_A_Verma
        Most Valuable Professional
        3 years ago

        Use this

        let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("hZAxC4MwEIX/SnAusYOTWwulk3XoqA5RryqaXPCixX9fjbagFB0SHnnfyzsuipzSGE2+6+ZAtUHNG5CossoMPBO8wN5JTjN0xOya20dXYgv2+mv73tn7Fe9Nt/nnix9gF4VqkNgRC4M7ewgJDBWLHZTFEogdCz6BqBqtMKVFWe6NbV2pF/JZFFavgqEGxVrooSXRsGsjynQ8U5pQwnY7a3pqQU0r4BawoKJO5SNihMrHUibMmks+", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [UrlandTitle = _t]),
    #"Added Custom" = Table.AddColumn(Source, "Domain Name", each [a = List.Last(Text.Split([UrlandTitle]," ")), b=List.Last(Text.SplitAny(Text.BeforeDelimiter(Text.Replace(a,"""",""), ".ca.gov"), "./"))][b]),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Sub Domain Name", each [a = List.Last(Text.Split([UrlandTitle]," ")), b=List.Last(List.RemoveLastN(Text.Split(Text.AfterDelimiter(Text.BeforeDelimiter(Text.Replace(if Text.Contains(a,"//") then a else "//" & a,"""",""), ".ca.gov"), "//"), "."), 1))][b])
in
    #"Added Custom1"