Conditional Split Column by delimiter based on value

[Anonymous]

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)

[v-diye-msft]

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]

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.

[v-diye-msft]

Hi @Anonymous 

Please give me some examples.

[Anonymous]

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

Thanks anyway

[mussaenda]

@Anonymous,

if you have figured it out yourself, It will be better to share it here then mark it as a solution so others can also rely on your solution.

Thank you.

[Anonymous · Accepted solution]

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.

[mussaenda]

Hi @Anonymous ,

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

Thank you