cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

Extract ID from textstring

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?

1 ACCEPTED SOLUTION
Impactful Individual
Impactful Individual

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"

splitid1.PNG

View solution in original post

4 REPLIES 4
Impactful Individual
Impactful Individual

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.

 

splitid.PNG

 

Anonymous
Not applicable

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

Impactful Individual
Impactful Individual

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"

splitid1.PNG

View solution in original post

Impactful Individual
Impactful Individual

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

splitid1.PNG 

Helpful resources

Announcements
secondImage

Happy New Year from Power BI

This is a must watch for a message from Power BI!

December Update

Check it Out!

Click here to read more about the December 2020 Updates!

Community Blog

Check it Out!

Click here to read the latest blog and learn more about contributing to the Power BI blog!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors