Split Column By Position

[dpotta]

Hello

Any help here appreciated.

In M, I am trying to reference a numeric value in a column as the number of characters to split a column by, but I am not sure how to do this.

The value I want to split by is in Column 5 and the Column I want to split is Column1.

I came up with this statement, but it does not work!

= Table.SplitColumn(#"Changed Type","Column1",Splitter.SplitTextByPositions({0, each [Column5]}, false),{"Column1.1", "Column1.2"})

What do I need to do to make this work?

Thanks in advance

Dave

 

[MarcelBeug]

Your example suggest that you want to split the text in 2 elements: the first part through the position in Column5 and the second part the remaining part.
In that case:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column5", Int64.Type}}),
    #"Split Column by Position" = Table.AddColumn(#"Changed Type","Split",each Record.FromList(Splitter.SplitTextByPositions({0,[Column5]})([Column1]),{"Column1.1","Column1.2"})),
    #"Expanded Split" = Table.ExpandRecordColumn(#"Split Column by Position", "Split", {"Column1.1", "Column1.2"}, {"Column1.1", "Column1.2"})
in
    #"Expanded Split"

If you want to split in parts, each with the number of characters in Column5, it is more complicated, but the following code performs the trick:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column5", Int64.Type}}),
    #"Split Column by Lengths" = Table.AddColumn(#"Changed Type","Split",each Splitter.SplitTextByRepeatedLengths([Column5])([Column1])),
    #"Added Custom1" = Table.AddColumn(#"Split Column by Lengths", "ListCount", each List.Count([Split])),
    #"Added Custom" = Table.AddColumn(#"Added Custom1", "Record", each Record.FromList([Split], List.Transform({1..
[ListCount]},each "Column1."&Text.From(_)))),
    #"Expanded Record" = Table.ExpandRecordColumn(#"Added Custom", "Record", List.Transform({1..List.Max(#"Added Custom"
[ListCount])},each "Column1."&Text.From(_))),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Record",{"Split", "ListCount"})
in
    #"Removed Columns"

 

[dpotta]

Thank you - the first solution is fine : )