Add index to hierarchy leves for later sorting

[rdiazjimenez]

Hi,

Using: Excel Power Query (Win64O365)

I'm trying to sort pivot table items based on their current position with a dynamically built hierarchy.

Currently using the code generously provided by @ImkeF  in this post:

https://www.thebiccountant.com/2017/02/14/dynamically-flatten-parent-child-hierarchies-in-dax-and-po...

Process:

1- Load table from Excel into Power Query

2- With M code dynamically create columns for hierarchy levels

3- With M code dynamically create columns for sorting levels (one sorting column for each level) 

4- Add table to Data Model

5- In Power Pivot use the Sort by column option in each column (level) to be sorted by the ones created in step 3

Current issues (see images below):

1) Fill Child levels with parent names

2) Limit the number of levels calculated to a certain number (in order to not beak hierarchy in PowerPivot) (if possible)

3) Add sorting columns for each level in the hierarchy dynamically (based on their current row position)

4) Goal: Define item's order in pivot table by their order in table and not alphabethically

Link to source file: PivotTableDinamicHierarchySorted.xlsx

Source table:

Current M Code:

let

//Debug Parameters
ParChTable = TableSource,
ChildKey = "NodeKey",
ParentKey = "ParentKey",
LevelColumnName = "Name",




SelectRelevantColumns = Table.SelectColumns(ParChTable, {ChildKey, ParentKey, LevelColumnName}),
SortNodeKey = Table.Sort(SelectRelevantColumns,{{"NodeKey", Order.Ascending}}),
ChangeType = Table.TransformColumnTypes(SortNodeKey ,{{ChildKey, type text}, {ParentKey, type text}}),
ReplaceNulls = Table.ReplaceValue(ChangeType,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, _)), ""}))})),
MergeQueries0 = Table.NestedJoin(AddMissingParents,{ChildKey},AddMissingParents,{ParentKey},"SelectRelevantColumns",JoinKind.LeftOuter),
CheckIfIsLeaf = Table.AddColumn(MergeQueries0, "IsLeaf", each if Table.IsEmpty([SelectRelevantColumns]) then "yes" else "no"),
ReplaceValue1 = Table.ReplaceValue(CheckIfIsLeaf,null,"",Replacer.ReplaceValue,{ParentKey, LevelColumnName}),
AddStartPath = Table.AddColumn(ReplaceValue1, "Path", each Text.Trim(Record.Field(_, ChildKey)&"|"&Record.Field(_,ParentKey), "|")),
DuplicateColumn = Table.DuplicateColumn(AddStartPath, LevelColumnName, "FirstName"),
Feed = Table.DuplicateColumn(DuplicateColumn, ParentKey, "FirstParentKey"),

