Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Expands Parent-Child-Hierarchy (Basic Pattern).
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
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment