How to get rid of blank row for power query hierarchy

jaryszek — Thu, 31 Jul 2025 13:45:07 GMT

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

Re: How to get rid of blank row for power query hierarchy

lbendlin — Fri, 01 Aug 2025 01:19:48 GMT

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.

Re: How to get rid of blank row for power query hierarchy

jaryszek — Fri, 01 Aug 2025 08:02:45 GMT

Thanks, hmm interesting.

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

Re: How to get rid of blank row for power query hierarchy

v-prasare — Wed, 06 Aug 2025 12:37:48 GMT

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

topic Re: How to get rid of blank row for power query hierarchy in Developer

How to get rid of blank row for power query hierarchy

Re: How to get rid of blank row for power query hierarchy

Re: How to get rid of blank row for power query hierarchy

Re: How to get rid of blank row for power query hierarchy

Re: How to get rid of blank row for power query hierarchy