Table.Split by a delimeter

[Anonymous]

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 argument defined by the first time a value appears on my first column.

For example, i have the following table:

(sorry for the print but i wasn't able to add a table because of a invalid HTML problem)

And goes like this until December. I know that I can use the number 34 on the seccond argument and it will works. But in case someone add a new project, the number changes. So i want to make the split by the first null value that appears on the first column or by the Total row.

Can anyone help me, please?!

[Anonymous]

You can duplicate your source table and add an index column starting with (1). Then use Table.SelectRows to select only rows with null in the first column. At the end of that formula, add [Index]{0} to get the Index number of that row, which you can use for the parameter in Table.SplitAt(PriorStepOrTableName, NameOfTheQueryWeJustMade)

--Nate

[ppm1]

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 · Accepted solution]

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]

Thank you!