Power Query: Table.AggregateTableColumn runs infinite loop?

[aseagull]

Hi. I hope someone can shed light on this without me trying to build a sample file. It seems absolutely bizarre to me. I have been merging queries and expanding table columns since I started in this tool a year+ ago. But I just went to aggregate a table column, and it's apparently in some kind of infinite loop: PQ tells me in the lower right that it's reading hundreds of MB of data (from a local file that is 11 MB) with no end or termination in sight.

But if I change from AggregateTableColumn to ExpandTableColumn, things work fine. Why is the aggregation sending me over a cliff?

If it helps: I'm merging a table with another where there is a many-to-many relationship (unofficially). I really just want to record the presence of a record from the first table in the second table. I was just going to aggregate and count the rows, so I could get 0, 1, 2, or maybe 3 depending on how many matches there are. I thought this was a simple thing to do? Otherwise, I'll have to create some more queries, removing dupes, appending, whatever...just seems like multiple queries and steps where AggregateTableColumn would have taken care of it very quickly.

Thanks for any ideas!

Amon

[ImkeF · Accepted solution]

Hi @aseagull ,

I don't know why that is, but I've published a workaround for your problem here: Performance tip for aggregations after joins in Power Query and Power BI (thebiccountant.com)

[aseagull]

@ImkeF that's a great article. Thank you for sharing! I agree with your comment about the mysterious M engine...