///* | |
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)) | |
//*/ |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
This comment has been minimized.
Added control to stop during endless loop