Forum Discussion

Linnil's avatar
Linnil
Helper III
2 years ago
Solved

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

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 ...
Need Help
  • wdx223_Daniel's avatar
    wdx223_Daniel
    2 years ago

    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's avatar
Linnil
Helper III
2 years ago

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's avatar
wdx223_Daniel
Community Champion
2 years ago

Could you show your code?

  • Linnil's avatar
    Linnil
    Helper III
    2 years ago

    = 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's avatar
      wdx223_Daniel
      Community Champion
      2 years ago

      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's avatar
        Linnil
        Helper III
        2 years ago

        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