Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

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?

Need Help
  • gooranga1's avatar
    gooranga1
    7 years ago

    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"

4 Replies

  • gooranga1's avatar
    gooranga1
    Power Participant
    7 years ago

    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's avatar
      Anonymous
      Not applicable
      7 years ago

      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's avatar
        gooranga1
        Power Participant
        7 years ago

        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