Forum Discussion

ValeriaBreve's avatar
ValeriaBreve
Icon for Post Partisan rankPost Partisan
2 years ago
Solved

CountRows in a nested table

Hello, I need to count the rows of each nested table (in a column "All") of a query. I am trying to do so by adding a custom column into the nested tables and, for each cell, adding the total row c...
Need Help
  • dufoq3's avatar
    dufoq3
    2 years ago

    Hi ValeriaBreve, check this:

    let
    Source = Table.FromList({#table(null, {{"a"}}), #table(null, {{"a"}, {"b"}})}, Splitter.SplitByNothing(), type table[All=table]),
    Ad_RowCountOuterColumn = Table.AddColumn(Source, "RowCount as Outer Column", each Table.RowCount([All]), Int64.Type),
    Ad_RowCountToAllTablesInner = Table.TransformColumns(Ad_RowCountOuterColumn, {{"All", each Table.AddColumn(_, "RowCount", (x)=> Table.RowCount(_), Int64.Type), type table}})
in
    Ad_RowCountToAllTablesInner

     

PhilipTreacy's avatar
PhilipTreacy
Icon for Super User rankSuper User
2 years ago

Hi ValeriaBreve 

 

Does your data look like this?

 

 

If so, create another column and you can count the rows in a table with this

 

= Table.RowCount([Custom])

 

Do you need the row count entered into the table itself?

 

Regards

 

Phil

ValeriaBreve's avatar
ValeriaBreve
Icon for Post Partisan rankPost Partisan
2 years ago

Hi Phil, yes that is how my query looks like! I need to enter the count in the table itself - as if there is more than 1 row in each nested table, I will need to perform other calculations - for each nested table.

That's what I was trying to do with the above formula - only I am not sure how to reference the nested tables in the countrows.... thanks!