CountRows in a nested table

[ValeriaBreve]

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 count:

= Table.TransformColumns(
#"Previous Step",
{
{"All", each Table.AddColumn(_,"RowCount",each Table.RowCount(_))}}
)

But I am getting an error: Expression.Error: We cannot convert a value of type Record to type Table.

Can you please help me understand how to correct this?

Thanks!

Kind regards

Valeria

[dufoq3 · Accepted solution]

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

[ValeriaBreve]

@dufoq3 thank you! This works wonder. Can you please help me understand why - for the inner count -  using

"(x)=> "

works, whereas "each" does not? I am confused on this point.

Thanks again! 🙂  

[dufoq3]

Hi, you can substitute word each and its shortcut _ with any other character or string i.e.:

each _ = 0

is the same as

(myName)=> myName = 0

Try to google it or watch this video.

In this case you need to distinguish between

• inner table (where I used (x)=>) and
• outer table (where I used each _)

Maybe with this example you will understand better.

It filetrs rows where inner Column1 from table [Table1 to every row] equals outer Column1

let
    Table1 = #table(null, {{"a", 10}, {"b", 20}}),
    Table2 = #table(null, {{"a"}, {"b"}, {"c"}}),
    RepeatTable1ToTable2 = Table.AddColumn(Table2, "Table1 to every row", each Table1, type table),
    Ad_Table1Filtered = Table.AddColumn(RepeatTable1ToTable2, "Table1 filtered", 
       each Table.SelectRows([Table1 to every row], (x)=> x[Column1] = [Column1])
  , type table)
in
    Ad_Table1Filtered

[ValeriaBreve]

@dufoq3 Thank you!

[dufoq3]

You're welcome Valeria.

[PhilipTreacy]

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]

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!