Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 numbers start with M follow by some numbers and a letter in between or just a number. The end result should be like :
Thank you in advance!
Solved! Go to Solution.
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"
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"
Hi, @gio1082
Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.
Table:
Here are the codes in 'Advanced Editor'.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCggIVChKLcgvKlFIyy9S8DU3MjM3NXC2MDbWgXPMzUyVYnUgaoMgan0tLUxMLUwcDQwMdaAcUyAHizIzI2MjuDJjS0uQMmO4OiSrA/yUDQzMTIwNzYCaTEyMLBWCPf18lGJjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Text = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Text", type text}}),
Custom1 = Table.ReplaceValue(#"Changed Type",","," ",Replacer.ReplaceText,{"Text"}),
#"Split Column by Delimiter" = Table.SplitColumn(Custom1, "Text", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"Text.1", "Text.2", "Text.3", "Text.4", "Text.5"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Text.1", type text}, {"Text.2", type text}, {"Text.3", type text}, {"Text.4", type text}, {"Text.5", type text}}),
Custom2 = List.Combine(
{
Table.Column(#"Changed Type1","Text.1"),
Table.Column(#"Changed Type1","Text.2"),
Table.Column(#"Changed Type1","Text.3"),
Table.Column(#"Changed Type1","Text.4"),
Table.Column(#"Changed Type1","Text.5")
}
),
#"Converted to Table" = Table.FromList(Custom2, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
Custom3 = Table.SelectRows(#"Converted to Table",each
let
column = [Column1],
txt = Text.Middle([Column1],1,Text.Length([Column1])-1)
in
(Text.StartsWith(column,"M") and Text.PositionOfAny(txt,{"A".."Z","a".."z"})>=1 and Text.PositionOfAny(txt,{"A".."Z","a".."z"})<=Text.Length(txt)-2
) or
Text.Contains(column,"#")
),
#"Added Custom" = Table.AddColumn(Custom3, "Custom", each if
Text.Contains([Column1],"#") then
Text.Middle(
[Column1],
Text.PositionOf([Column1],"#")+1,
Text.Length([Column1])-1
)
else
[Column1]),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Column1"})
in
#"Removed Columns"
Result:
Best Regards
Allan
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
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"
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!
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.
Can you help me extract all the part numbers that start with M?
It didn't work :(, but thanks for all you help!
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"
this worked!!!
@gio1082 - Well, for the M ones you could do something like this:
Column = MID([Column],SEARCH(" M",[Column])+ 1, 11)
It gave me an error 😞
from what little you see in the scree shot, I assume that the strings to be extracted are those that contain an "M" or a "#" and are between a space and a comma.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WKigoVChKLcgvKlFIyy9S8DU3MzE1tnA3NTHWAXJMLUwNLZzMTE10FDIOb8o5vEkpVgehB67DzNzUFF2XG1hXYlIyihaoNQF5yhCLDAww9aSXpKSBNZHtMAJ60tKQHAVzkR/URWRbADIBrMvSwpxMhyH0xAIA", 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", "extract", each List.Select(Text.SplitAny([Column1]," ,"),(p)=> Text.Contains(p,"M") or Text.Contains(p,"#"))),
#"Expanded extract" = Table.ExpandListColumn(#"Added Custom", "extract")
in
#"Expanded extract"
Hi,
Well, in general cases it looks like this.
but sometimes it will be in between parenthesis
other before a #
Also not sure how to use your code. Do I use it as a new column? what are the items I need to change to make it work for mine?
thanks
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.