Forum Discussion
Split into Column based on one row
Hi vazmor ,
you could count them or use this function:(myTable as table, RowPosition as number, optional ColumnName as text) => let ColName = if ColumnName = null then "Column1" else ColumnName, Source = myTable, #"Kept Range of Rows" = Table.Range(Source, RowPosition-1,1), Custom1 = Record.ToList( #"Kept Range of Rows"{0} ){0}, Custom2 = Text.PositionOf( Custom1, " ", Occurrence.All), SplitPositions = {0} & Custom2, #"Split Column by Positions" = Table.SplitColumn(Source, ColName, Splitter.SplitTextByPositions(SplitPositions)) in #"Split Column by Positions"It assumes that your column to split is called "Column1".
If not, pass the column name into the 3rd (optional) parameter.
Hi vazmor ,
in the Split Column-dialogue, there is an option to split by positions.
It accepts a comma separated list of positions (where your blanks are) to split the table.
Just make sure to start with a 0 as a starting point.
Hello, Thank you, if I can use this option but how can I use the character function based on only one row or that the position function is dynamic based on that row of the -------.
Hi vazmor ,
you could count them or use this function:(myTable as table, RowPosition as number, optional ColumnName as text) => let ColName = if ColumnName = null then "Column1" else ColumnName, Source = myTable, #"Kept Range of Rows" = Table.Range(Source, RowPosition-1,1), Custom1 = Record.ToList( #"Kept Range of Rows"{0} ){0}, Custom2 = Text.PositionOf( Custom1, " ", Occurrence.All), SplitPositions = {0} & Custom2, #"Split Column by Positions" = Table.SplitColumn(Source, ColName, Splitter.SplitTextByPositions(SplitPositions)) in #"Split Column by Positions"It assumes that your column to split is called "Column1".
If not, pass the column name into the 3rd (optional) parameter.Thanks,