Forum Discussion
Replace or remove values that are not in specified format. (Find and keep values)
Thanks to all who answered!
In fact, I took a part from everybody's answer to build my own solution.
I created a new column where...
1...all acharacters but digits and used delimeters are removed.
2...text is splitted by any defined delimeter.
3...text is combined back with delimeter "-", but only if format is correct (4 digits + 4 digits)
Community ChampionNewStep=let lst={"0".."9","#","-"},fx=(x,y,z)=>Splitter.SplitTextByCharacterTransition(x,y,z) in Table.TransformColulmns(PreviousStepName,{"Serial Number",each List.RemoveNulls(List.TransformMany(fx(lst,each not List.Contains(lst,_),_),each fx((x)=>not List.Contains(lst,x),lst,_),(x,y)=>let a=Text.SplitAny(y,"-#") in if List.Count(a)=2 and Text.Length(a{0})=4 and Text.Length(a{1})=4 then Text.Combine(a,"-") else null)){0}?})
I didn't get this to work. After fixing typos teh first error was something like "there is 3 arguments for a function that excepts 2". I fixed it and then there was an other one "cannot convert a value of type Function to type List".
However, I got some ideas how to approach this from your answer. Thank you!