If you have used the Split by feature of Power Query to delimit columns, it has a slight limitation. This feature only delimits to a fixed number of columns by hard coding the number of columns to split into. Later if your values change the split columns remain the same and do not change.. what a bummer! Let’s solve this   Split by Variable Columns – Video   Consider this tiny data! Me and my son’s hobbies And yes I’d like to sleep.. a lot If I Split the Hobbies column by a , (comma) Power Query generates the following code The Column names Hobbies.1 to Hobbies.3 are hard coded. So if I add a 4th hobby for Rehet, I’ll still get 3 columns and not 4. Unless I edit the query manually. Let’s fix this   Creating a Variable Column List You should understand the big idea to solve this problem – I want to create a dynamic list that expands or contracts based on maximum number hobbies in the Hobbies Column. Consider the following highlighted M Code let   Source = Excel.CurrentWorkbook(){[Name = “Data”]}[Content],   DynamicColumnList = Table.AddColumn(     Source,      “Custom”,      each List.Count(Text.PositionOfAny(       [Hobbies],        {“,”},        Occurrence.All     ))   ) in   DynamicColumnList By using the above code, I get how many commas are there in each row of the hobby column. The results are like this. Obviously if you have 2 commas you have 3 hobbies, so I’ll have to do a + 1 later To finally get the total number of columns, I slightly revise my code. let   Source = Excel.CurrentWorkbook(){[Name = “Data”]}[Content],   DynamicColumnList     = List.Max(     Table.AddColumn(Source, “Custom”, each List.Count(       Text.PositionOfAny([Hobbies], {“,”}, Occurrence.All)     ))[Custom]   ) + 1 in  DynamicColumnList   Finally convert this into a List of Columns Consider the additional highlighted part let   Source = Excel.CurrentWorkbook(){[Name = “Data”]}[Content],   DynamicColumnList = List.Transform({     1..List.Max(       Table.AddColumn(Source, “Custom”, each List.Count(         Text.PositionOfAny([Hobbies], {“,”}, Occurrence.All)       ))[Custom]     ) + 1   }, each “Hobbies.” & Text.From(_)) in  DynamicColumnList Here are the results.. a dynamic list of columns. Sweet!   Split by Variable Columns I finally add the Split by step and replace the hard coded column names with my hard work (DynamicColumnList) 🙂 let   Source = Excel.CurrentWorkbook(){[Name = “Data”]}[Content],   DynamicColumnList = List.Transform({     1..List.Max(       Table.AddColumn(Source, “Custom”, each List.Count(         Text.PositionOfAny([Hobbies], {“,”}, Occurrence.All)       ))[Custom]     ) + 1   }, each “Hobbies.” & Text.From(_)),   #”Split Column by Delimiter” = Table.SplitColumn(     Source,      “Hobbies”,      Splitter.SplitTextByDelimiter(“,”, QuoteStyle.Csv),      DynamicColumnList   ) in   #”Split Column by Delimiter” And this will split to more columns if I add more hobbies for my Son! See this   You can argue, that was a bit of work but that’s the price of automation! As a reward, I’ll never edit this query again 😀     Some more automations in Power Query A slightly different approach to the above problem by Oz Calculate Age in Years and Months Calculate Fiscal Year and Qtr in Power Query Make Remove Other Columns Dynamic Dynamic Column Names in Power Query   .        Automate repetitive data cleaning tasks using Power Query A comprehensive course to learn Power Query to automate all your mundane and repetitive data cleaning tasks in Excel or in Power BI   DOWNLOAD THE COURSE OUTLINE    |    ENROLL IN THE COURSE