Forum Discussion

joooffice's avatar
joooffice
Helper I
6 years ago
Solved

Extracting Text between two delimiters - multiple delimiters in string

I have a series of tags in my database that when i export to excel appear in one column, split by commas. 

 

I want to extract any tag that contains "WBS" but I want the whole tag ie between the two commas.

 

I'm sure its some combination of text.contains and textbetween delimiters but the "WBS" could appear anywhere in the list, or not at all. 

 

Existing Data Expected Result
BOD - 2 adults,Mercaz - 2 adults,Trad B,WBS Burial Fees - 2 adults,Adult Learning,Mailings,Community Care,Social Action,Culture WBS Burial Fees - 2 adults
No mail,Healing  
WBS Burial Fees - 1 adult,BOD - 1 adult,Mercaz - 1 adult,Trad A WBS Burial Fees - 1 adult

 

Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    deleting the last step

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dY/BCsIwEER/Zcl5PegfNBHxYO2hgoeQw9IuEmgTSJqDfr1JS6VQPA07zJtltBayOcMBTkB9GqaINYeOPlvnEagHiU/ZgkzB0gAX5rhNVEXgxhScdS+syQ5ZIyo/jsnZ6Q2KAmPru0JX3WS9Q5WZFFig+N8sDGpx9zDmRrwyldoMwOzvseOC4TJpvX6LVmMeVO0eC2O+", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Existing Data" = _t, #"Expected Result" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Existing Data", type text}, {"Expected Result", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "extract WBS", each try List.Select( Text.Split([Existing Data],","), each Text.Contains(_,"WBS")){0} otherwise "")
 
in
    #"Added Custom"

     

    which is no longer necessary and by changing the previous step

6 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    this should work even if there are many substring contaning the key:

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dY/BCsIwEER/Zcl5PegfNBHxYO2hgoeQw9IuEmgTSJqDfr1JS6VQPA07zJtltBayOcMBTkB9GqaINYeOPlvnEagHiU/ZgkzB0gAX5rhNVEXgxhScdS+syQ5ZIyo/jsnZ6Q2KAmPru0JX3WS9Q5WZFFig+N8sDGpx9zDmRrwyldoMwOzvseOC4TJpvX6LVmMeVO0eC2O+", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Existing Data" = _t, #"Expected Result" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Existing Data", type text}, {"Expected Result", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "extract WBS", each List.Select( Text.Split([Existing Data],","), each Text.Contains(_,"WBS"))),
    #"Extracted Values" = Table.TransformColumns(#"Added Custom", {"extract WBS", each Text.Combine(List.Transform(_, Text.From), ","), type text})
in
    #"Extracted Values"

     

    if you are sure that in each row there is only one substring containing "WBS", one can semplify the code a little.

     

     

    • joooffice's avatar
      joooffice
      Helper I
      6 years ago

      Thanks, there will only be one substring containing WBS in any cell - how would i simplify the code?

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      deleting the last step

       

      let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dY/BCsIwEER/Zcl5PegfNBHxYO2hgoeQw9IuEmgTSJqDfr1JS6VQPA07zJtltBayOcMBTkB9GqaINYeOPlvnEagHiU/ZgkzB0gAX5rhNVEXgxhScdS+syQ5ZIyo/jsnZ6Q2KAmPru0JX3WS9Q5WZFFig+N8sDGpx9zDmRrwyldoMwOzvseOC4TJpvX6LVmMeVO0eC2O+", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Existing Data" = _t, #"Expected Result" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Existing Data", type text}, {"Expected Result", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "extract WBS", each try List.Select( Text.Split([Existing Data],","), each Text.Contains(_,"WBS")){0} otherwise "")
 
in
    #"Added Custom"

       

      which is no longer necessary and by changing the previous step

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        another way, a little twisted, that makes use of the text.between... function:

         

        Table.AddColumn(#"Changed Type", "extract WBS", each let suffix= Text.BetweenDelimiters([Existing Data],"WBS",",") in Text.Repeat("WBS",Number.From(Text.Length(suffix)>0))&suffix)