I have a custom function that works fine in Power Query, but when I tried to move it to Dataflow during the refresh it breaks. I have read that in case of recursion in power query/dataflow "@" as inclusive identifier reference be used. But, unfortunately, the custon function is too complex for mayself so I can not find a place where to use inclusive identifier reference. I hope that this could be the reason why the function does't work in Datalow.

Here is the function, so if anyboddy can hlep.

let
Source = let func =

(ParChTable as table,
ChildKey as text,
ParentKey as text,
LevelColumnName as text) =>
//*/

let
/*/Debug Parameters
ParChTable = SourceData_Loop,
ChildKey = "NodeKey",
ParentKey = "ParentKey",
LevelColumnName = "Name",
*/

SelectRelevantColumns = Table.SelectColumns(ParChTable, {ChildKey, ParentKey, LevelColumnName}),
#"Changed Type" = Table.TransformColumnTypes(SelectRelevantColumns ,{{ChildKey, type text}, {ParentKey, type text}}),
ReplaceNulls = Table.ReplaceValue(#"Changed Type",null,"",Replacer.ReplaceValue,{ParentKey}),
// CleanParChTable = Table.Distinct(ReplaceNulls , {ChildKey, ParentKey}),
MissingParents = List.Buffer(List.Select(List.Difference(List.Distinct(Table.Column(ReplaceNulls , ParentKey)), List.Distinct(Table.Column(ReplaceNulls , ChildKey))), each _ <> "")),
AddMissingParents = Table.Buffer(Table.Combine({ReplaceNulls , #table({ChildKey, LevelColumnName, ParentKey}, List.Transform(MissingParents, each {_, "Unknown TopLevel"& Text.From(List.PositionOf(MissingParents, _)), ""}))})),
#"Merged Queries0" = Table.NestedJoin(AddMissingParents,{ChildKey},AddMissingParents,{ParentKey},"SelectRelevantColumns",JoinKind.LeftOuter),
CheckIfIsLeaf = Table.AddColumn(#"Merged Queries0", "IsLeaf", each if Table.IsEmpty([SelectRelevantColumns]) then "yes" else "no"),
#"Replaced Value1" = Table.ReplaceValue(CheckIfIsLeaf,null,"",Replacer.ReplaceValue,{ParentKey, LevelColumnName}),
AddStartPath = Table.AddColumn(#"Replaced Value1", "Path", each Text.Trim(Record.Field(_, ChildKey)&"|"&Record.Field(_,ParentKey), "|")),
#"Duplicated Column" = Table.DuplicateColumn(AddStartPath, LevelColumnName, "FirstName"),
Feed = Table.DuplicateColumn(#"Duplicated Column", ParentKey, "FirstParentKey"),

// Retrieve all parents per row
fnAllParents = List.Generate(()=>
[Result= Feed, Level=1, EndlessLoop = false, StopEndlessLoop = false],
each Table.RowCount([Result]) > 0 and not [StopEndlessLoop],
each [ Result= let
#"Merged Queries" = Table.NestedJoin([Result],{ParentKey},AddMissingParents,{ChildKey},"Added Custom",JoinKind.Inner),
#"Removed Columns1" = Table.RemoveColumns(#"Merged Queries",{ParentKey}),
#"Expanded Added Custom" = Table.ExpandTableColumn(#"Removed Columns1", "Added Custom", {ParentKey, LevelColumnName}, {"ParentKey.1", "Name.1"}),
#"Duplicated Column" = Table.DuplicateColumn(#"Expanded Added Custom", "ParentKey.1", ParentKey),
#"Merged Columns" = Table.CombineColumns(#"Duplicated Column",{"Path", "ParentKey.1"},Combiner.CombineTextByDelimiter("|", QuoteStyle.None),"Path"),
#"Merged Columns2" = Table.CombineColumns( #"Merged Columns" ,{LevelColumnName, "Name.1"},Combiner.CombineTextByDelimiter("|", QuoteStyle.None),LevelColumnName)
in
Table.Buffer(#"Merged Columns2"),
Level = [Level]+1,
EndlessLoop = List.Sort(List.Distinct(Table.Column(Result, ChildKey))) = List.Sort(List.Distinct(Table.Column([Result], ChildKey))),
StopEndlessLoop = [EndlessLoop]
]),
ConvertToTable = Table.FromList(fnAllParents, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
ExpandLevel = Table.ExpandRecordColumn(ConvertToTable, "Column1", {"Result", "Level", "EndlessLoop"}, {"Result", "Level", "EndlessLoop"}),
ExpandLG = Table.ExpandTableColumn(ExpandLevel, "Result", {LevelColumnName, ParentKey, ChildKey, "Path", "FirstName", "FirstParentKey"}, {"Name", "ParentKey", "NodeKey", "Path", "FirstName", "FirstParentKey"}),
FilterParents = Table.SelectRows(ExpandLG, each ([ParentKey] = null or [ParentKey] = "")),
#"Removed Columns" = Table.RemoveColumns(FilterParents,{"ParentKey"}),
#"Trimmed Text" = Table.TransformColumns(#"Removed Columns",{{"Path", each Text.Trim(_, "|")}}),
ReverseOrderName = Table.TransformColumns(#"Trimmed Text",{{"Name", each Text.Combine(List.Reverse(Text.Split(_, "|")), "|")}}),
ReverseOrderPath = Table.TransformColumns(ReverseOrderName,{{"Path", each Text.Combine(List.Reverse(Text.Split(_, "|")), "|")}}),
#"Reordered Columns" = Table.ReorderColumns(ReverseOrderPath,{"NodeKey", "FirstParentKey", "Path", "FirstName", "Level", "Name"}),
#"Split Column by Delimiter" = Table.SplitColumn(#"Reordered Columns", "Name", Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv), List.Transform({1..Table.RowCount(ConvertToTable)}, each "Level "&Text.From(_))),
#"Merged Queries" = Table.NestedJoin(#"Split Column by Delimiter",{"NodeKey", "FirstParentKey"},ParChTable ,{ChildKey, ParentKey},"Split Column by Delimiter",JoinKind.LeftOuter),
#"Expanded Split Column by Delimiter" = Table.ExpandTableColumn(#"Merged Queries", "Split Column by Delimiter", List.Difference(Table.ColumnNames(ParChTable), Table.ColumnNames(#"Replaced Value1"))),
Rename = Table.RenameColumns(#"Expanded Split Column by Delimiter",{{"Level", "HierarchyDepth"}}),
Parents = List.Buffer(Rename[FirstParentKey]),
IsLeaf = Table.AddColumn(Rename, "IsLeaf", each not List.Contains(Parents, [NodeKey])),
NoOfInterations = List.Count(fnAllParents),
LastIteration = Table.SelectRows(ExpandLG, each ([Level] = NoOfInterations)),
EndlessLoops = LastIteration[EndlessLoop],
IsEndlessLoop = EndlessLoops{0},
RemainingResults = Table.NestedJoin(IsLeaf, {ChildKey}, LastIteration, {ChildKey}, "x", JoinKind.LeftAnti),
Custom1 = if IsEndlessLoop then [Message= "The data is in an endless loop. Check Table in ""Endless Loop""", #" Endless Loop"= LastIteration] meta [ResultsSoFar = RemainingResults] else IsLeaf
in
Custom1
///*
, documentation = [
Documentation.Name = " Table.SolveParentChild
", Documentation.Description = " Creates columns for all parents, multiple parents are supported
" , Documentation.LongDescription = " Creates columns for all parents, multiple parents are supported
", Documentation.Category = " Table
", Documentation.Version = " 2.0: Checking for endless loops
", Documentation.Source = " local
", Documentation.Author = " Imke Feldmann: www.TheBIccountant.com
", Documentation.Examples = {[Description = " See: http://wp.me/p6lgsG-sl for more details
" , Code = "
", Result = "
"]}]
in
Value.ReplaceType(func, Value.ReplaceMetadata(Value.Type(func), documentation))
in
Source

letSource = let func =(ParChTable as table,ChildKey as text,ParentKey as text,LevelColumnName as text) =>//*/let/*/Debug ParametersParChTable = SourceData_Loop,ChildKey = "NodeKey",ParentKey = "ParentKey",LevelColumnName = "Name",*/SelectRelevantColumns = Table.SelectColumns(ParChTable, {ChildKey, ParentKey, LevelColumnName}),#"Changed Type" = Table.TransformColumnTypes(SelectRelevantColumns ,{{ChildKey, type text}, {ParentKey, type text}}),ReplaceNulls = Table.ReplaceValue(#"Changed Type",null,"",Replacer.ReplaceValue,{ParentKey}),// CleanParChTable = Table.Distinct(ReplaceNulls , {ChildKey, ParentKey}),MissingParents = List.Buffer(List.Select(List.Difference(List.Distinct(Table.Column(ReplaceNulls , ParentKey)), List.Distinct(Table.Column(ReplaceNulls , ChildKey))), each _ <> "")),AddMissingParents = Table.Buffer(Table.Combine({ReplaceNulls , #table({ChildKey, LevelColumnName, ParentKey}, List.Transform(MissingParents, each {_, "Unknown TopLevel"& Text.From(List.PositionOf(MissingParents, _)), ""}))})),#"Merged Queries0" = Table.NestedJoin(AddMissingParents,{ChildKey},AddMissingParents,{ParentKey},"SelectRelevantColumns",JoinKind.LeftOuter),CheckIfIsLeaf = Table.AddColumn(#"Merged Queries0", "IsLeaf", each if Table.IsEmpty([SelectRelevantColumns]) then "yes" else "no"),#"Replaced Value1" = Table.ReplaceValue(CheckIfIsLeaf,null,"",Replacer.ReplaceValue,{ParentKey, LevelColumnName}),AddStartPath = Table.AddColumn(#"Replaced Value1", "Path", each Text.Trim(Record.Field(_, ChildKey)&"|"&Record.Field(_,ParentKey), "|")),#"Duplicated Column" = Table.DuplicateColumn(AddStartPath, LevelColumnName, "FirstName"),Feed = Table.DuplicateColumn(#"Duplicated Column", ParentKey, "FirstParentKey"),// Retrieve all parents per rowfnAllParents = List.Generate(()=>[Result= Feed, Level=1, EndlessLoop = false, StopEndlessLoop = false],each Table.RowCount([Result]) > 0 and not [StopEndlessLoop],each [ Result= let#"Merged Queries" = Table.NestedJoin([Result],{ParentKey},AddMissingParents,{ChildKey},"Added Custom",JoinKind.Inner),#"Removed Columns1" = Table.RemoveColumns(#"Merged Queries",{ParentKey}),#"Expanded Added Custom" = Table.ExpandTableColumn(#"Removed Columns1", "Added Custom", {ParentKey, LevelColumnName}, {"ParentKey.1", "Name.1"}),#"Duplicated Column" = Table.DuplicateColumn(#"Expanded Added Custom", "ParentKey.1", ParentKey),#"Merged Columns" = Table.CombineColumns(#"Duplicated Column",{"Path", "ParentKey.1"},Combiner.CombineTextByDelimiter("|", QuoteStyle.None),"Path"),#"Merged Columns2" = Table.CombineColumns( #"Merged Columns" ,{LevelColumnName, "Name.1"},Combiner.CombineTextByDelimiter("|", QuoteStyle.None),LevelColumnName)inTable.Buffer(#"Merged Columns2"),Level = [Level]+1,EndlessLoop = List.Sort(List.Distinct(Table.Column(Result, ChildKey))) = List.Sort(List.Distinct(Table.Column([Result], ChildKey))),StopEndlessLoop = [EndlessLoop]]),ConvertToTable = Table.FromList(fnAllParents, Splitter.SplitByNothing(), null, null, ExtraValues.Error),ExpandLevel = Table.ExpandRecordColumn(ConvertToTable, "Column1", {"Result", "Level", "EndlessLoop"}, {"Result", "Level", "EndlessLoop"}),ExpandLG = Table.ExpandTableColumn(ExpandLevel, "Result", {LevelColumnName, ParentKey, ChildKey, "Path", "FirstName", "FirstParentKey"}, {"Name", "ParentKey", "NodeKey", "Path", "FirstName", "FirstParentKey"}),FilterParents = Table.SelectRows(ExpandLG, each ([ParentKey] = null or [ParentKey] = "")),#"Removed Columns" = Table.RemoveColumns(FilterParents,{"ParentKey"}),#"Trimmed Text" = Table.TransformColumns(#"Removed Columns",{{"Path", each Text.Trim(_, "|")}}),ReverseOrderName = Table.TransformColumns(#"Trimmed Text",{{"Name", each Text.Combine(List.Reverse(Text.Split(_, "|")), "|")}}),ReverseOrderPath = Table.TransformColumns(ReverseOrderName,{{"Path", each Text.Combine(List.Reverse(Text.Split(_, "|")), "|")}}),#"Reordered Columns" = Table.ReorderColumns(ReverseOrderPath,{"NodeKey", "FirstParentKey", "Path", "FirstName", "Level", "Name"}),#"Split Column by Delimiter" = Table.SplitColumn(#"Reordered Columns", "Name", Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv), List.Transform({1..Table.RowCount(ConvertToTable)}, each "Level "&Text.From(_))),#"Merged Queries" = Table.NestedJoin(#"Split Column by Delimiter",{"NodeKey", "FirstParentKey"},ParChTable ,{ChildKey, ParentKey},"Split Column by Delimiter",JoinKind.LeftOuter),#"Expanded Split Column by Delimiter" = Table.ExpandTableColumn(#"Merged Queries", "Split Column by Delimiter", List.Difference(Table.ColumnNames(ParChTable), Table.ColumnNames(#"Replaced Value1"))),Rename = Table.RenameColumns(#"Expanded Split Column by Delimiter",{{"Level", "HierarchyDepth"}}),Parents = List.Buffer(Rename[FirstParentKey]),IsLeaf = Table.AddColumn(Rename, "IsLeaf", each not List.Contains(Parents, [NodeKey])),NoOfInterations = List.Count(fnAllParents),LastIteration = Table.SelectRows(ExpandLG, each ([Level] = NoOfInterations)),EndlessLoops = LastIteration[EndlessLoop],IsEndlessLoop = EndlessLoops{0},RemainingResults = Table.NestedJoin(IsLeaf, {ChildKey}, LastIteration, {ChildKey}, "x", JoinKind.LeftAnti),Custom1 = if IsEndlessLoop then [Message= "The data is in an endless loop. Check Table in ""Endless Loop""", #" Endless Loop"= LastIteration] meta [ResultsSoFar = RemainingResults] else IsLeafinCustom1///*, documentation = [Documentation.Name = " Table.SolveParentChild", Documentation.Description = " Creates columns for all parents, multiple parents are supported" , Documentation.LongDescription = " Creates columns for all parents, multiple parents are supported", Documentation.Category = " Table", Documentation.Version = " 2.0: Checking for endless loops", Documentation.Source = " local", Documentation.Author = " Imke Feldmann: www.TheBIccountant.com", Documentation.Examples = {[Description = " See: http://wp.me/p6lgsG-sl for more details" , Code = "", Result = ""]}]inValue.ReplaceType(func, Value.ReplaceMetadata(Value.Type(func), documentation))inSource

Forum Discussion

Recursive functions in dataflows

Recursive function in Power Query

Recursion Strategy

PowerBI Loop/Recursive Function

Cyclic reference error in recursive Dataflow function