Create a Column which labels table rows as Section 1, Section 2, 3, 4 according to number of rows

[Linnil]

Hi - I have a table with a variable amount of rows. Whenever the data is refreshed (once a week) it changes.

I would like to count all the rows and then divide those rows into 4 sections, more of less.

For instance, if I have 100 rows:

Row 1-25 = Section 1

Row 26-50 - Section 2

etc

if I have 200 rows:

Row 1-50 = Section 1

etc

Is it possible to do in PQ?

Many thanks Linnil

[BA_Pete]

Hi Linnil,

You can try using the following in a custom step:

Table.Split(PreviousStepName, Number.RoundUp(Table.RowCount(PreviousStepName) / 4))

This will split your table into four tables - not perfectly evenly, but it's fast and simple.

Pete

[Linnil]

Thanks Pete - I ran a few scenarios trying this out before coming to the Community. Not quite what I need but thanks for your suggestion. Very kind of you to respond.

[wdx223_Daniel]

NewStep=let a=Table.RowCount(PreviousStepName) in Table.FromColumns(Table.ToRows(PreviousStepName)&{List.Transform({0..a-1},each "Section "&Text.From(Number.IntegerDivide(_,Number.RoundUp(a/4))+1))},Table.ColumnNames(PreviousStepName)&{"Section"})

[Linnil]

Hi Daniel - thanks so much. This is on the right track. I've changed variable a to AAA so I could understand the script better.

When I created the custom column with this Let statement, I got this error:

Expression.Error: The count of 'columns' (1445) doesn't match that of 'columnNames' (11).
Details:
[List]

So the number of rows is correct (1445).
And my table (with the new column we just created for Sections) has 11 columns.
Can you help me solve this mismatch?
Thanks 🙂

[wdx223_Daniel]

Could you show your code?

[Linnil]

= 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

[wdx223_Daniel · Accepted solution]

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"}))

[Linnil]

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!

[Linnil]

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 again

[wdx223_Daniel]

when 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.

[Linnil]

Thanks Daniel - that makes sense.

However before the Section Step, there are 1445 rows, and when Section Step is applied, there are 1444 steps. So one row is dropped. I thought that was due to a rounding in the code.
BTW Section 1 = 361

Section 2 = 361

Section 3 = 361

Section 4 = 361

All sections have same qty of rows, there does not seem to be any "offset".

Let me know what you think - thanks

[wdx223_Daniel]

i used Number.RoundUp, so each section should be 362, instead of 361

then first three sections will have 362 rows, and the last has 359 rows

[Linnil]

I see - thanks Daniel.

In my actual table that does not happen. One row has disappeared and all sections have 361.
So the code is not working correctly in real-life scenario. Is there anything else I can try?

Thanks

[wdx223_Daniel]

the code will not change the row number of your data, it just add a new column

Table.ToColumns(YourData)&{NewColumn}, this will not delete any rows.

try to check, is there some steps filter out something.

[Linnil]

Hi Daniel

OK - yes - it is all working perfectly! Thanks for your patience and thanks for such a great solution.
All the best

Linnil