Recursive query example?

[alotro]

Hello community,

I am trying to put together a query that would list all employees reporting to a manager, all levels down.

Please see attached example. What would be a way to create such a query in PBI? My datasource is Excel.

Thank you!

[Anonymous]

Hi I have done this in Power Query. Is this enquiry still active. I just came across it while looking for other things.

I just noticed you can't run a recursive function in dataflows.

[karthiks180]

Hi Robert, 

Could you please let me know how you found the solution for this enquire in power query?

Thanks

[cpeavyhouse]

You can run recursive queires in dataflows. 

In the Power M Specifications, In the section on recursive functions, it mentions that you need to use an '@' sign (scoping operator) when you're making the recursive call.  Related to https://community.powerbi.com/t5/Service/Recursive-functions-in-dataflows/m-p/778222#M75219

Edited the following for a better answer:

In my solution, I have a table of employees that I put a zero-based index on. I built my hierarchy map_table using the index as my keys.

Employee 
---------- 
index (Int64), name (text), ... 

map_table
------------
employee (Int64), manager(Int64) (manager is the index of another entry in the empolyee table, or null in the case of a root node)

In Power M:

I wrote this function to replicate the DAX PATH function in M.

let
  Output = (map_table as table, element as nullable number) as text => let
        manager = if element is null then null else Record.Field(map_table{element}, "manager"),
        pathOutput = if element is null then null 
                        else if manager is null then Text.From(element)
                        else Text.Combine({@Output(map_table, manager), "|", Text.From(element)})
                        
    in
        pathOutput
in
  Output

The zero-based index I referenced earlier is critical because I cheated when I wrote the function and indexed into the table rather than doing a lookup of the employee. This function will only work if your 'employee' table uses a zero-based index, and your 'map_table' sorted ascending by the 'employee' column.

If you add this to the map_table as a custom column, you can then expand this out to however many levels your hierarchy has with 

Table.SplitColumn(#"Added Custom", "Path_Column", Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv), column_list) 
//column_list is a list of columns I built to hold the hierarchy levels

Alternatively, in DAX:

Add a new column to the map_table:

path = PATH(employee, manager) 

Add as many columns as you have levels of hierarchy

h1 = PATHITEM(map_table[path], 1)
h2 = PATHITEM(map_table[path], 2)
h3 = PATHITEM(map_table[path], 3)
etc.

Now, using either the Power M or DAX method, my map_table looks like:
map_table
-----------
employee(Int64), manager(Int64), path(text), h1(Int64), h2(Int64), h3(Int64)...

To get useful information out of the hierarchy, you have to relate it back to the Employee table.
To do that, I created copies of my Employee table in DAX for each level of the hierachy.
Manager_1 = Employee, Manager_2 = Employee ...
I then created relationships between the Manager tables and the map_table.
map_table -> Employee on employee = index
map_table -> Manager_1 on h1 = index
map_table -> Manager_2 on h2 = index
map_table -> Manager_3 on h3 = index

To make the nice expanding tree view, I used a matrix visual with +/- icons turned on.
I then added the name column from each of the Manager_# tables as rows in the hierarchy

[ChrisMendoza]

@alotro,

How is it the case that 'Anna' and 'Nicholas' report to 'Michael' based off your source?

The pattern @ https://www.daxpatterns.com/parent-child-hierarchies/, is likely what you'll need but you'll need to restructure your source table to be something more like:

Employee	ReportsTo
Michael
John	Michael
Roger	Michael
Anna	Roger
Nicholas	Anna
Suzy	Phil
Phil

[Employee] will need to be unique so you'll likely use an employee id or similar key.

[alotro]

Hi Chris,

Thank you for this quick reply!

I want to show all the levels of the hirearchy/Organizational Structure in my resulting dataset, so when I select Michael it shows Anna (who reports to Roger who reports to Michael) and Nicholas (who reports to Anna who reports to Roger who reports to Michael). 

Shall I be using PATH function in Parent-Child Hierarchies article you provided to display all the levels of hierarchy?

[ChrisMendoza]

@alotro,

Selecting 'Michael' will get you 'Roger' will get you 'Anna' will get you 'Nicholas'.

I do not know of a way to get what you've described in your response.

Figure 6 shows what you can expect in a Matrix table visual if you follow the pattern in the link.