Forum Discussion
Cyclic reference error in recursive Dataflow function
- 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
ReplaceMuch credit also to Miguel Escobar - https://www.thepoweruser.com/2019/07/01/recursive-functions-in-power-bi-power-query - for the original concept
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)