SOLVED

Cyclic reference in Power Query - How to avoid?

%3CLINGO-SUB%20id%3D%22lingo-sub-2424066%22%20slang%3D%22en-US%22%3ECircular%20reference%20in%20Power%20Query%20-%20How%20to%20avoid%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2424066%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20All%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESo%2C%20rather%20than%20show%20a%20bunch%20of%20code%20and%20images%2C%20I%20want%20to%20try%20to%20just%20explain%20the%20problem%20and%20see%20what%20options%20there%20are%20to%20deal%20with%20it.%3C%2FP%3E%3CP%3EI%20query%20SQL%20Server%20for%20a%20list%20of%20about%202K%20employees.%26nbsp%3B%20I%20have%20a%20sheet%20called%20Previously_Processed%20with%20the%20employee%20IDs%20of%20people%20who%20this%20process%20has%20run%20the%20month%20before.%26nbsp%3B%20I%20select%205%25%20at%20random%20of%20the%20employees%20read%20in%20from%20SQL%20Server%20excluding%20those%20already%20processed%20(anti%20join)%20and%20create%20a%20sheet%20with%20those%205%25%20of%20employees.%26nbsp%3B%20I%20do%20this%20for%20different%20cuts%20of%20employees%20looked%20at%20different%20ways.%26nbsp%3B%20Each%20builds%20it's%20own%20sheet.%26nbsp%3B%20At%20the%20end%20I%20want%20to%20take%20all%20the%20employee%20IDs%20from%20the%20sheets%20that%20are%20not%20Previously_Processed%20and%20append%20them%20together%20into%20Previously_Processed%20(plus%20whatever%20employees%20already%20exist%20in%20it)%20so%20that%20the%20next%20month%20those%20people%20will%20be%20excluded%20from%20processing.%3C%2FP%3E%3CP%3EIt%20is%20at%20this%20step%20that%20it%20says%20%22circular%20reference%22.%26nbsp%3B%20But%20this%20is%20exactly%20what%20I%20need.%26nbsp%3B%20I%20want%20those%20processed%20this%20month%20to%20be%20excluded%20from%20processing%20next%20month.%26nbsp%3B%20Any%20ideas%20how%20to%20accomplish%20this%3F%3C%2FP%3E%3CP%3ETY%2C%3C%2FP%3E%3CP%3EKirt%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2424066%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EBI%20%26amp%3B%20Data%20Analysis%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2425420%22%20slang%3D%22en-US%22%3ERe%3A%20Circular%20reference%20in%20Power%20Query%20-%20How%20to%20avoid%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2425420%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1073225%22%20target%3D%22_blank%22%3E%40Kirtc%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EDo%20I%20understood%20correctly%20you%20have%20queries%3C%2FP%3E%0A%3CP%3E-%20database%20(DB)%3C%2FP%3E%0A%3CP%3E-%20previously%20proceeds%20(PP)%3C%2FP%3E%0A%3CP%3E-%20DB%20excluding%20PP%20(DB-PP)%3C%2FP%3E%0A%3CP%3Ebased%20on%20the%20latest%20set%20of%20queries%20which%20forms%20sheets%3C%2FP%3E%0A%3CP%3E-%20Sh1%3C%2FP%3E%0A%3CP%3E-%20Sh%202%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EFinally%20you%20create%20query%20which%20appends%20Sh1%2C%20Sh2%2C%20...%20to%20PP%20%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2426581%22%20slang%3D%22en-US%22%3ERe%3A%20Circular%20reference%20in%20Power%20Query%20-%20How%20to%20avoid%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2426581%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3Bthank%20you%20for%20your%20reply.%26nbsp%3B%20I%20think%20you%20follow%20what%20I%20am%20trying%20to%20do.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20multiple%20DB%20queries%20pulling%20from%20SQL%20Server%20per%20vice%20president.%26nbsp%3B%20One%20of%20the%20steps%20in%20each%20of%20those%20queries%20is%20to%20do%20an%20anti-join%20against%20the%20previously%20processed%20(PP)%20sheet%20to%20give%20me%20only%20those%20people%20that%20have%20not%20been%20processed.%26nbsp%3B%20I%20want%20to%20then%20add%20those%20people%20to%20the%20PP%20sheet%20so%20next%20month%20they%20will%20not%20be%20processed%20again.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20think%20Excel%20gives%20me%20a%20%22circular%20reference%22%20error%20because%20it%20feels%20it%20cannot%20guarantee%20that%20the%20query%20to%20add%20people%20to%20the%20PP%20sheet%20will%20happen%20after%20the%20other%20queries%20so%20it%20believes%20there%20is%20a%20conflict.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESomeone%20out%20there%20in%20Excel-land%20who%20does%20much%20more%20Excel%20coding%20than%20I%20surely%20has%20a%20way%20to%20handle%20this%20situation%20that%20is%20clever%20and%20I%20have%20not%20thought%20of.%26nbsp%3B%20Thank%20you.%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

Hi All,

 

So, rather than show a bunch of code and images, I want to try to just explain the problem and see what options there are to deal with it.

I query SQL Server for a list of about 2K employees.  I have a sheet called Previously_Processed with the employee IDs of people who this process has run the month before.  I select 5% at random of the employees read in from SQL Server excluding those already processed (anti join) and create a sheet with those 5% of employees.  I do this for different cuts of employees looked at different ways.  Each builds it's own sheet.  At the end I want to take all the employee IDs from the sheets that are not Previously_Processed and append them together into Previously_Processed (plus whatever employees already exist in it) so that the next month those people will be excluded from processing.

It is at this step that it says "Expression.Error: A cyclic reference was encountered during evaluation.".  But this is exactly what I need.  I want those processed this month to be excluded from processing next month.  Any ideas how to accomplish this?

TY,

Kirt

5 Replies

@Kirtc 

Do I understood correctly you have queries

- database (DB)

- previously proceeds (PP)

- DB excluding PP (DB-PP)

based on the latest set of queries which forms sheets

- Sh1

- Sh 2

 

Finally you create query which appends Sh1, Sh2, ... to PP ?

@Sergei Baklan thank you for your reply.  I think you follow what I am trying to do. 

 

I have multiple DB queries pulling from SQL Server per vice president.  One of the steps in each of those queries is to do an anti-join against the previously processed (PP) sheet to give me only those people that have not been processed.  I want to then add those people to the PP sheet so next month they will not be processed again.

 

I think Excel gives me a "circular reference" error because it feels it cannot guarantee that the query to add people to the PP sheet will happen after the other queries so it believes there is a conflict.

 

Someone out there in Excel-land who does much more Excel coding than I surely has a way to handle this situation that is clever and I have not thought of.  Thank you.

best response confirmed by Kirtc (New Contributor)
Solution

@Kirtc 

Maybe if these are steps within same query. I meant you have separate queries. In general it's always good practice to make operations with separate tables as join, combine, etc as separate queries. With that it shall if on final steps you use references, not initial queries.

Thank you Sergei. I was able to get it to work by simply making the anti-joins to the PP table use a connection only to the PP table whereas when I append to the PP table it is actually linked to and updating the table in Excel.

@Kirtc 

Great to know it works, thank you for the update.