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

 

Consider this tiny data!

Split by Variable Columns in Power Query

  • 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

Split by Variable Columns in Power Query - Fixed Split By

  • 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

Split by Variable Columns in Power Query - Count of Commas

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 in Power Query - Dynamic List

 

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

Split by Variable Columns in Power Query - Final Result

 

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 😀

 

DOWNLOAD EXCEL QUERY FILE

 

Some more automations in Power Query

 



Topics that I write about...






Download Smart Ebooks on
Excel and Power BI