Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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)
Solved! Go to 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.
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"
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 @Anonymous
Please give me some examples.
It's ok I figured out how to do it myself!
Thanks anyway
@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.
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.
Hi @Anonymous ,
can you provide a sample data and your expected output so we can text and help you easier.
Thank you
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
95 | |
76 | |
65 | |
51 |
User | Count |
---|---|
146 | |
109 | |
106 | |
88 | |
61 |