Expands Parent-Child-Hierarchy (Basic Pattern).

Table.ParentChildExplosion.pq

let
fnParentChild = let
    func = 
(ParentChildTable as table, ParentColumnName as text, ChildColumnName as text) =>
  let
    /* Debug Parameters
    ParentChildTable = PCTable_,
    ParentColumnName = "Parent_",
    ChildColumnName = "Child_",
    */

    PCTable = Table.Buffer(ParentChildTable), 
    TopParents = List.Difference(
      List.Distinct(Table.Column(PCTable, ParentColumnName)), 
      Table.Column(PCTable, ChildColumnName)
    ), 
    StartingTable = Table.SelectRows(
      PCTable, 
      each List.Contains(TopParents, Record.Field(_, ParentColumnName))
    ), 
    #"Renamed Columns" = Table.RenameColumns(
      StartingTable, 
      {{ParentColumnName, ParentColumnName & "_0"}, {ChildColumnName, ChildColumnName & "_0"}}
    ), 
    ParentChildExplosion = List.Generate(
      () => [Result = #"Renamed Columns", Counter = 0], 
      each not Table.IsEmpty([Result]), 
      each [
        Counter = [Counter] + 1, 
        #"Merged Queries" = Table.NestedJoin(
          [Result], 
          {ChildColumnName & "_" & Text.From([Counter])}, 
          PCTable, 
          {ParentColumnName}, 
          "PCTable", 
          JoinKind.Inner
        ), 
        Result = Table.ExpandTableColumn(
          #"Merged Queries", 
          "PCTable", 
          {ChildColumnName}, 
          {ChildColumnName & "_" & Text.From(Counter)}
        )
      ],
      each [Result]
    ),
    Custom1 = Table.Combine( ParentChildExplosion ) meta [NumberOfLevels = List.Count(ParentChildExplosion)]
in
    Custom1,
    
    documentation = [
        Documentation.Name = " Table.ParentChildExplosion ",
        Documentation.Description
            = " Expands Parent-Child-Hierarchy. ",
        Documentation.LongDescription
            = " Expands Parent-Child-Hierarchy (Basic Pattern).",
        Documentation.Category = " Table.Transformation ",
        Documentation.Source = " www.TheBIcountant.com alternative approach: https://www.thebiccountant.com/2017/02/14/dynamically-flatten-parent-child-hierarchies-in-dax-and-powerbi/ . ",
        Documentation.Version = " 1.0 ",
        Documentation.Author = " Imke Feldmann ",
        Documentation.Examples = {

[Description = "  ", Code = " ", Result = " "
]}]
in
    Value.ReplaceType(func, Value.ReplaceMetadata(Value.Type(func), documentation))
in
    fnParentChild