Forum Discussion

vazmor's avatar
vazmor
Helper II
5 years ago
Solved

Split into Column based on one row

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

 



Need Help
  • ImkeF's avatar
    ImkeF
    5 years ago

    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.

     

     

4 Replies

  • ImkeF's avatar
    ImkeF
    Community Champion
    5 years ago

    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's avatar
      vazmor
      Helper II
      5 years ago

      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's avatar
        ImkeF
        Community Champion
        5 years ago

        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.