CountRows in nested table performance issues

[PaulBoot]

* EDIT: apparently PowerQuery got scared when I posted this message - the new custom column "= Table.AddColumn(#"Merged with Total based on CN ID", "RowCount", each Table.RowCount([Total.Table]))" now does work quite well. Not sure what I did differently, but there you go. Case closed, I'd say."

Dear all,

This is my first time posting, so apologies if I missed a forum best-practice.

This is to kindly ask for views on / help on a seemingly simple issue I cannot get my head around despite browsing forums for the past 2-3 hours.

Essentially, I'm trying to do a Table.RowCount or List.Count on nested tables inside a column after NestedJoin (see screenshot).

The screenshot shows column "Total.Table" containing nested tables after performing a Merge Query operation based on a common key column between two tables. Now, I'd like PQ to only count / return the number of non-empty rows per nested table.

I've tried doing this by

1. adding a new custom column with the formula "Table.RowCount([Total.Table])" > this keeps on loading one of the source files, but it takes 30+ minutes if it loads at all.
2. Using the "aggregate" function on the Total.Table column with a #Count of ID (ID being one of the columns in the nested table) > same result
3. Creating a new blank query with formula "= let RowCount = List.Count(#"CN RP 2"[Total.Table]) in RowCount" > this gets me the total number of rows not in the nested table but in the main table - I was trying to creat a function but don't quite know how to do this.
4. Creating another blank query with formula "= let TableRowCount = Table.RowCount(#"CN RP 2") in TableRowCount > idem

Finally, I've tried buffing the CN RP 2 table and the nested tables in its column Total.Table - but buffering is a rabbit hole on its own, and I've not managed.

Like I said, I've read a lot of forum posts, and have done this operation for smaller files (although the current file is only 4.8 MB in Excel) without any issues. Would welcome any thoughts on errors and omissions on my end.

Many thanks, wishing you all a good day and an icecream for those suffering the heat.

Paul

[rohit1991]

Solution for Counting Rows in Nested Tables:

• Use Buffered Tables: let
  BufferedTable = Table.Buffer(#"CN RP 2"),
  RowCountColumn = Table.AddColumn(BufferedTable, "RowCount", each Table.RowCount([Total.Table]), Int64.Type)
  in
  RowCountColumn

• Optimize Aggregation: Pre-compute row counts in the source query if possible.
• Use a lightweight column (e.g., ID) for aggregation
• Reduce Nested Table Size: Eliminate unnecessary columns in Total.Table before merging.
• This reduces reloads and improves query performance.

[furthermore]

I have the same problem. the "Differences" Column is loading in powerQuery, but not in PowerBI.
Did you find a solution?

[Anonymous]

Lol

--Nate