Forum Discussion
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...
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
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
dufoq3 Thank you!
You're welcome Valeria.