// Retrieve all parents per row
fnAllParents = List.Generate(()=>
[Result= Feed, Level=1],
each Table.RowCount([Result]) > 0,
each [ Result= let
MergeQueries = Table.NestedJoin([Result],{ParentKey},AddMissingParents,{ChildKey},"Added Custom",JoinKind.Inner),
RemoveColumns1 = Table.RemoveColumns(MergeQueries,{ParentKey}),
ExpandAddedCustom = Table.ExpandTableColumn(RemoveColumns1, "Added Custom", {ParentKey, LevelColumnName}, {"ParentKey.1", "Name.1"}),
DuplicateColumn = Table.DuplicateColumn(ExpandAddedCustom, "ParentKey.1", ParentKey),
MergeColumns = Table.CombineColumns(DuplicateColumn,{"Path", "ParentKey.1"},Combiner.CombineTextByDelimiter("|", QuoteStyle.None),"Path"),
MergeColumns2 = Table.CombineColumns(MergeColumns,{LevelColumnName, "Name.1"},Combiner.CombineTextByDelimiter("|", QuoteStyle.None),LevelColumnName)
in
MergeColumns2,
Level = [Level]+1
]),
ConvertToTable = Table.FromList(fnAllParents, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
ExpandLevel = Table.ExpandRecordColumn(ConvertToTable, "Column1", {"Result", "Level"}, {"Result", "Level"}),
ExpandResult = Table.ExpandTableColumn(ExpandLevel, "Result", {LevelColumnName, ParentKey, ChildKey, "Path", "FirstName", "FirstParentKey"}, {"Name", "ParentKey", "NodeKey", "Path", "FirstName", "FirstParentKey"}),
FilterRows1 = Table.SelectRows(ExpandResult, each ([ParentKey] = null or [ParentKey] = "")),
RemoveColumns = Table.RemoveColumns(FilterRows1,{"ParentKey"}),
TrimText = Table.TransformColumns(RemoveColumns,{{"Path", each Text.Trim(_, "|")}}),
ReverseOrderName = Table.TransformColumns(TrimText,{{"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"}),
SplitColumnByDelimiter = Table.SplitColumn(#"Reordered Columns", "Name", Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv), List.Transform({1..Table.RowCount(ConvertToTable)}, each "Level "&Text.From(_))),
MergeQueries = Table.NestedJoin(SplitColumnByDelimiter,{"NodeKey", "FirstParentKey"},ParChTable ,{ChildKey, ParentKey},"Split Column by Delimiter",JoinKind.LeftOuter),
ExpandSplitColumnByDelimiter = Table.ExpandTableColumn(MergeQueries, "Split Column by Delimiter", List.Difference(Table.ColumnNames(ParChTable), Table.ColumnNames(ReplaceValue1))),
Rename = Table.RenameColumns(ExpandSplitColumnByDelimiter,{{"Level", "HierarchyDepth"}, {"FirstName", "Name"}, {"FirstParentKey", "ParentKey"}}),
Parents = List.Buffer(Rename[ParentKey]),
IsLeaf = Table.AddColumn(Rename, "IsLeaf", each not List.Contains(Parents, [NodeKey])),
ReorderCols = Table.ReorderColumns(IsLeaf,{"NodeKey", "Name", "Value", "ParentKey", "Level 1", "Level 2", "Level 3", "Path", "HierarchyDepth", "IsLeaf"}),
SortRows = Table.Sort(ReorderCols,{{"NodeKey", Order.Ascending}})
in
SortRows

Any help would be really appreciated.

Edit # 2:

With help of @ImkeF here is the code that solves issue # 2 (and also validates if levels 2 - 4 and their respective sorting columns are present so they don't break Power Pivot model)

Final code (Edit 2019-12-26 -> Cleanned up some code):

let func =  

(SourceTable as table,
NodeKey as text,
ParentKey as text,
LabelColumn as text) =>

let

/*
    //Debug Parameters
    SourceTable = TablaCuentaReporte,
    NodeKey = "Cuenta",
    ParentKey = "CuentaPadre",
    LabelColumn = "Nombre",
*/


    SelectRelevantColumns = Table.SelectColumns(SourceTable, {NodeKey,ParentKey,LabelColumn}),

    ChangeType = Table.TransformColumnTypes(SelectRelevantColumns, {
        {NodeKey,type text},
        {ParentKey,type text}
    }),

    ReplaceNulls = Table.ReplaceValue(ChangeType, null, "", Replacer.ReplaceValue, {ParentKey}),

    MissingParents = List.Buffer(List.Select(List.Difference(List.Distinct(Table.Column(ReplaceNulls, ParentKey)), List.Distinct(Table.Column(ReplaceNulls, NodeKey))), each _ <> "")),

    AddMissingParents = Table.Buffer(Table.Combine({
        ReplaceNulls,
        #table({
            NodeKey,
            LabelColumn,
            ParentKey
        }, List.Transform(MissingParents, each {
            _,
            "Unknown TopLevel" & Text.From(List.PositionOf(MissingParents, _)),
            ""
        }))
    })),

    MergeQueriesMissingParents = Table.NestedJoin(AddMissingParents, {NodeKey}, AddMissingParents, {ParentKey}, "SelectRelevantColumns", JoinKind.LeftOuter),

    CheckIfIsLeaf = Table.AddColumn(MergeQueriesMissingParents, "IsLeaf", each
        if Table.IsEmpty([SelectRelevantColumns]) then "yes"
        else "no", type logical),

    ReplaceNullsParentLabelCols = Table.ReplaceValue(CheckIfIsLeaf, null, "", Replacer.ReplaceValue, {
        ParentKey,
        LabelColumn
    }),

    AddStartPath = Table.AddColumn(ReplaceNullsParentLabelCols, "Path", each Text.Trim(Record.Field(_, NodeKey) & "|" & Record.Field(_, ParentKey), "|")),

    AddTempLabelColumn = Table.DuplicateColumn(AddStartPath, LabelColumn, "TempLabelColumn"),

    AddTempParentKeyColumn = Table.DuplicateColumn(AddTempLabelColumn, ParentKey, "TempParentKey"),

    // Retrieve all parents per row
    fnRetrieveAllParents = List.Generate(() => [Result = AddTempParentKeyColumn, Level = 1],
        each Table.RowCount([Result]) > 0 and[Level] <= 6,
        each [Result =
            let
                MergeQueries = Table.NestedJoin([Result], {
                    ParentKey
                }, AddMissingParents, {
                    NodeKey
                }, "Added Custom", JoinKind.Inner),
                RemoveColumns1 = Table.RemoveColumns(MergeQueries, {
                    ParentKey
                }),
                ExpandAddedCustom = Table.ExpandTableColumn(RemoveColumns1, "Added Custom", {
                    ParentKey,
                    LabelColumn
                }, {
                    "ParentKey.1",
                    "Name.1"
                }),
                DuplicateColumn = Table.DuplicateColumn(ExpandAddedCustom, "ParentKey.1", ParentKey),
                MergeColumns = Table.CombineColumns(DuplicateColumn, {
                    "Path",
                    "ParentKey.1"
                }, Combiner.CombineTextByDelimiter("|", QuoteStyle.None), "Path"),
                MergeColumns2 = Table.CombineColumns(MergeColumns, {
                    LabelColumn,
                    "Name.1"
                }, Combiner.CombineTextByDelimiter("|", QuoteStyle.None), LabelColumn) in
                MergeColumns2,
                Level = [Level] + 1
        ]),

    ConvertAllParentsToTable = Table.FromList(fnRetrieveAllParents, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    
    ExpandAllParentsLevel = Table.ExpandRecordColumn(ConvertAllParentsToTable, "Column1", {"Result","Level"}, {"Result","Level"}),

    ExpandAllParentsResult = Table.ExpandTableColumn(ExpandAllParentsLevel, "Result", {LabelColumn,ParentKey,NodeKey,"Path","TempLabelColumn","TempParentKey"}, {"LabelColumn","ParentKey","NodeKey","Path","TempLabelColumn","TempParentKey"}),
    
    FilterAllParents = Table.SelectRows(ExpandAllParentsResult, each([ParentKey] = null or [ParentKey] = "")),
    RemoveParentKeyCol = Table.RemoveColumns(FilterAllParents, {
        "ParentKey"
    }),
    RemoveLastPipeFromPath = Table.TransformColumns(RemoveParentKeyCol, {
        {
            "Path",
            each Text.Trim(_, "|")
        }
    }),
    ReverseOrderLabelColumn = Table.TransformColumns(RemoveLastPipeFromPath, {
        {
            "LabelColumn",
            each Text.Combine(List.Reverse(Text.Split(_, "|")), "|")
        }
    }),
    ReverseOrderPath = Table.TransformColumns(ReverseOrderLabelColumn, {
        {
            "Path",
            each Text.Combine(List.Reverse(Text.Split(_, "|")), "|")
        }
    }),
    ReorderTempCols = Table.ReorderColumns(ReverseOrderPath, {
        "NodeKey",
        "TempParentKey",
        "Path",
        "TempLabelColumn",
        "Level",
        "LabelColumn"
    }),
    SplitLabelColByLevel = Table.SplitColumn(ReorderTempCols, "LabelColumn", Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv), List.Transform({
        1..Table.RowCount(ConvertAllParentsToTable)
    }, each "Level " & Text.From(_))),

    // Fill to the right
    DemoteHeaders = Table.DemoteHeaders(SplitLabelColByLevel),

    TransposeTable = Table.Transpose(DemoteHeaders),

    FillDown = Table.FillDown(TransposeTable, Table.ColumnNames(TransposeTable)),

    DeTransposeTable = Table.Transpose(FillDown),

    PromoteHeaders = Table.PromoteHeaders(DeTransposeTable, [PromoteAllScalars = true]),

    MergeSourceTable = Table.NestedJoin(PromoteHeaders, {
        "NodeKey",
        "TempParentKey"
    }, Table.ReplaceValue(SourceTable, null, "", Replacer.ReplaceValue, {ParentKey}), {
        NodeKey,
        ParentKey
    }, "MergedSourceTable", JoinKind.LeftOuter),

    ExpandSourceTable = Table.ExpandTableColumn(MergeSourceTable, "MergedSourceTable", List.Difference(Table.ColumnNames(SourceTable), Table.ColumnNames(ReplaceNullsParentLabelCols))),

    ListParents = List.Buffer(ExpandSourceTable[TempParentKey]),

    AddColIsLeaf = Table.AddColumn(ExpandSourceTable, "IsLeaf", each not List.Contains(ListParents, [NodeKey])),

    RenameTempCols = Table.RenameColumns(AddColIsLeaf, {
        {
            "Level",
            "HierarchyDepth"
        },
        {
            "NodeKey",
            NodeKey
        },
        {
            "TempLabelColumn",
            LabelColumn
        },
        {
            "TempParentKey",
            ParentKey
        }
    }),

    AddLevelSorting = Table.AddColumn(RenameTempCols, "Sorting", each Text.Combine(List.Transform(Text.Split([Path], "|"), each Text.AfterDelimiter(_, ".", {
        0,
        RelativePosition.FromEnd
    })), "|")),

    ExpandLevelSorting = Table.SplitColumn(AddLevelSorting, "Sorting", Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv), List.Transform({
        1..Table.RowCount(ConvertAllParentsToTable)
    }, each "Sorting " & Text.From(_))),

    AddColumnLevel2 =
    if Table.HasColumns(ExpandLevelSorting, "Level 2") then ExpandLevelSorting
