cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
rdiazjimenez Frequent Visitor
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:

 

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"),

    //Add Sorting
    AddStartSorting = Table.AddIndexColumn(Feed, "Sorting", 1, 1),


// Retrieve all parents per row
    fnAllParents = List.Generate(()=>
    [Result= Feed, Level=1],
    each Table.RowCount([Result]) > 0 and [Level] <= 4,
    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(_))),

    // Fill to the right
    DemoteHeaders = Table.DemoteHeaders(SplitColumnByDelimiter),
    TransposeTable = Table.Transpose(DemoteHeaders),
    FillDown = Table.FillDown(TransposeTable,Table.ColumnNames(TransposeTable)),
    DeTransposeTable = Table.Transpose(FillDown),
    PromoteHeaders = Table.PromoteHeaders(DeTransposeTable, [PromoteAllScalars=true]),

    MergeQueries = Table.NestedJoin(PromoteHeaders,{"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])),
    SortRows = Table.Sort(IsLeaf,{{"NodeKey", Order.Ascending}}),
    AddSorting = Table.AddColumn(SortRows, "Sorting", each Text.Combine(List.Transform(Text.Split([Path],"|"), each Text.AfterDelimiter(_,".", {0, RelativePosition.FromEnd})),"|")),
    ExpandSorting = Table.SplitColumn(AddSorting, "Sorting", Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv), List.Transform({1..Table.RowCount(ConvertToTable)}, each "Sorting "&Text.From(_))),
    AddColumnLevel2 = if Table.HasColumns(ExpandSorting, "Level 2") then ExpandSorting else Table.AddColumn(ExpandSorting, "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),
    AddColumnSorting2 = if Table.HasColumns(AddColumnLevel4, "Sorting 2") then ExpandSorting else Table.AddColumn(ExpandSorting, "Sorting 2", each null, Int64.Type),
    AddColumnSorting3 = if Table.HasColumns(AddColumnSorting2, "Sorting 3") then AddColumnLevel2 else Table.AddColumn(AddColumnLevel2, "Sorting 3", each null, Int64.Type),
    AddColumnSorting4 = if Table.HasColumns(AddColumnSorting3, "Sorting 4") then AddColumnLevel3 else Table.AddColumn(AddColumnLevel3, "Sorting 4", each null, Int64.Type),
    ReorderCols = Table.ReorderColumns(AddColumnSorting4,{"NodeKey", "Name", "Value", "ParentKey", "Path", "HierarchyDepth", "IsLeaf", "Level 1", "Level 2", "Level 3", "Sorting 1", "Sorting 2", "Sorting 3", "Sorting 4"}),
    ChangeColTypes = Table.TransformColumnTypes(ReorderCols,{{"NodeKey", type text}, {"Name", type text}, {"ParentKey", type text}, {"Level 1", type text}, {"Level 2", type text}, {"Level 3", type text}, {"Path", type text}, {"Level 4", type text}, {"HierarchyDepth", Int64.Type}, {"IsLeaf", type logical}, {"Sorting 1", Int64.Type}, {"Sorting 2", Int64.Type}, {"Sorting 3", Int64.Type}, {"Sorting 4", Int64.Type}})
in
    ChangeColTypes

 

 

 

 

Hope this helps somebody else.

 

 

2 ACCEPTED SOLUTIONS

Accepted Solutions
ImkeF
MVP

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

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




View solution in original post

rdiazjimenez Frequent Visitor
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 Team
Community Support Team

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.

 

ImkeF
MVP

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

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




View solution in original post

rdiazjimenez Frequent Visitor
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

ImkeF
MVP

Re: Add index to hierarchy leves for later sorting

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

Thanks.

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




Community Support Team
Community Support Team

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 Kudos Received Badges Coming

New Kudos Received Badges Coming

Kudos to you if you earned one of these! Check your inbox for a notification.

Microsoft Implementation for Communities Wins Award

Microsoft Implementation for Communities Wins Award

Learn about the award-winning innovation that was implemented across Microsoft’s Business Applications Communities.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Solution Authors
Top Kudoed Authors (Last 30 Days)
Users online (1,749)