Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Table.Split by a delimeter

So, I think my question is quite simple, but I've tried to search for an answer on the web and couldn't find anything.   Basically, I want to use the function Table.Split but with the seccond argum...
Need Help
Tips and Tricks
Tutorial Requests
  • ronrsnfld's avatar
    ronrsnfld
    3 years ago

    I suggest

    •  Add an Index Column (zero-based)
    • Use List.PositionOf to obtain the position of all the nulls, as well as the number of rows in the table
    • Use List.Generate to generate the individual tables by using Table.SelectRows relative to the Index column
    • Remove the Index column within the List.Generate function

     

     

    ...
    #"Added Index" = Table.AddIndexColumn(#"Previous Step", "Index", 0, 1, Int64.Type),

    n = List.PositionOf(#"Added Index"[Name],null,Occurrence.All) & {Table.RowCount(#"Added Index")},

    #"Split Table" = List.Generate(
        ()=>[t=Table.SelectRows(#"Added Index", (tb)=> tb[Index]<n{0}), idx=0],
        each [idx] < List.Count(n),
        each [t=Table.SelectRows(#"Added Index", (tb)=>tb[Index] > n{[idx]} and tb[Index] < n{[idx]+1}), idx=[idx]+1],
        each Table.RemoveColumns([t],"Index"))
in
    #"Split Table"

     

     

    If, as you imply in your question, you only want the FIRST split, then just use:

     

    Table.Split(#"Previous Step", List.PositionOf(#"Previous Step"[Name], null, Occurrence.First)){0}

     

ppm1's avatar
ppm1
Solution Sage
3 years ago

May I ask why you want to split your table up? Usually it is better to append tables to simplify analysis/visualization. This would complicate your data model and make your DAX measures more complex.

 

Pat

  • ronrsnfld's avatar
    ronrsnfld
    Super User
    3 years ago

    I suggest

    •  Add an Index Column (zero-based)
    • Use List.PositionOf to obtain the position of all the nulls, as well as the number of rows in the table
    • Use List.Generate to generate the individual tables by using Table.SelectRows relative to the Index column
    • Remove the Index column within the List.Generate function

     

     

    ...
    #"Added Index" = Table.AddIndexColumn(#"Previous Step", "Index", 0, 1, Int64.Type),

    n = List.PositionOf(#"Added Index"[Name],null,Occurrence.All) & {Table.RowCount(#"Added Index")},

    #"Split Table" = List.Generate(
        ()=>[t=Table.SelectRows(#"Added Index", (tb)=> tb[Index]<n{0}), idx=0],
        each [idx] < List.Count(n),
        each [t=Table.SelectRows(#"Added Index", (tb)=>tb[Index] > n{[idx]} and tb[Index] < n{[idx]+1}), idx=[idx]+1],
        each Table.RemoveColumns([t],"Index"))
in
    #"Split Table"

     

     

    If, as you imply in your question, you only want the FIRST split, then just use:

     

    Table.Split(#"Previous Step", List.PositionOf(#"Previous Step"[Name], null, Occurrence.First)){0}

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      Thank you!