Forum Discussion

ValeriaBreve's avatar
ValeriaBreve
Post 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 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

 

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

     

7 Replies

  • dufoq3's avatar
    dufoq3
    Community Champion
    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

     

    • ValeriaBreve's avatar
      ValeriaBreve
      Post Partisan
      2 years ago

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

        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

         

         

  • PhilipTreacy's avatar
    PhilipTreacy
    Super 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
      Post 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!