Between Delimiters for various different domain names and domain extensions

[Anonymous]

I have a plethora of different types of URL's and i would like to extract the domain names only and leave everything else. The problem is when i do a transformation to extract, only 1 start delimiter is available to do multiple URL's? For example: www.powerbi.com extracted is "powerbi" but as soon as a https://www.powerbi.com instance happens it gives an error or extracts improperly? Can i do multiple delimiter conditions to create a column with the domain in text IE; "powerbi"??

[tackytechtom]

Hi @Anonymous ,

How about this:

You can find this actions under transform > extract > Text between delimeters:

Does this help? 🙂

/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/

[Anonymous]

Thank you very much but not quite. I've tried those methods and it's more intricate then that. I need to be able to set multiple conditons. Some including the above are these URL types: "https://thesouth.com" or "thesouth.com" or "www.thesouth" or "https://www.thesouth.com" or "thesouth.gov" = "thesouth".

I get some results to extract but not all. I need them all if possible?

[Vijay_A_Verma]

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("i45WyigpKSi20tcvyUgtzi8tydBLzs9VitWJVsIQKC8v14MJYgjAVcHMwyoJF0jPL1OKjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Data = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Data", type text}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type","https://","",Replacer.ReplaceText,{"Data"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","www.","",Replacer.ReplaceText,{"Data"}),
    #"Extracted Text Before Delimiter" = Table.TransformColumns(#"Replaced Value1", {{"Data", each Text.BeforeDelimiter(_, "."), type text}})
in
    #"Extracted Text Before Delimiter"

[Anonymous]

Thank you very much. But im six weeks in and i tried to replace the source and can't seem to grasp the concept of it?? How would i go about changing it appropriately?? 

[Vijay_A_Verma]

To understand the concept, do it manually - Select the column - Right click - Replace values - Put https:// in Value to Find and OK...This will replace https:// with blank.

The generates this statement

#"Replaced Value" = Table.ReplaceValue(#"Changed Type","https://","",Replacer.ReplaceText,{"Data"}),

[Anonymous]

That is already including in the above test code. What do i remove from my query to add to the above code to extract all of the unique URL's?? Here is my query? What do i remove to add to your code?

[Vijay_A_Verma]

In PQ, please see what is the column name which contains URL- Note it.

Remove all lines starting with #"Removed Columns" in your code.

Copy my code starting with #"Replaced Value" till end and paste in your code.

Now, replace "Data" in my code wherever it appears with the column name which contains URL.

[mramstead1]

Hello Luis,

I was able to split https://www.powerbi.com into three separate columns. Here's what they looked like:

From my understanding of your post, you would be able to delete columns 1 and 3 after splitting, keeping only "powerbi" in the column.

If this is what you are looking for here are the steps I used to do this:

1. Right click column -> Split Column -> By Delimiter

2. "Select or enter delimiter" = Select --Custom--

Use these setting and hit ok.

Hopefully this achieves what you are looking for! 

--Michael