Forum Discussion
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...
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"- 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 obtainedlet 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.
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.
"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.
this worked!!!