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...
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"

     

     

Vijay_A_Verma's avatar
Vijay_A_Verma
Icon for Most Valuable Professional rankMost 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"
Anonymous's avatar
Anonymous
Not applicable
3 years ago

Thank you for the response, I appreciate your time. I tried the new code and it seems to extract too many characters to the left of ca.gov (in some cases).

I did modify your original M function, SplitAny to include a space and a colon and it worked on all but one record. 1184 of 1185. So happy with that. thanks again.

 List.Last(Text.SplitAny(Text.BeforeDelimiter([UrlandTitle], ".ca.gov"), ":./ "))) 

 

The record it did not work on is a continuous string like,

https://www.google.com/url?sa=t&rct=j&q=&esrc=s&source=web&cd=&ved=2ahUKEwjHpcuWiZOAAxUCV2wGHQyUABQQFnoECA4QAQ&url=https%3A%2F%2F  lemoncounty.ca.gov%2Fa%2F98615&usg=AOvVaw2UleUK97W2GSs_U1-1P55p&opi=89978449