itsnotaboutthecell

Power Query M - Text Cleaner

Oct 17th, 2018
803
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 1.05 KB | None | 0 0
  1. /*
  2. Created By: Alex Powers
  3. Website: http://www.itsnotaboutthecell.com
  4. LinkedIn: https://www.linkedin.com/in/alexmpowers/
  5. Contact: alexmpowers@itsnotaboutthecell.com
  6. Reference: https://www.excelguru.ca/blog/2018/10/17/analyzing-text-with-power-query-and-excel/?fbclid=IwAR1t-emFdo7Wt0cnNMuM5W4njwhbxftcRku3GUzPzYQOyGeb0sgb-kbKMhU
  7.  
  8. */
  9.  
  10. (Sentence as text) as text =>
  11. let
  12. characterList = {97..122, 32, 33, 66, 42, 44, 46},
  13. Source = Table.FromList(Text.ToList(Sentence), Splitter.SplitByNothing(), type table [Letter = text], null, ExtraValues.Error),
  14. #"Valid Character" = Table.AddColumn(Source, "Valid Character", each List.Contains(characterList, Character.ToNumber(Text.Lower([Letter]))), type logical),
  15. #"Filtered Rows: Valid Character = true" = Table.SelectRows(#"Valid Character", each true),
  16. #"Combined Characters" = Table.Group(#"Filtered Rows: Valid Character = true", {"Valid Character"}, {{"Letter", each Text.Combine([Letter], ""), type text}})
  17. in
  18. #"Combined Characters"{[#"Valid Character"=true]}[Letter]
Add Comment
Please, Sign In to add comment