I posted this in the BI and Analysis section before realising this dedicated Get & Transform section was available...
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. 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.
