How to get rid of blank row for power query hierarchy

[jaryszek]

This is my table:

Employee IDManager IDEmployeeManagerCleanPathPathLevel1Level2Level3

1010	null	Roy F			1010	1010	null	null
1011	1010	Pam H	Roy F		1010|1011	1010	1011	null
1012	1010	Guy L	Roy F		1010|1012	1010	1012	null
1013	1011	Roger M	Pam H	1010	1010|1011|1013	1010	1011	1013
1014	1011	Kaylie S	Pam H	1010	1010|1011|1014	1010	1011	1014
1015	1012	Mike O	Guy L	1010	1010|1012|1015	1010	1012	1015
1016	1012	Rudy Q	Guy L	1010	1010|1012|1016	1010	1012	1016

I created it using power query code. It is recursive hierarchy. 

Now i want to get rid of blanks on matrix/slicer for all levels. 
I could filter them but generally it can be problematic becuase imagine that i have 20 levels. 
What in this case? Manually go and show all of them?

Code used:

Main query:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQwNFDSUQKioPxKBTcIEywYqwOWNYTxdZQCEnMVPJBUgoRrwEqgao0Qat1LKxV8sKg1gqk1hggagtWkpxYp+CLZAFNkglDknViZk5mqEIxFlSlEFch238zsVAV/JPthaswQaoJKUyoVAlHUxAIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Employee ID" = _t, #"Manager ID" = _t, Employee = _t, Manager = _t, Path = _t]),
    #"Removed Columns" = Table.RemoveColumns(Source,{"Path"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Removed Columns",{{"Employee ID", Int64.Type}, {"Manager ID", Int64.Type}}),
    CleanPath= Table.AddColumn(#"Changed Type", 
                            "CleanPath", each 
                                   fnGetPathHierarchyWorking("Employee ID", "Manager ID", [Manager ID], #"Changed Type")),
    AddRemoveItems = Table.AddColumn(CleanPath, "Path", 
        each 
            let 
                a = List.RemoveItems(Text.Split(_[CleanPath], "|"), {""}),
                b = List.Reverse(a) ,
                c  = 
                    if _[Manager ID] = null or _[Manager ID] = "" 
                    then Text.From(_[Employee ID])
                    else Text.Combine(List.Transform(b, Text.From) & List.Transform({_[Manager ID], _[Employee ID]}, Text.From), "|")
            in c),
    AddLevels = fnAddLevelsWorking(AddRemoveItems, "Path")
    // Path2 = Table.AddColumn(AddRemoveItems, "Path2", each fnGetHierarchyImproved("Employee ID", "Manager ID", _, AddRemoveItems))
in
    AddLevels

Adding "Levels" columns dynamically:

(inputTable as table, pathCol as text) as table =>
let
    // Add PathList column by splitting the path
    AddPathList = Table.AddColumn(
        inputTable,
        "PathList",
        each Text.Split(Record.Field(_, pathCol), "|")
    ),

    MaxLevel = List.Max(List.Transform(AddPathList[PathList], each List.Count(_))),

    LevelColumns = List.Transform({1..MaxLevel}, each "Level" & Text.From(_)),

    AddLevels = List.Accumulate(
        LevelColumns,
        AddPathList,
        (state, current) =>
            Table.AddColumn(
                state,
                current,
                (row) =>
                    let
                        idx = Number.From(Text.Range(current, 5)) - 1,
                        pathList = row[PathList]
                    in
                        if idx < List.Count(pathList) then pathList{idx} else null
            )
    ),

    Result = Table.RemoveColumns(AddLevels, {"PathList"})
in
    Result

Hierarchy code:

let
    fnGetPathHierarchyFinal = (childCol as text, parentCol as text, currentRow as record, sourceTable as table) as text =>
        let
            parentValue = Record.Field(currentRow, parentCol),
            childValue =  Record.Field(currentRow, childCol),
            rawPath = fnGetPathHierarchyWorking(childCol, parentCol, parentValue, sourceTable),
            cleanedList = List.RemoveItems(Text.Split(rawPath, "|"), {""}),
            reversed = List.Reverse(cleanedList),
            fullPath = 
                if parentValue = null or parentValue = "" 
                then Text.From(childValue)
                else Text.Combine(List.Transform(reversed, Text.From) & List.Transform({parentValue, childValue}, Text.From), "|")
        in
            fullPath
in
    fnGetPathHierarchyFinal

Recursive Hierarchy:

let
    fnGetPathHierarchy = (childCol as text, parentCol as text, currentParent as any, sourceTable as table) as text =>
        let
            mylist = Table.Column(Table.SelectRows(sourceTable, each Record.Field(_, childCol) = currentParent), parentCol),
            result = Text.Combine(List.Transform(mylist, Text.From))
        in
            Text.TrimEnd(
                if result = "" then ""
                else @result & "|" & @fnGetPathHierarchy(childCol, parentCol, result, sourceTable),
                "|"
            )
in
    fnGetPathHierarchy

1. How to get rid of blanks with ease from slicer/matrix? 
2. How to automatically add new levels of hierarchy? 

Best,
Jacek

[v-prasare]

Hi @jaryszek,

To better assist you, could you please share:

• A sample .pbix file that reproduces the issue or demonstrates your scenario.

• The expected outcome, such as a screenshot, an Excel file, or a brief explanation of what you'd like to see.

This will help us understand your requirement clearly and provide an accurate solution.

Thanks,

Prashanth Are

MS fabric community support

[jaryszek · Accepted solution]

No way of doing this @v-prasare .

https://community.fabric.microsoft.com/t5/Desktop/How-can-I-reduce-blank-rows-in-a-hierarchical-setu...
https://radacad.com/removing-blanks-from-organizational-ragged-hierarchy-in-power-bi-matrix-visual/

Best,
Jacek

[lbendlin]

There's no need for any of this. All you need is to blank out the parent value for the top entries in the hierarchy.

[jaryszek]

Thanks, hmm interesting. 

So how to make this working? 
You are saying that Level1 should be empty?