Create new columns with substrings from another column

[LFK73]

I know there are a number of articles on this subject but I cant seem to make any of these work for me.  I have a table populated by an API call.  I need to create 2 columns taking part of the string in the URL column for each row so I can use a slicer.

I need on column (call it location) that is the word found between the 2nd and 3rd "/".  So first and second line it would be "asia", third and forth line would be "americas" etc

The seocnd additioanl column is everything after the 3rd "/" (call is country).  We would have 1st line cambodia, 2nd line sri-lanka etc.

I feel this is probably sopmething straight forward but just can't get it to work.

[latimeria]

Hi @LFK73 ,

In PowerQuery, just click on "Column from example" 

Code you will get:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fc9BDsMwCETRu3jdiANFWUxtkqDGYBn7/m3VLc2aJ/FnXRMV9iGKIaZOcAFl1KcVQdoe4d27LBf0FYLKXTKcjonBFdctalDUUOxfQG5znIvPAg0Qz26NSXmc3D9Bxf8VixZT9njS7xX0sDC2IcsumVQmp217Aw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [url = _t]),
    #"Inserted Text Between Delimiters" = Table.AddColumn(Source, "Text Between Delimiters", each Text.BetweenDelimiters([url], "/", "/", 1, 0), type text),
    #"Inserted Text After Delimiter" = Table.AddColumn(#"Inserted Text Between Delimiters", "Text After Delimiter", each Text.AfterDelimiter([url], "/", 2), type text)
in
    #"Inserted Text After Delimiter"

[GB74]

Thank you so much latimera. I never new what Column From Examples did. That solved a similar issue for me.

[deevaker · Accepted solution]

Hi Mate,

You can use this calculation to get the location : 

Location =

Var _First = Len(LEFT('Table'[Url],SEARCH("/",'Table'[Url],2)))

Var _Second = Len(Left('Table'[Url], SEARCH("/",'Table'[Url],_First+1)))

return

MID('Table'[Url],_First+1,_Second-_First-1)

(Please refer to the attached screenshot)

Same logic you can use to get the country . 🙂