Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Vijay_A_Verma
Super User
‎04-09-2022 04:16 AM

Extract Domain from URLs in Power Query Language M

Use Case - Sometimes there will be a need to extract domain names from given URLs. Hence http://www.microsoft.com should give microsoft. Below are some sample cases and the results expected.

domain.png

 

Exclusion - Subdomains are excluded from the scope of this. Hence, https://powerbi.microsoft.com which has subdomain powerbi.microsoft is excluded.

 

Solution - Following formula can be used to extract domain from a given URL

 

= Text.Lower(List.RemoveMatchingItems(Text.Split(Text.Replace([URL],"://","."),"."),{"https","http","www"},Comparer.OrdinalIgnoreCase){0})

 

To see its working on sample data - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test (later on when you use the query on your dataset, you will have to change the source appropriately)

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ZY8xT8QwDIX/S2eUnIRAp5NOLAyICaYOVYdcSZSIujaxe4F/f4kLJxBb/N7z++Jh6KII8cHaUoohLD6fkpkQuvFm6HSmdEYxwKq01EtTX1efv0xaVO37vgaC6ABsJqcvyW7hgPlXmZab9V2Vp4quZGAMcmU2AqQp47f6w4hbWFvAUcsf9rur9ecEQZy5JWxE8CYKzBrUnzuizUvLm/9s5nZDXcYQ0uRv7+/Upw/LDmj2/I9S6i6WrYbIimdRysPKPh+fMS6P6LtxvAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [URL = _t]),
    #"Added Custom" = Table.AddColumn(Source, "Domain", each Text.Lower(List.RemoveMatchingItems(Text.Split(Text.Replace([URL],"://","."),"."),{"https","http","www"},Comparer.OrdinalIgnoreCase){0}))
in
    #"Added Custom"