How to Dynamically Split Data by Character Count in Power Query

Transforming raw data from the left to the one on the right where the data is split nicely into each column.

Let's look at the raw data, the employee shifts are all sticking together without space or any delimiter, but there is a consistent number count that can be split. However, there is another challenge where an employee may have varying number of shifts, sometimes 3 sometimes 4 or maybe 5.

So I'm going to show you on how to dynamically split the data even if the number of data entries grows in the future.

Here's how I solve this challenge:

First of all, load the raw data into Power Query Editor.

Insert Text Length

Click on the column that is going to be split.

Add Column tab > Extract > Length

The length of each raw data is added in a new column.

Insert Custom Column to find Number of columns to be split

Number of columns is determined by how many split needed and the length of the data. In the example below, my data is needed to be split by every 17 characters as each 17 characters represent one shift for each employee.

So what I do here is divide the length of the data by 17. For example, if the data only contain 17 characters, it has only one shift and only column is needed, so divide by 17 is 1; while if the data is having 51 characters, which means it has 3 shifts, 51/17=3, so 3 columns is needed to store the split data.

Divide the length of the data by the number of characters to be split to get the number of columns needed to store each split data.

New Division column is inserted.

Create a Dynamic List for the Split Column Header

Next is to create a list for the split column headers based on the Division column added in the previous step.

Go Home tab > Advanced Editor

Add below code to the Advanced Editor, where #"Added Division" is the previous step, [Division]  is the previously added column (Data Length / Number of Character to be split), each "Shift " is the headers name I want on my split columns.

Replace the previous step and the column name to suit your needs.

SplitColName = List.Transform({1..List.Max(#"Added Division"[Division])},each "Shift "&Text.From(_))

Then a list for the headers name is created.

Split by Number of Characters

Lastly, the data is now ready to be split dynamically.

Go to Advanced Editor to add the below code. #"Changed Type" is the earlier step where I want to used to split the data, "Shift" is the data that I want to perform splitting, 17 is the number of characters to be split repeatedly, SplitColName is the list I created previously contain the headers names.

#"Split Column by shift" = Table.SplitColumn(#"Changed Type", "Shift", Splitter.SplitTextByRepeatedLengths(17), SplitColName)

Click on Done and Close & Load.

Then Voila, the data is split nicely into 3 columns named Shift 1, Shift 2 and Shift 3.

This query works dynamically whereby in the future when the number of shifts increases or decreases, each time I refresh, the columns will be split accordingly.

Hope you find this article helpful.

Like and follow me for more tutorials regarding Excel, Power Query, Power BI and so on.

Wishing you a fantastic day ahead and have fun analyzing!