else Table.AddColumn(ExpandLevelSorting, "Level 2", each null, type text),

    AddColumnLevel3 =
    if Table.HasColumns(AddColumnLevel2, "Level 3") then AddColumnLevel2
else Table.AddColumn(AddColumnLevel2, "Level 3", each null, type text),

    AddColumnLevel4 =
    if Table.HasColumns(AddColumnLevel3, "Level 4") then AddColumnLevel3
else Table.AddColumn(AddColumnLevel3, "Level 4", each null, type text),

    AddColumnLevel5 =
    if Table.HasColumns(AddColumnLevel4, "Level 5") then AddColumnLevel4
else Table.AddColumn(AddColumnLevel4, "Level 5", each null, type text),

    AddColumnLevel6 =
    if Table.HasColumns(AddColumnLevel5, "Level 6") then AddColumnLevel5
else Table.AddColumn(AddColumnLevel5, "Level 6", each null, type text),

    AddColumnSorting2 =
    if Table.HasColumns(AddColumnLevel6, "Sorting 2") then AddColumnLevel6
else Table.AddColumn(AddColumnLevel6, "Sorting 2", each null, Int64.Type),

    AddColumnSorting3 =
    if Table.HasColumns(AddColumnSorting2, "Sorting 3") then AddColumnSorting2
