Forum Discussion
Create a Column which labels table rows as Section 1, Section 2, 3, 4 according to number of rows
please use my code as a new step, instead of puting it in the add column function.
click the "fx" label beside the formula edit bar, then copy code into formula area
=let
AAA=Table.RowCount(#"Added Index")
in Table.FromColumns(Table.ToColumns(#"Added Index")&{List.Transform({0..AAA-1},each "Section "&Text.From(Number.IntegerDivide(_,Number.RoundUp(AAA/4))+1))},Table.ColumnNames(#"Added Index")&{"Section"}))
Could you show your code?
= Table.AddColumn(#"Added Index", "Sections", each let
AAA=Table.RowCount(#"Added Index")
in Table.FromColumns(Table.ToRows(#"Added Index")&{List.Transform({0..AAA-1},each "Section "&Text.From(Number.IntegerDivide(_,Number.RoundUp(AAA/4))+1))},Table.ColumnNames(#"Added Index")&{"Section"}))
My previous step was Added Index.
Thanks
please use my code as a new step, instead of puting it in the add column function.
click the "fx" label beside the formula edit bar, then copy code into formula area
=let
AAA=Table.RowCount(#"Added Index")
in Table.FromColumns(Table.ToColumns(#"Added Index")&{List.Transform({0..AAA-1},each "Section "&Text.From(Number.IntegerDivide(_,Number.RoundUp(AAA/4))+1))},Table.ColumnNames(#"Added Index")&{"Section"}))Hi Daniel - I think that's working - there was an extra ) at the end which I removed.
Can I just check - what happens when I have an odd number of rows? Will one section have 1 additional row (that would be OK, I just need all rows to be included).
If it helps as a solution, it would be OK to create a single blank row before the Section step, when the row count is odd. When the row count is even, the Section step works perfect.
But I do have odd number row count too.
Thanks againwhen you get 100 rows, that can be divisible by 4, then you got 4 sections, each section has 25 rows.
when you get 101 rows, it is preferred to ensure the first section has the mose rows, then you got 4 sections too, but the first three sections with 26 rows and the last section with only 23 rows.
that's the logic of my code.
This solution is perfect. It has an extra close bracket at the end of the code which I removed.
It worked perfectly for both odd and even countrows. Thanks Daniel!