Forum Discussion

ChemEnger's avatar
ChemEnger
Icon for Advocate V rankAdvocate V
4 years ago
Solved

Cyclic reference error in recursive Dataflow function

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...
dataflow
Need Help
  • ChemEnger's avatar
    ChemEnger
    4 years ago

    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