else Table.AddColumn(AddColumnSorting2, "Sorting 3", each null, Int64.Type),

    AddColumnSorting4 =
    if Table.HasColumns(AddColumnSorting3, "Sorting 4") then AddColumnSorting3
else Table.AddColumn(AddColumnSorting3, "Sorting 4", each null, Int64.Type),

    AddColumnSorting5 =
    if Table.HasColumns(AddColumnSorting4, "Sorting 5") then AddColumnSorting4
else Table.AddColumn(AddColumnSorting4, "Sorting 5", each null, Int64.Type),

    AddColumnSorting6 =
    if Table.HasColumns(AddColumnSorting5, "Sorting 6") then AddColumnSorting5
else Table.AddColumn(AddColumnSorting5, "Sorting 6", each null, Int64.Type),


    ReorderCols = Table.ReorderColumns(AddColumnSorting6, Table.ColumnNames(SourceTable) & {
        "Path",
        "HierarchyDepth",
        "IsLeaf",
        "Level 1",
        "Level 2",
        "Level 3",
        "Level 4",
        "Level 5",
        "Level 6",
        "Sorting 1",
        "Sorting 2",
        "Sorting 3",
        "Sorting 4",
        "Sorting 5",
        "Sorting 6"}),

    ChangeColTypes = Table.TransformColumnTypes(ReorderCols, {
        {
            NodeKey,
            type text
        },
        {
            LabelColumn,
            type text
        },
        {
            ParentKey,
            type text
        },
        {
            "Path",
            type text
        },
        {
            "HierarchyDepth",
            Int64.Type
        },
        {
            "IsLeaf",
            type logical
        },
        {
            "Level 1",
            type text
        },
        {
            "Level 2",
            type text
        },
        {
            "Level 3",
            type text
        },
        {
            "Level 4",
            type text
        },
        {
            "Level 5",
            type text
        },
        {
            "Level 6",
            type text
        },
        {
            "Sorting 1",
            Int64.Type
        },
        {
            "Sorting 2",
            Int64.Type
        },
        {
            "Sorting 3",
            Int64.Type
        },
        {
            "Sorting 4",
            Int64.Type
        },
        {
            "Sorting 5",
            Int64.Type
        },
        {
            "Sorting 6",
            Int64.Type
        }
    }), SortCols = Table.Sort(ChangeColTypes, {
        {
            "Sorting 1",
            Order.Ascending
        },
        {
            "Sorting 2",
            Order.Ascending
        },
        {
            "Sorting 3",
            Order.Ascending
        },
        {
            "Sorting 4",
            Order.Ascending
        },
        {
            "Sorting 5",
            Order.Ascending
        },
        {
            "Sorting 6",
            Order.Ascending
        }
    }) in SortCols

