Forum Discussion
Solved
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:
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.
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
6 Replies
daxCommunity 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 ZhiIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
- ImkeF
Community Champion
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
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.
