M Code to extract data out of string

Power Query
1

Hi eDNA members,
Could I get some help please?
I have a long string and in a new column I would like to extract either “KC3-” or “KZ3-” and then next 5 digits.
eg, string “DSCT2KC3-DOG05CONQ03253246-02” extract “KC3-DOG05”
“DSCT2KZ3-FIS05CONQ03253246-03” extract “KZ3-FIS05”
if string doesn’t contain either KC3 or KZ3 then “-”

File is attached. Thank you
Extract data.xlsx (19.7 KB)

image
image668×579 15.2 KB

2

Hello @KimC,

Thank You for posting your query onto the Forum. Paste the below provided code into the blank query -

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("rdLLTsMwEAXQf8kaK/PwOPYSJW2BqolCWaRUWbDoulIFIp+P8yhEptQgsbTu0cy17P0+Kbb5E61zVkW1AsmrsgYmYdJGASXtzShwEIscdHl47wkKmkwU4N0vzOLTRDf1+WpTAE4zvCBCP+Ma0FkMzCdMRQtPxhIsmtEGI5qmOedo/T1glqc9qHc1YOqXpN6QJ8ZxzIB17j9MsCvW98rTVH8xl7oQG4rdGyQz8HNftuzAzPvS+jnyIQexvN+GggPxeBsK/SU2l7fIKL4HZgjEOoKpChJbx/xQDici1XUyJWr5cjoc317Pzkddl7TtBw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [PO = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"PO", type text}}),
    
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each if Text.Contains([PO], "KC3-") then "KC3-" else if Text.Contains([PO], "KZ3-") then "KZ3-" else "-"),
    #"Inserted Text After Delimiter" = Table.AddColumn(#"Added Custom", "Text After Delimiter", each Text.AfterDelimiter([PO], "-"), type text),
    #"Inserted First Characters" = Table.AddColumn(#"Inserted Text After Delimiter", "First Characters", each Text.Start([Text After Delimiter], 5), type text),
    #"Added Custom1" = Table.AddColumn(#"Inserted First Characters", "Custom.1", each if [Custom] = "KC3-" then [Custom] & [First Characters] else 
if [Custom] = "KZ3-" then [Custom] & [First Characters]
else "-"),
    #"Removed Columns1" = Table.RemoveColumns(#"Added Custom1",{"Custom", "Text After Delimiter", "First Characters"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns1",{{"Custom.1", "New Column"}}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Renamed Columns",{{"New Column", type text}})
in
    #"Changed Type2"

Once you paste the code, you’ll get the desired results. Below is the screenshot of the final results provided for the reference -

Final Results
Final Results669×1051 48.1 KB

I’m also attaching the working of the PBIX file so that you can go through the steps applied in the Power Query for the reference purposes.

Hoping you find this useful and meets your requirements that you’ve been looking for. :slightly_smiling_face:

Thanks and Warm Regards,
Harsh

Extract Data Out Of String - Harsh.pbix (16.1 KB)

4 Likes
3

Thankyou Harsh, it is very useful and I appreciate your help. I like how you approached it in two steps.

1 Like
4

Hi @KimC and @Harsh,
With the intention of learning by answering questions in the forum, I share the solution that I have found.

let
Source = Excel.Workbook(File.Contents(“C:\Enterprise DNA\Forum\M Code to extract data out of string\Extract data.xlsx”), null, true),
Table1_Sheet = Source{[Item=“Table1”,Kind=“Sheet”]}[Data],
#“Changed Type” = Table.TransformColumnTypes(Table1_Sheet,{{“Column1”, type text}}),
#“Added Conditional Column” = Table.AddColumn(#“Changed Type”, “Result”, each if Text.Contains([Column1], “KC3-”) then Text.Range([Column1],Text.PositionOf([Column1],“KC3-”),9 ) else if Text.Contains([Column1], “KZ3-”) then Text.Range([Column1],Text.PositionOf([Column1],“KZ3-”),9 ) else “-”, type text)
in
#“Added Conditional Column”

Add a custom column where if the string contains the substring ‘KC3-’ or ‘KZ3-’, it returns the substring from the position of ‘KC3-’ or 'KZ3- ’ counting 9 characters. Otherwise it returns ‘-’

ExtractText
ExtractText1363×723 67.3 KB

Regards

M Code to extract data out of string_JAFP.pbix (16.2 KB)

1 Like
5

Hi @KimC ,

Please find the solution as required. let me know if you have any further query ?

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"PO", type text}}),
    #"Added Conditional Column" = Table.AddColumn(#"Changed Type", "Custom", each if Text.Contains([PO], "KC3") then Text.Middle([PO],Text.PositionOf([PO],"KC3"),9) else if Text.Contains([PO], "KZ3") then Text.Middle([PO],Text.PositionOf([PO],"KZ3"),9) else "-")
in
    #"Added Conditional Column"

Extract data.xlsx (22.2 KB)

3 Likes
6

@rajender1984 & @jafernandezpuga that is nice work to solve it in one line of mcode. Thank you both very much

1 Like
7

It’s great to know that you are making progress with your query @KimC.

Please don’t forget if your question has been answered within the forum it is important to mark your thread as ‘solved’.

We request you to kindly take time to answer the Enterprise DNA Forum User Experience Survey,We hope you’ll give your insights on how we can further improve the Support forum. Thanks!

8

Hi @KimC, we’ve noticed that no response has been received from you since a few days ago.

We just want to check if you still need further help with this post?

In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved.