分享一个强大的PowerQuery自定义函数，轻松转换父子结构数据

///*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 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 IsLeafin    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)) //*/