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: blabl...
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"

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