Power Query - M "Expression.Error: A cyclic reference" when merging with staging table

Question

I have two tables in my Power Query editor. User and Account.

I am trying to add a column to User that counts how many accounts are owned per user.

To do this I have a staging query called UserAccountCount that references Accounts and performs a group on the User Id. It returns a two column table with User.Id and Count.

I then try to merge and expand the User query on the UserAccountCount but it returns a Expression.Error: A cyclic reference was encountered during evaluation..

I agree it is cyclical as I have referenced one query in the other. Is there a way, besides duplication, that I can reference the queries.

I am trying to minimise duplicated code.

I tried to use Table.Buffer so that it would stop evaluating and possibly impact the order that the queries are run.

I should note that the Account table, joins and merges on the User table to bring in the User.Name before it is referenced by UserAccountCount.

Is it bad design to do this in a staging query. Should I just do this using an AddColumn formula?

Answer 1

I would add User.Name to your Group By in your UserAccountCount Query.

Then I would remove the Merge to the User table from the Account Query. You should then be able to expand both the User.Name and Count columns after the Merge with UserAccountCount.