Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Conditional Split Column by delimiter based on value

I'm doing some cheeky data modelling in Query Editor, I have a column which basically holds an API call - which includes useful information I want split out. 

 

Therefore I'm trying to create a conditional column which splits the API column by a text delimiter.

If the API column contains 'GUID' then split after 'GUID' else if API column contains 'Member ID' then split after 'Member ID' else null.

 

This will then give me a new column which holds either a Member ID or GUID or nothing. 

 

Here is what I have strung together which does not work:

 

= Table.AddColumn(PreviousStep, "New Column", each if Text.Contains([API], "guid") then Splitter.SplitTextByEachDelimiter({"guid "}) else if Text.Contains([API], "member id") then Splitter.SplitTextByEachDelimiter({"member id "}) else null)
Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    The solution I used was to Extract Text Between Delimeters:

     

    Under Add Columns in Query Editor, Extract - Text Between Delimiters - and I entered the text string before and after the value I wanted to extract.

    This will only work if the text string is consistent.

7 Replies

  • v-diye-msft's avatar
    v-diye-msft
    Community Support
    6 years ago

    Hi Anonymous 

     

    Please try below M code:

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMnIP9XQxVorViVbyTc1NSi1SgHGxSMDFY0oNDIzMUMWNMJVBxI1BUCk2FgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
    #"Added Conditional Column" = Table.AddColumn(#"Changed Type", "Custom", each if Text.Contains([Column1], "GUID") then "GUID" else if Text.Contains([Column1], "Member ID") then "Member ID" else null)
in
    #"Added Conditional Column"

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi v-diye-msft 

       

      I think your code is just extracting the word GUID and Member ID. I actually want the text that appears after these words, so if text contains "GUID" then I want the actual ID number that appears after it. 

      Fortunately, the GUID or Member ID appear at the end of the API string.

       

      • mussaenda's avatar
        mussaenda
        Community Champion
        6 years ago

        Hi Anonymous ,

         

        can you provide a sample data and your expected output so we can text and help you easier.

        Thank you