Tricks in Table.SplitColumn Function - Power Query

A Technique in Table.SplitColumn Function in Power Query for Dynamically Creating Multiple Columns:

The fourth argument of the Table.SplitColumn function can take either a list of column names or a count of columns. This feature allows us to split a single column into several columns dynamically.

Thus, if the number of words in the source data changes—whether it increases or decreases the function will automatically adjust the number of resulting columns.

let

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

 ColCount = List.Max(List.Transform(Source[Sentence], (f) => List.Count(Text.Split(f, " ")))),

 Result = Table.TransformColumnNames(Table.SplitColumn(Source,"Sentence", Splitter.SplitTextByDelimiter(" "),ColCount), each Text.Replace(_,"Sentence.","Word"))

in

 Result

Utilize extraColumns Argument in Table.SplitColumn Function - Power Query:

The Table.SplitColumn function allows for various methods of splitting a column. When the exact number of resulting columns after the split is uncertain, we can set the column count to 1. Additionally, by using ExtraValues.List in the extraColumns argument, all items can be consolidated into a single list.

let

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

 SplitColumnbyDelimiter = Table.SplitColumn(Source, "Sentence", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), 1,null, ExtraValues.List ),

 Result = Table.FromRows( Table.ToColumns(Table.FromColumns( SplitColumnbyDelimiter[Sentence.1])))

in

 Result