Take care when splitting cells - Updated with a better solution...

Power Query is fantastic at all sorts of wonderful data transformations and can save you huge amounts of time.

One common task is the need to split data apart from a single column.

This is extremely straightforward with Power Query but comes with a warning...

For example:

Let's say I want to turn the blue column of data into the green table

Simply go to Power Query > From Table > highlight the column and select Split Column

You are then prompted for a delimiter, and in this case you select a comma.

Click OK and you're done

Click Close & Load to a table and all is well with the world :)

Simple

Or is it.....?

Generally we use Power Query to repeat these mundane tasks, and when you click refresh you want the Query to run and transform your data.

However, if you change your source table so that there are 5 items separated by commas it doesn't work!

The root of the problem is in that Split Column screen. If you click on the Advanced Options section then you can see that the number 4 is there.  This is the number of delimiters identified the first time you ran this query. But this might not be the case next time you refresh!!

So what do you do?

I approach it in one of 2 ways, and I have a very very long way to go in fully understanding all the different approaches that could be taken.   I'd love to hear from anyone who has a more elegant solution, as I actually hope there is a better way than this...

Option 1. The simple brute force approach:   Change the number 4 to a number bigger than you're ever going to need.  Crude but simple and effective.

Option 2. Do not bother with Option 2,  This was my original solution (I've left it in to show that we all learn the hard way), but there is a simpler way! There always is with Excel!!!

Option 3.  This does involve writing (or copying and pasting) some code in the advanced editor window

Here it is...

________________________________________________________

//Table1 is the source table name

//Data is the column name

let

Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],

DelimiterCount = Table.AddColumn(Source, "CountDelimiters", each List.Count(Text.Split([Data],","))-1),

MaxCount = List.Max(DelimiterCount[CountDelimiters])+1,

SplitResult = Table.SplitColumn(Source, "Data" , Splitter.SplitTextByDelimiter(","), MaxCount )

in
SplitResult

______________________________________________________

 

Now when you refresh the query the output table will get wider each time you add a 5th, 6th or 7th element to your input table

 

Hopefully the Power Query team can add functionality for this to happen automatically.

 

AMAZING EXCEL SOLUTIONS

 

www.accessanalytic.com.au

 

My LinkedIn Articles

 

*************************************************************************************

IGNORE THIS OPTION - Option 3 is way easier...

Option 2. Use a custom function. I've created the function below but there must be a better way, please let me know if this can be done more easily  (these custom functions are not for the faint-hearted, stick with option 1 to start with and migrate to option 2 as you get more comfortable with Power Query) ....

So, I create a blank Query and name it fnSplitter then paste this code in

Here's the code if you want to try it:

*************************************************************************************

( TableName, colHeading, DELIMITER) =>

let

//To get count of columns to split by
//get length with delimiter, then length without then calculate difference and take the max

HelperColumn = Table.DuplicateColumn(TableName , colHeading , "Helper"),

LengthWithDelimiter = Table.AddColumn(HelperColumn, "LengthWith", each Text.Length([Helper]), type number),

RemoveDelimiters = Table.ReplaceValue(LengthWithDelimiter, DELIMITER ,"",Replacer.ReplaceText,{"Helper"}),

LengthWithoutDelimiter = Table.AddColumn(RemoveDelimiters , "LengthWithout", each Text.Length([Helper]), type number),

CountDelimiters = Table.AddColumn(LengthWithoutDelimiter, "Count", each [LengthWith] - [LengthWithout], type number),

MaxCount = List.Max(CountDelimiters[Count])+1,

 

SplitResult = Table.SplitColumn(TableName, colHeading , Splitter.SplitTextByDelimiter(","), MaxCount )



in
SplitResult

******************************************************************************************

 

I then "call" this function from my original query.  I have to pass 3 variables to my query (a familiar concept to anyone who's written a bit of VBA in the past)

(note: "Data" was the name of my column and "," is the delimiter I'm using)

Here's that code if you want to paste it
******************************************************************

let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],

Split= fnSplitter(Source, "Data", ",")

in
Split

********************************************************************

 

******************************************************************************************