When your mashup data through Power Query, sometimes it is one time activity. But sometimes, the refresh has to be done every time when data gets updated. Split a Column Dynamically is a very important tool for PQ practitioners. Consider a data like below and split the column with comma separator in Power Query. See the result
Now, change the data by increasing additional alphabets and then refresh the query (I added x, y in first row). You will notice that output will remain the same.
If you see the existing Query
= Table.SplitColumn(Source, "String", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"String.1", "String.2", "String.3", "String.4"})
This has got String.1 to String.4 hard-coded (These 4 columns got generated on the basis of first time split).
To remove this limitation, we need to make the query dynamic. To make it dynamic, we need to generate this part on the basis of maximum number of words
{"String.1", "String.2", "String.3", "String.4"} === Hence, if I send 6 words, then this part should become {"String.1", "String.2", "String.3", "String.4", "String.5", "String.6"}
Let's do it now.
Step 1 – Add Column – Custom column and put following to generate the number of words in every row (note – String is name of column which I am splitting. In your case, if your column name is different, then replace it with your column name). I also changed the New column name to Temp (this is my preference)
= List.Count(Text.Split([String],","))
Step 2 – Select Temp column – Transform – Statistics – Maximum
This generates below code automatically
Step 3 – Now, we need to generate a list like {"String.1", "String.2", "String.3", "String.4"}. Insert a new function step again and put following formula.
= List.Transform({1..#"Calculated Maximum"},each "String." & Number.ToText(_))
The overall idea of above is to Add 1, 2, 3, 4 to my column name followed by "."
#"Calculated Maximum" yielded a value of 4 in previous step. {1..#"Calculated Maximum"} will generate a list {1..4} i.e. {1,2,3,4}. List.Transform is appending {1,2,3,4} to "String."
This will generate below
Hence, we have following 3 steps in our query
= Table.AddColumn(Source, "Temp", each List.Count(Text.Split([String],",")))
= List.Max(#"Added Custom"[Temp])
= List.Transform({1..#"Calculated Maximum"},each "String." & Number.ToText(_))
To make it dynamically, we need to combine these into 1.
So, we will use last Query as the base and will merge till the first one.
Hence, #"Calculated Maximum" will need to be replaced with List.Max(#"Added Custom"[Temp])
Hence, new query becomes
= List.Transform({1..List.Max(#"Added Custom"[Temp])},each "String." & Number.ToText(_))
In this Query, #"Added Custom" will need to be replaced with first i.e. by Table.AddColumn(Source, "Temp", each List.Count(Text.Split([String],",")))
Hence, our new Query becomes
= List.Transform({1..List.Max(Table.AddColumn(Source, "Temp", each List.Count(Text.Split([String],",")))[Temp])},each "String." & Number.ToText(_))
Now, when your split String column, the following code gets generated
= Table.SplitColumn(Source, "String", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"String.1", "String.2", "String.3", "String.4"})
In place of {"String.1", "String.2", "String.3", "String.4"}, we need to feed the query where we put all 3 steps into one. Hence, final output becomes
= Table.SplitColumn(Source, "String", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), List.Transform({1..List.Max(Table.AddColumn(Source, "Temp", each List.Count(Text.Split([String],",")))[Temp])},each "String." & Number.ToText(_)))
This is the query which you need to use to make your column split dynamic. You just need to replace word String with your column name (if delimiter is not comma say space, then that also has to be replaced appropriately). Hence, this replacement has to be done at maximum 6 places as given below
= Table.SplitColumn(Source, "String", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), List.Transform({1..List.Max(Table.AddColumn(Source, "Temp", each List.Count(Text.Split([String],",")))[Temp])},each "String." & Number.ToText(_)))
NOTE – You may need to replace Source also depending upon your last step.
Now, you increase (or decrease) number of words, your query will always yield the right number of columns.
Excel file related to Split a Column Dynamically can be downloaded from PQ_TT5 – Dynamic Split
Power Query M function reference – PowerQuery M | Microsoft Docs