Forum Discussion

P0ach3r1's avatar
P0ach3r1
Frequent Visitor
2 years ago

A cyclic reference was encountered

Hi I'm trying to get a power query to work and each way I try I'm encountering the cyclical reference error.

 

So I can get this info in DAX but final aim is to have dynamic filters for a matrix - so I need the data to be compiled in query editor first.  I want a column created (CSATDue) that checks what survey cycle the account is grouped into "SurveyCycle" versus the current quarter we are in. 

 

My company has financial quarters that are not Jan-March, apr-Jun etc so the previous step works out the company quarter we are currently in: (CurrentCmpyQuarter).

 

The code I'm trying to use is below - I can't see how this is causing a cyclical error so hoping you can help.

 

let

    Source = #"Client_list",

    fnCSATDue = (CurrentCmpyQuarter as text, SurveyCycle as text) as text =>

        if SurveyCycle = null then "No SurveyCycle"

        else if ( CurrentCmpyQuarter = "Qtr4" or CurrentCmpyQuarter = "Qtr2") and SurveyCycle = "Q2/Q4" then "Yes"

        else if ( CurrentCmpyQuarter = "Qtr1" or CurrentCmpyQuarter = "Qtr3") and SurveyCycle = "Q1/Q3" then "Yes"

        else "No",

    AddedCustom = Table.AddColumn(Source, "CSATDue", each fnCSATDue([CurrentCmpyQuarter], [SurveyCycle]))

in

    AddedCustom

4 Replies

  • hi, P0ach3r1 first, use SurveyCycle as nullable text.Otherwise your function won't accept nulls. Other than this I don't see any problems with your function. How about Source = #"Client_list"? Does it feel good?

    • P0ach3r1's avatar
      P0ach3r1
      Frequent Visitor

      Hi - #"Client_list"? Does it feel good?

       

      Client_list is the name of the table so I assume so?

      • AlienSx's avatar
        AlienSx
        Super User

        P0ach3r1 your code works just fine. I believe that either smth is wrong with Client_list or bad things happen outside of your code.