cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Post Prodigy
Post Prodigy

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)
1 ACCEPTED SOLUTION

Accepted Solutions

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.

View solution in original post

7 REPLIES 7
Community Support
Community Support

Hi @heytherejem 

 

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"

06.PNG

 

Community Support Team _ Dina Ye
If this post helps, then please consider Accept it as the solution to help the other members find it more
quickly.

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.

 

Hi @heytherejem ,

 

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

Thank you





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Hi @heytherejem 

 

Please give me some examples.

Community Support Team _ Dina Ye
If this post helps, then please consider Accept it as the solution to help the other members find it more
quickly.

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

Thanks anyway

@heytherejem,

 

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.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




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.

View solution in original post

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors