Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
LFK73
New Member

Create new columns with substrings from another column

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.

 

LFK73_0-1658048872151.png

 

1 ACCEPTED SOLUTION
deevaker
Resolver I
Resolver I

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)
deevaker_0-1658051459158.png

Same logic you can use to get the country . 🙂 


View solution in original post

3 REPLIES 3
latimeria
Solution Specialist
Solution Specialist

Hi @LFK73 ,

In PowerQuery, just click on "Column from example" 

latimeria_0-1658075138753.png

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"

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

deevaker
Resolver I
Resolver I

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)
deevaker_0-1658051459158.png

Same logic you can use to get the country . 🙂 


Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.