Forum Discussion
Finding tracking number in certain columns
I'm new to the M code. All the work that i do is mainly through UI. But, i've encountered an issue which i can't seem to resolve without the help of coding. So here is the problem:
I have a table that contains a tracking number which has it's constants and variables. I wrote a formula in excel that extracts the number from certain columns, but i want it in Power query so i can automate this process (and make margin for error close to 0). Table looks like this (example):
- Cells in column A have all sorts of mixture of comments and numbers and contains possible tracking number.
- In column B there should be a tracking number extracted from each cell in column A (if there is any - if there isn't it shows "No Num").
Tracking number always starts with 100 and has 10 digits in total. Example: 1001234567. There are no special signs, spaces or whatever on left or right side of the number.
Logic behind my formula in excel is:
Find sequence that starts with 100 and extract 10 digits starting with 100. If all of the extracted characters are numbers and the length equals to 10 then extract the number. Since there could be line of numbers starting with 100 which are not tracking numbers, it moves on to the next instance and so on.
Is there a way to do this in M code? If you need any details (most likely i've slipped something to mention, please let me know).
I appreciate any help, thank you.
Hi Anonymous
My sorry for late reply. I got occupied with work
Please see attached file
Hopefully this will work with multiple columns and get you desired result
let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content], ChangedType = Table.TransformColumnTypes(Source,{{"Tracking number", type any}, {"Column 1", type text}, {"Column 2", type text}, {"Column 3", type text}, {"Column 4", type text}, {"Column 5", type text}, {"? Result ?", type any}}), #"Merged Columns" = Table.CombineColumns(ChangedType,{"Column 1", "Column 2", "Column 3", "Column 4", "Column 5"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Merged"), CharsToRemove = List.Transform({33..45,47,58..126}, each Character.FromNumber(_)), #"Added Custom1" = Table.AddColumn(#"Merged Columns", "Custom.1", each let mycolumn=[Merged] in List.Transform(Text.PositionOf([Merged], "100", Occurrence.All), each Text.Range(mycolumn,_,10))), #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Desired Result", each List.Max(List.RemoveNulls(List.Transform([Custom.1],each if List.ContainsAny(Text.ToList(_),CharsToRemove) then null else _)))) in #"Added Custom2"
8 Replies
- Zubair_Muhammad
Community Champion
Anonymous
Could you copy paste some sample data (Copiable format)?
I'll paste dummy data, but it's the same as original since it contains all kind of wording.
All the cells in column called "Remarks" are as below."eu erat semper rutrum. Fusce dolor quam, elementum at, egestas a, [100]scelerisque sed, sapien. Nunc pulvinar arcu et pede. Nunc sed orci lobortis augue scelerisque mollis. Phasellus libero mauris, aliquam eu, accumsan sed, facilisis vitae, orci. Phasellus dapibus quam quis diam. Pellentesque habitant morbi tristique senectus et netus et malesuada fames ac turpis egestas. Fusce aliquet magna a neque. Nullam ut nisi a odio semper cursus. Integer mollis. Integer tincidunt aliquam arcu. Aliquam ultrices iaculis odio. Nam interdum enim non nisi. Aenean eget metus. In nec orci. Donec nibh. Quisque nonummy ipsum non arcu. Vivamus sit amet risus. Donec egestas. Aliquam nec enim. Nunc ut erat. Sed nunc est, mollis non, cursus non, egestas a, dui. Cras pellentesque. Sed dictum. Proin eget odio. Donec est mauris, rhoncus id, mollis nec, cursus a, enim. Suspendisse aliquet, sem ut cursus luctus, ipsum leo elementum sem, vitae aliquam eros turpis non enim. Mauris quis turpis vitae purus gravida sagittis. Duis gravida. Praesent eu nulla at sem molestie sodales. Mauris blandit enim consequat purus. Maecenas libero est, congue a, aliquet vel, vulputate eu, odio. Phasellus at augue id ante dictum cursus. Nunc mauris elit, dictum eu, eleifend nec, malesuada ut, sem. Nulla interdum. Curabitur dictum. Phasellus in felis. Nulla tempor augue ac ipsum. 1001234567 Phasellus vitae mauris sit a met lorem semper auctor. Mauris vel turpis. Aliquam adipiscing lobortis risus. In mi pede, nonummy ut, molestie in, tempus eu, ligula. Aenean euismod mauris eu elit. Nulla facilisi. Sed neque. Sed eget lacus. Mauris non dui nec urna suscipit nonummy."
- Zubair_Muhammad
Anonymous
Try this
First add this custom column
let mycolumn=[Column A] in List.Transform(Text.PositionOf([Column A], "100", Occurrence.All), each Text.Range(mycolumn,_,10))
Then this one
List.RemoveNulls(List.Transform([Custom.1],each if List.ContainsAny(Text.ToList(_),{"a".."z"}) then null else _))