cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Oresto Frequent Visitor
Frequent Visitor

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

Accepted Solutions
gooranga1 Senior Member
Senior Member

Re: Extract ID from textstring

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
gooranga1 Senior Member
Senior Member

Re: Extract ID from textstring

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

 

Oresto Frequent Visitor
Frequent Visitor

Re: Extract ID from textstring

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 Senior Member
Senior Member

Re: Extract ID from textstring

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 

gooranga1 Senior Member
Senior Member

Re: Extract ID from textstring

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

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 206 members 2,208 guests
Please welcome our newest community members: