Forum Discussion

LFK73's avatar
LFK73
New Member
4 years ago
Solved

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.

 

 

Need Help
  • deevaker's avatar
    deevaker
    4 years ago

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


3 Replies

  • deevaker's avatar
    deevaker
    Resolver I
    4 years ago

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


  • latimeria's avatar
    latimeria
    Solution Specialist
    4 years ago

    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's avatar
      GB74
      Frequent Visitor
      1 year ago

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