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]

Hi @PaulBoot ,

Here’s a way I’ve found works well and keeps things running smoothly, even on bigger files:

let
    BufferedTable = Table.Buffer(#"PreviousStep"),
    RowCountColumn = Table.AddColumn(BufferedTable, "RowCount", each Table.RowCount([Total.Table]), Int64.Type)
in
    RowCountColumn

A couple more tips to keep things fast,Try to remove any columns you don’t need from [Total.Table] before the merge, so you’re not carrying extra data through each nested table. If you can, aggregate or count rows at the source (in SQL, a dataflow, etc.) and merge those results in, instead of doing the counting in Power Query, this is often much faster on big datasets. Make sure you turn off background data previews in Power Query while editing, so it’s not constantly re-evaluating the steps.

If you ever need to count only certain rows in the nested table (for example, only non-blanks), you can do something like: Table.AddColumn(BufferedTable, "NonBlankCount", each List.Count(List.Select([Total.Table][YourColumn], each _ <> null)))

[PaulBoot]

Thanks Rohit! That model was a long time ago - don't have it anymore, but thanks for thinking along. i can't say whether or not your proposal would have solved it, but apprecaite the effort!

[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