Forum Discussion

gio1082's avatar
gio1082
Helper I
6 years ago
Solved

Extract part number from text

 Hi,  I have this data from a description that I have to clean up. It's a long list. I need to extract the part number (highlighted in yellow) and put it in a single column. All the part numb...
Need Help
Show and Tell
Tutorial Requests
  • Anonymous's avatar
    Anonymous
    6 years ago

    this code aspires to manage almost all cases you showed.

     

    the logic is take all the "words" that are between two of these ", # ()" [there is also a blank " "] characters that contain a digit


    //add the following lines of code to your advanced editor, in the query where there is your table.
    // if your last step is differente from #"Changed Type", change in the expressione that follows consequently

     

     

        #"Added Custom" = Table.AddColumn(#"Changed Type", "extract", each List.Select(Text.SplitAny([Column1],"# ,()"),(p)=> List.ContainsAny(Text.ToList(p) ,{"0".."9"}))),
    #"Expanded extract" = Table.ExpandListColumn(#"Added Custom", "extract")
in
//change also your expressione to this

    #"Expanded extract"

     

     

  • Anonymous's avatar
    Anonymous
    6 years ago
     
     
    if you want to be helped, you should find the time to be more precise and clear.
    "IT" can be too many things, to get an idea of what "doesn't work".
    Maybe the problem is on the name of the columns, or you and I have a different version of PBI that behaves differently on our PC.
    However here is the code with the example data used and the result obtained
     
     

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMkzJT1bSUSooKFQoSi3ILypRSMsvUvA1NzMxNbZwNzUx1gFyTC1MDS2czExNdBQyDm/KObxJKVYHQytco5m5qSm6Zjew5sSkZGw6oZYG5ClDrDUwwNSaXpKSBtZrBNFLsmuNCXtUA0mvpkJaGsSxJkgaYS71g7oUrA3ZQl8Lc3OLUEsLZJtNCdsMMhBsiqWFObrDzciMoVgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "extract", each List.Select(Text.SplitAny([Column2],"# ,()"),(p)=> List.ContainsAny(Text.ToList(p) ,{"0".."9"}))),
    #"Expanded extract" = Table.ExpandListColumn(#"Added Custom", "extract")
in
    #"Expanded extract"




     

     
    if that's not what you want, you should indicate exactly where changes are to be made.
     
gio1082's avatar
gio1082
Helper I
6 years ago

thank you for all the help, sorry, for all the trouble. 

so here are all my columns in my tables

This is the code before your code

This is how I try to input your code 

this is the error that I get (I change [column1] to [column2], but I also tried it the 1)

what I am doing wrong? also I don't need the numbers that end DOC

 

thank you!

 

Anonymous's avatar
Anonymous
Not applicable
6 years ago

it seems that somewhere a comma is missing: each expression, except the last before in, must end with a comma

try adding a comma at end of expression that starts with #Changed Type".

After that, if it works, you could do some toilette to the table, promoting the first row to header role.

But this, seems, non essential for the moment.

 

 

  • gio1082's avatar
    gio1082
    Helper I
    6 years ago

    It didn't work :(, but thanks for all you help!

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago
       
       
      if you want to be helped, you should find the time to be more precise and clear.
      "IT" can be too many things, to get an idea of what "doesn't work".
      Maybe the problem is on the name of the columns, or you and I have a different version of PBI that behaves differently on our PC.
      However here is the code with the example data used and the result obtained
       
       

       

      let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMkzJT1bSUSooKFQoSi3ILypRSMsvUvA1NzMxNbZwNzUx1gFyTC1MDS2czExNdBQyDm/KObxJKVYHQytco5m5qSm6Zjew5sSkZGw6oZYG5ClDrDUwwNSaXpKSBtZrBNFLsmuNCXtUA0mvpkJaGsSxJkgaYS71g7oUrA3ZQl8Lc3OLUEsLZJtNCdsMMhBsiqWFObrDzciMoVgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "extract", each List.Select(Text.SplitAny([Column2],"# ,()"),(p)=> List.ContainsAny(Text.ToList(p) ,{"0".."9"}))),
    #"Expanded extract" = Table.ExpandListColumn(#"Added Custom", "extract")
in
    #"Expanded extract"




       

       
      if that's not what you want, you should indicate exactly where changes are to be made.
       
      • gio1082's avatar
        gio1082
        Helper I
        6 years ago

        this worked!!!

  • gio1082's avatar
    gio1082
    Helper I
    6 years ago

    Can you help me extract all the part numbers that start with M?