Power Query: chain of command

05 Jul, 2022

At work, I needed to traverse a table and get a "chain of command" - that is, automatically generate a list of someone's manager, and their manager, and their manager, until it reaches the top of the organisation (the Director General).

I knew that Power Query had the capacity for recursive functions, and this was a great opportunity to try it out.

The task that I actually needed to do was more complex than this, because there were side lookups that I needed to do, but the core engine of it is illustrated here.

In a galaxy, far, far away...

I love Star Wars, and I can think of no better fictional world to illustrate this with. Instead of subordinates and bosses, I'll use Jedi students and masters. Our recursive function will trace back student-master lineages.

For convenience, here is the table in Power Query for you to copy and paste:

#table(
    {"Student", "Master"},
    {
        {"Anakin Skywalker", "Obi-Wan Kenobi"},
        {"Obi-Wan Kenobi", "Qui-Gon Jinn "},
        {"Luke Skywalker", "Obi-Wan Kenobi"},
        {"Ben Solo", "Luke Skywalker "},
        {"Rey", "Luke Skywalker"}
    }
)

Results

We'll call our function getLineage - the code is presented below this.

If we call getLineage("Ben Solo"), we will expect to get:

If we call getLineage("Anakin Skywalker"), we will expect to get:

Functions

lookup

We first need to set up a lookup function, which will fetch just one value when given one input.

(targetName as text) as text => 
    try
        Table.SelectRows(
            source,
            each [Student] = targetName
        ){0}[Master]
    otherwise
        "Not found"

getLineage

This is the main core of the engine. It doesn't look like much; perhaps that because Power Query does all of the hard stuff for you, but it can be tricky to mentally reason about List.Generate, so it was difficult for me to compose at first.

Here is the official documentation on this function, for reference.

The "recursive" part here is each lookup(_), which will use _ as the most recent value in the generated list as the input for the next name to lookup() on.

(name as text) as list => 
    let
        chain = List.Generate(
            () => lookup(name), // starting value
            each _ <> "Not found", // stopping condition
            each lookup(_) // at each iteration
        ),
        final = List.Combine({
            {name}, // first name in the chain
            chain // the rest of the chain
        })
    in
        final

#excel #powerquery #recursive