Forum Discussion
Advocate VCyclic reference error in recursive Dataflow function
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
Advocate VSolved.
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