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


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.