cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
rdiazjimenez
Frequent Visitor

Add index to hierarchy leves for later sorting

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:

image.png

 

image.png

 

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.

 

 

2 ACCEPTED SOLUTIONS

Accepted Solutions
Super User III
Super User III

Re: Add index to hierarchy leves for later sorting

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

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark my post as a solution. Thanks!

Imke Feldmann

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

 

View solution in original post

rdiazjimenez
Frequent Visitor

Re: Add index to hierarchy leves for later sorting

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

 

View solution in original post

5 REPLIES 5
Community Support
Community Support

Re: Add index to hierarchy leves for later sorting

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.

 

Super User III
Super User III

Re: Add index to hierarchy leves for later sorting

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

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark my post as a solution. Thanks!

Imke Feldmann

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

 

View solution in original post

rdiazjimenez
Frequent Visitor

Re: Add index to hierarchy leves for later sorting

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

 

View solution in original post

Super User III
Super User III

Re: Add index to hierarchy leves for later sorting

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

Thanks.

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark my post as a solution. Thanks!

Imke Feldmann

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

 
Community Support
Community Support

Re: Add index to hierarchy leves for later sorting

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.

 

Helpful resources

Announcements
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Join THE global Microsoft Power Platform event series.

Join THE global Power Platform event series.

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors