Split by Variable Columns in Power Query

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