# Tags **Software** : [[02 - Tags/Software/Power Query]] **Functions** used : [[Table.SplitColumn]] [[Table.TransformColumnTypes]] [[Table.Combine]] [[Text.Split]] [[Table.FromRows]] [[List.Transform]] [[List.Count]] [[Text.PositionOf]] [[Text.From]] [[Excel.CurrentWorkbook]] **Date** : 15-March-2025 # Description Splitting a column by delimiter by using the UI is easy but it comes with one drawback it hardcodes the column names so if your dataset includes new delimiters then it wont add the new columns. in this video i show you how to dynamically split a column by a delimiter no matter how many delimiters in the column. # Video Link <iframe width="560" height="315" src="https://www.youtube.com/embed/jhRW9qrtiYs?si=E9b7EdYGGRHOwK7A" 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_Address_Split_Video.xlsx) # M-Query ```mquery 1 UI Split let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content], #"Split Column by Delimiter" = Table.SplitColumn(Source, "Address", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Address.1", "Address.2", "Address.3"}), #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Address.1", type text}, {"Address.2", type text}, {"Address.3", Int64.Type}}) in #"Changed Type" ``` ```mquery 2 Dynamic Split let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content], SplitNoHeaders = Table.Combine(Table.AddColumn(Source, "Custom", each Table.FromRows({Text.Split([Address],",")}))[Custom]), DynamicSplit = Table.Combine(Table.AddColumn(Source, "Custom", each Table.FromRows({Text.Split([Address],",")}, List.Transform({1..List.Count(Text.PositionOf([Address],",", Occurrence.All))+1}, each "Address_" & Text.From(_))))[Custom]) in DynamicSplit ``` ## Optional Tags **Tags** : **Technique** :