# Tags **Software** : [[02 - Tags/Software/Power Query]] **Functions** used : [[Table.TransformColumnTypes]] [[Table.AddColumn]] [[02 - Tags/Power Query Functions/List.Select]] [[Text.Split]] [[Text.Remove]] [[Text.Length]] [[Text.Start]] [[Text.End]] [[Text.Combine]] [[Excel.CurrentWorkbook]] **Date** : 18-March-2025 # Description - In this Video i show you how to extract a Fixed Pattern from a Dataset 🎾 # Video Link <iframe width="560" height="315" src="https://www.youtube.com/embed/d-D9Zj02yVE?si=3zEuy3G3KwPGLE3V" title="YouTube video player" frameborder="0" allow="accelerometer; autoplay; clipboard-write; encrypted-media; gyroscope; picture-in-picture; web-share" referrerpolicy="strict-origin-when-cross-origin" allowfullscreen></iframe> [GITHUB File Link](https://github.com/jbotes/powerbiTutorials/blob/main/PQ_Pattern_Extraction_Video.xlsx) # M-Query ```Query 1 let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content], #"Changed Type" = Table.TransformColumnTypes(Source,{{"Pattern Sentence", type text}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each List.Select(Text.Split(Text.Remove([Pattern Sentence], {",","."})," "), each Text.Length(_)=10)), #"Invoked Custom Function" = Table.AddColumn(#"Added Custom", "Custom.1", each PatternFunction([Pattern Sentence], 3, 7)) in #"Invoked Custom Function" ``` ```Query 2 - function (Sentence as text, lettercount as number, numbercount as number ) as text => let PatternLength = lettercount + numbercount, PatternCheck = (Word)=> ( Text.Remove(Text.Start(Word,lettercount), {"A".."Z"}) = "" and Text.Remove(Text.End(Word,numbercount), {"0".."9"}) = "" ), ShortListedWords = List.Select(Text.Split(Text.Remove(Sentence, {",","."})," "), each Text.Length(_)=PatternLength), PatternMatches = List.Select(ShortListedWords, each PatternCheck(_)) in Text.Combine(PatternMatches, ", ") ``` ## Optional Tags **Tags** : **Technique** : [[Custom Function]] [[Function in Function]]