,
documentation = [
    Documentation.Name =  " Table.ResolveParentChild",
    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.Source = "local",
    Documentation.Author = "Imke Feldmann: www.TheBIccountant.com | Adapted by Ricardo Diaz www.ricardodiaz.co",
    Documentation.Examples = {[Description =  "See: http://wp.me/p6lgsG-sl for more details",
        Code = "",
        Result = ""
        ]}
    ] 
 in 
    Value.ReplaceType(func, Value.ReplaceMetadata(Value.Type(func), documentation))

Hope this helps somebody else.

[ImkeF · Accepted solution]

Hi @rdiazjimenez 

Agree with @dax  , don't understand the requirement from 2)

Technically, you could limit the number of levels in the fnAllParents step by adding a limit on the Level in the List.Generate function:

each Table.RowCount([Result]) > 0

each Table.RowCount([Result]) > 0 and Level <= YourMaxLevel

[Anonymous]

Hi @rdiazjimenez , 

thank's for the code edit #2, it really could help me to solve 2 problems I've got - filling empty child levels with parent names and adding sort columns for every level. 

I have NodeKey values as text, therefore PowerQuery shows an error "DataFromat.Error: We couldn't convert to Number. Details: A"

when using your code, using the following data:

Cannot find where M tries to convert NodeKey into numeric value...

Any help would be appreciated.

[dax]

Hi rdiazjimenez,

According to your code sample, it seems that you have solved your part of your problem. But I don't understand your requirement.

If possible, could you please explain " Limit the number of levels calculated to a certain number (in order to not beak hierarchy in PowerPivot) (if possible)" to me?

Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[ImkeF · Accepted solution]

Hi @rdiazjimenez 

Agree with @dax  , don't understand the requirement from 2)

Technically, you could limit the number of levels in the fnAllParents step by adding a limit on the Level in the List.Generate function:

each Table.RowCount([Result]) > 0

each Table.RowCount([Result]) > 0 and Level <= YourMaxLevel

[rdiazjimenez · Accepted solution]

Thank you both @ImkeF and @dax ,

I tried at that line, but missed that the one that I had to limit was [Level] and not Table.RowCount([Result])

so Imke's solution was correct by adding the surrounding brackets.

each Table.RowCount([Result]) > 0 and [Level] <= YourMaxLevel

[dax]

Hi rdiazjimenez,

It seems that you solve this problem, right?If so and if you'd like to, you could mark corresponding post as answer or share your solutions. That way, people who in this forum and have similar issue will benefit from it.

Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[ImkeF]

Great to hear @rdiazjimenez  ! Please mark the answers then.

Thanks.