Split into Column based on one row

[vazmor]

Hello,
I have the following problem,
Based on the row where the ----- appear, you can determine a way to separate with that example into columns and assume the division of asterisks to be another sheet. Marked wih yellow
Thanks

[ImkeF]

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.

[vazmor]

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 -------.

[ImkeF · Accepted solution]

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.

[vazmor]

Thanks,