Be careful when splitting cells in Power Query

%3CLINGO-SUB%20id%3D%22lingo-sub-11307%22%20slang%3D%22en-US%22%3EBe%20careful%20when%20splitting%20cells%20in%20Power%20Query%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-11307%22%20slang%3D%22en-US%22%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%3E%3CIMG%20%2F%3E%3C%2FSPAN%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20748px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F3369iC9ADBE198929BC48%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22Header.PNG%22%20title%3D%22Header.PNG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20posted%20this%20in%20the%20BI%20and%20Analysis%20section%20before%20realising%20this%20dedicated%20Get%20%26amp%3B%20Transform%20section%20was%20available...%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPower%20Query%20is%20fantastic%20at%20all%20sorts%20of%20wonderful%20data%20transformations%20and%20can%20save%20you%20huge%20amounts%20of%20time.%3C%2FP%3E%3CP%3EOne%20common%20task%20is%20the%20need%20to%20split%20data%20apart%20from%20a%20single%20column.%3C%2FP%3E%3CP%3EThis%20is%20extremely%20straightforward%20with%20Power%20Query%20but%20comes%20with%20a%20warning...%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFor%20example%3A%3C%2FP%3E%3CP%3ELet's%20say%20I%20want%20to%20turn%20the%20blue%20column%20of%20data%20into%20the%20green%20table%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CIMG%20src%3D%22https%3A%2F%2Fmedia.licdn.com%2Fmpr%2Fmpr%2Fshrinknp_800_800%2FAAEAAQAAAAAAAAg8AAAAJGU5NWE2NWQ1LTUxODktNDhjMi05M2ExLWYzMGVjZjQ2YTU0Ng.png%22%20border%3D%220%22%20width%3D%22442%22%20height%3D%22206%22%20%2F%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESimply%20go%20to%20Power%20Query%20%26gt%3B%20From%20Table%20%26gt%3B%20highlight%20the%20column%20and%20select%20Split%20Column%3C%2FP%3E%3CP%3E%3CIMG%20src%3D%22https%3A%2F%2Fmedia.licdn.com%2Fmpr%2Fmpr%2Fshrinknp_800_800%2FAAEAAQAAAAAAAAj_AAAAJDdlNTNmZWM2LTQwMDQtNDIyMC1iODhiLTMyNzhiMjI2OTE1Zg.png%22%20border%3D%220%22%20width%3D%22640%22%20height%3D%22341%22%20%2F%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20are%20then%20prompted%20for%20a%20delimiter%2C%20and%20in%20this%20case%20you%20select%20a%20comma.%3C%2FP%3E%3CP%3E%3CIMG%20src%3D%22https%3A%2F%2Fmedia.licdn.com%2Fmpr%2Fmpr%2Fshrinknp_800_800%2FAAEAAQAAAAAAAAg8AAAAJGQzZTM2YmZlLWI4YWUtNGM0Ni1iM2IxLTVlZDA3ZjM5OTJkYg.png%22%20border%3D%220%22%20width%3D%22640%22%20height%3D%22367%22%20%2F%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EClick%20OK%20and%20you're%20done%3C%2FP%3E%3CP%3E%3CIMG%20src%3D%22https%3A%2F%2Fmedia.licdn.com%2Fmpr%2Fmpr%2Fshrinknp_800_800%2FAAEAAQAAAAAAAAldAAAAJDNmZGFhNTU4LTgwOTAtNDY4Yy1iNTcxLTFlYzU2YWU0YjMyNg.png%22%20border%3D%220%22%20width%3D%22543%22%20height%3D%22302%22%20%2F%3E%3C%2FP%3E%3CP%3EClick%20Close%20%26amp%3B%20Load%20to%20a%20table%20and%20all%20is%20well%20with%20the%20world%20%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Fi%2Fsmilies%2F16x16_smiley-happy.png%22%20border%3D%220%22%20alt%3D%22Smiley%20Happy%22%20title%3D%22Smiley%20Happy%22%20%2F%3E%3C%2FP%3E%3CP%3ESimple%3C%2FP%3E%3CP%3EOr%20is%20it.....%3F%3C%2FP%3E%3CP%3EGenerally%20we%20use%20Power%20Query%20to%20repeat%20these%20mundane%20tasks%2C%20and%20when%20you%20click%20refresh%20you%20want%20the%20Query%20to%20run%20and%20transform%20your%20data.%3C%2FP%3E%3CP%3EHowever%2C%20if%20you%20change%20your%20source%20table%20so%20that%20there%20are%205%20items%20separated%20by%20commas%20it%20doesn't%20work!%3C%2FP%3E%3CP%3E%3CIMG%20src%3D%22https%3A%2F%2Fmedia.licdn.com%2Fmpr%2Fmpr%2Fshrinknp_800_800%2FAAEAAQAAAAAAAAeFAAAAJDBkYjRmNGVlLTM5MjYtNDIwNy05NzcxLWExMDJhMzNhNDYxOQ.png%22%20border%3D%220%22%20width%3D%22567%22%20height%3D%22231%22%20%2F%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20root%20of%20the%20problem%20is%20in%20that%20Split%20Column%20screen.%20If%20you%20click%20on%20the%20Advanced%20Options%20section%20then%20you%20can%20see%20that%20the%20number%204%20is%20there.%20%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20is%20the%20number%20of%20delimiters%20identified%20the%20first%20time%20you%20ran%20this%20query.%20But%20this%20might%20not%20be%20the%20case%20next%20time%20you%20refresh!!%3C%2FP%3E%3CP%3E%3CIMG%20src%3D%22https%3A%2F%2Fmedia.licdn.com%2Fmpr%2Fmpr%2Fshrinknp_800_800%2FAAEAAQAAAAAAAAlBAAAAJDczZTRkYjQ3LWM1MTQtNGQ0Yy1hYWJmLWUwNTY0ZGUwNDQ2ZQ.png%22%20border%3D%220%22%20width%3D%22640%22%20height%3D%22439%22%20%2F%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESo%20what%20do%20you%20do%3F%3C%2FP%3E%3CP%3EI%20approach%20it%20in%20one%20of%202%20ways%2C%20and%20I%20have%20a%20very%20very%20long%20way%20to%20go%20in%20fully%20understanding%20all%20the%20different%20approaches%20that%20could%20be%20taken.%20%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'd%20love%20to%20hear%20from%20anyone%20who%20has%20a%20more%20elegant%20solution%2C%20as%20I%20actually%20hope%20there%20is%20a%20better%20way%20than%20this...%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EOption%201.%20The%20simple%20brute%20force%20approach%3A%20%26nbsp%3B%20Change%20the%20number%204%20to%20a%20number%20bigger%20than%20you're%20ever%20going%20to%20need.%20%26nbsp%3BCrude%20but%20simple%20and%20effective.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EOption%202.%20%26nbsp%3BThis%20does%20involve%20writing%20(or%20copying%20and%20pasting)%20some%20code%20in%20the%20advanced%20editor%20window%3C%2FP%3E%3CP%3EHere%20it%20is...%3C%2FP%3E%3CP%3E________________________________________________________%3C%2FP%3E%3CP%3E%2F%2FTable1%20is%20the%20source%20table%20name%3C%2FP%3E%3CP%3E%2F%2FData%20is%20the%20column%20name%3C%2FP%3E%3CP%3Elet%3C%2FP%3E%3CP%3ESource%20%3D%20Excel.CurrentWorkbook()%7B%5BName%3D%22Table1%22%5D%7D%5BContent%5D%2C%3CBR%20%2F%3E%3CBR%20%2F%3EDelimiterCount%20%3D%20Table.AddColumn(Source%2C%20%22CountDelimiters%22%2C%20each%20List.Count(Text.Split(%5BData%5D%2C%22%2C%22))-1)%2C%3C%2FP%3E%3CP%3EMaxCount%20%3D%20List.Max(DelimiterCount%5BCountDelimiters%5D)%2B1%2C%3CBR%20%2F%3E%3CBR%20%2F%3ESplitResult%20%3D%20Table.SplitColumn(Source%2C%20%22Data%22%20%2C%20Splitter.SplitTextByDelimiter(%22%2C%22)%2C%20MaxCount%20)%3C%2FP%3E%3CP%3Ein%3CBR%20%2F%3ESplitResult%3C%2FP%3E%3CP%3E______________________________________________________%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENow%20when%20you%20refresh%20the%20query%20the%20output%20table%20will%20get%20wider%20each%20time%20you%20add%20a%205th%2C%206th%20or%207th%20element%20to%20your%20input%20table%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHopefully%20the%20Power%20Query%20team%20can%20add%20functionality%20for%20this%20to%20happen%20automatically.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CA%20title%3D%22Excel%20Articles%22%20href%3D%22https%3A%2F%2Fwww.linkedin.com%2Ftoday%2Fauthor%2Fwynhopkins%22%20target%3D%22_self%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3EMy%20other%20Excel%20articles%20on%20Linkedin%3C%2FA%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-11307%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EBI%20%26amp%3B%20Data%20Analytics%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ETutorial%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1858941%22%20slang%3D%22en-US%22%3ERe%3A%20Be%20careful%20when%20splitting%20cells%20in%20Power%20Query%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1858941%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F9832%22%20target%3D%22_blank%22%3E%40Wyn%20Hopkins%3C%2FA%3E%26nbsp%3BI%20am%20surprised%20this%20is%20still%20not%20implemented%20yet%20and%20that%20this%20is%20the%20default%20behavior.%20Didn't%20know%20this%20was%20normal%20behavior%20until%20I%20found%20out%20about%20it%20while%20trying%20to%20split%20a%20column%20that%20had%20a%20lot%20more%20cases%20than%204.%20Thanks%20for%20sharing%20this%20solution.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
MVP

Header.PNG

 

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 Smiley Happy

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.

 

 

 

My other Excel articles on Linkedin

1 Reply

@Wyn Hopkins I am surprised this is still not implemented yet and that this is the default behavior. Didn't know this was normal behavior until I found out about it while trying to split a column that had a lot more cases than 4. Thanks for sharing this solution.