Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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

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

Imke Feldmann (The BIccountant)

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

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

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

6 REPLIES 6
Sajus
New Member

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:

 

Sajus_0-1685627211188.png

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

Any help would be appreciated.

dax
Community Support
Community Support

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.

 

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

Imke Feldmann (The BIccountant)

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

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

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

 

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.

 

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

Thanks.

Imke Feldmann (The BIccountant)

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

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors