Cyclic reference error in recursive Dataflow function

[ChemEnger]

I have a recursive function setup in Desktop, which works just fine when published to the Service.  I am trying to convert the dataset to a dataflow to be able to share it across the organisation but get the error 'A cyclic reference was encountered during evaluation'.

The function is:

let
    fnReplaceWithNull = (Source, n as number) =>
    let
        //Buffer the Source table
        bSource = Table.Buffer(Source),
        //Get Column Names of Source & buffer
        bColumns = List.Buffer(Table.ColumnNames(bSource)),
        //Count the number of items in 'Not Applicable' table
        ReplaceCount = List.Count(#"Not Applicable"),
        //Replace each occurence of Not Applicable text in all columns of Source
        Replace = Table.ReplaceValue(bSource, #"Not Applicable"{n}, null, Replacer.ReplaceValue, bColumns),
        Checking = if n = ReplaceCount-1 then Replace else @fnReplaceWithNull(Replace, n+1)
     in
        Checking
in
    //Return the table with N/As removed
    fnReplaceWithNull

The inputs are:

• Source - source table with various text such as 'N/A' that I want to replace with null
  • I previously tried including Table.ColumnNames(Source) as an input to the function bt no joy
• #"Not Applicable" - a list with examples of N/A, such as 'n/a', 'Not listed', 'NA', 'MISSING' - 13 items at the moment but I can add to them as needed

The function works just fine in Desktop / Power Query editor but fails when trying to use it in a Dataflow.  Not sure I need the Buffer, particularly within the recursive part of the function

[ChemEnger · Accepted solution]

Solved.

It took a bit of trial and error but it looks like I wasn't recursing in the right place.  I created the recursion as part of the function, not the whole function.  I also referenced just the source table where I wanted to replace the N/A values, as the other parameters were fixed (starting point n=0 and dimension table Not Applicable).  Note also that I have changed the code to create a list from the the Not Applicable table within the function - Power BI was insistent on not allowing a native list in the final output.

(Source as table) =>
let
  //Create a list from a Not Applicable table and buffer it - List not native to Dataflow / Datamart?
  #"NA" = List.Buffer(#"Not Applicable"[#"N/A Text"]),
  //Get a count of the number of rows in the list, for recursion
  CountNA = List.Count(#"NA"),
  //Buffer the source table - not sure this is strictly required, as we actually only use it for the first recursion
  bSource = Table.Buffer(Source),
  //Get a list of the column names for the Source table - it's this list we'll scan across for Replacer function
  Columns = Table.ColumnNames(bSource),

  ReplaceWithNull = (t as table, n as number) =>
    let
      //Replace each occurence of Not Applicable text in all columns of Source
      Replace = Table.ReplaceValue(t, #"NA"{n}, null, Replacer.ReplaceValue, Columns),
      //Keep a count of the recursion; exit once we've cycled through all the Not Applicable list
      Checking = if n = CountNA-1 then Replace else @ReplaceWithNull(Replace, n+1)
    in
      Checking,
  //Finally, actually call the recursion, starting from n=0
  Replace = ReplaceWithNull(bSource, 0)
in
  Replace

Much credit also to Miguel Escobar - https://www.thepoweruser.com/2019/07/01/recursive-functions-in-power-bi-power-query - for the original concept

[ChemEnger]

@v-yalanwu-msft, I have checked out both of the links that you sent - the first descirbes ciruclar references in an Excel workbook, which is not the issue that I'm facing here.  The second is on the topic that I have posted about and I have already included the suggested '@' reference in my code - I have had this working for a long time using Power BI Desktop, as stated in my post.

The issue that I have is trying to convert the code to be used in a Dataflow (and now in Datamart), where I get the error message 'A cyclic reference was encountered during evaluation.' for code that works just fine in Desktop Power Query Editor (and when the data is published to the service)

[v-yalanwu-msft]

Hi， @ChemEnger ；

Please refer to this link:

https://support.microsoft.com/en-us/office/remove-or-allow-a-circular-reference-8540bd0f-6e97-4483-b...

https://community.powerbi.com/t5/Service/Recursive-functions-in-dataflows/m-p/731035

Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.