Extract ID from textstring

[Anonymous]

Hi 

Im a newbie in power query so hoping that you got a better understanding then me. I got a lot of rows with text that can look like this:

1: blabla blabla blalbla ID456 blablabla

2: blabla blablalbla ID476 blablabla

From these two rows I want to extract ID456 and ID476 and put them in a column of them own. I have found the "extract between delimiter"-option and use ID to <blank>. However then I only get the numbers from the string, so I would need an option pick everything between ID and <blank>, but start two position to the left.

Any ideas?

[gooranga1]

You can do it in dax.

Here is basic formulas for a new column in a table. You would need to add error handling for data that does not comply to the example you gave.

Find ID = find("ID",Table14[Column1])

Find Space After ID = find(" ",Table14[Column1],Table14[Find ID])

ID = mid(Table14[Column1],Table14[Find ID],Table14[Find Space After ID]-Table14[Find ID])

You should be able to do the same in the Edit queries section as well. It works for those examples but you may find not all the data complies to this pattern.

[Anonymous]

Thanks!

Sorry however if I wasnt clear, I need the function to work in power query. This is a loading table to another query at the end of the process

[gooranga1 · Accepted solution]

You could try splitting it by space and only select items starting with ID?

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSspJBCIFBJUDoj1dTEzNoIJApBSrg6YSpsocRVUsAA==", 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 Custom" = Table.AddColumn(#"Changed Type", "Custom", each Text.Split([Column1]," ")),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"),
    #"Filtered Rows" = Table.SelectRows(#"Expanded Custom", each Text.StartsWith([Custom], "ID"))


   
in
    #"Filtered Rows"

[gooranga1]

You could try something that splits the text into rows based on the spaces but only selects rows that begin "ID" for example.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSspJBCIFBJUDoj1dTEzNoIJApBSrg6YSpsocRVUsAA==", 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 Custom" = Table.AddColumn(#"Changed Type", "Custom", each Text.Split([Column1]," ")),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"),
    #"Filtered Rows" = Table.SelectRows(#"Expanded Custom", each Text.StartsWith([Custom], "ID"))


   
in
    #"Filtered Rows