Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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?!
Solved! Go to Solution.
I suggest
...
#"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}
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
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
I suggest
...
#"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}
Thank you!