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

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

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

      Hi Anonymous 

       

      Please give me some examples.

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        It's ok I figured out how to do it myself! 

        Thanks anyway