How to Calculate Hierarchy Levels in Power Query

In this tuto­r­i­al, I demon­strate a prac­ti­cal appli­ca­tion of recur­sive func­tions in Pow­er Query to cal­cu­late hier­ar­chy lev­els in orga­ni­za­tion­al struc­tures or MLM sys­tems. Using a cus­tom func­tion with recur­sion, we can effi­cient­ly deter­mine each per­son­’s posi­tion in a hier­ar­chi­cal struc­ture based on their refer­rer rela­tion­ships, pro­vid­ing valu­able insights for orga­ni­za­tion­al analy­sis and reporting.

Under­stand­ing hier­ar­chy lev­els is essen­tial for visu­al­iz­ing report­ing struc­tures, track­ing MLM down­lines, or map­ping any par­ent-child rela­tion­ships in your data.

Understanding the Problem: Hierarchy Levels in Organizations

In many orga­ni­za­tion­al struc­tures, par­tic­u­lar­ly in mul­ti-lev­el mar­ket­ing (MLM) sys­tems or cor­po­rate hier­ar­chies, under­stand­ing the lev­el depth of each mem­ber is cru­cial. The lev­el rep­re­sents how many steps a per­son is from the top of the orga­ni­za­tion. For instance, in our exam­ple, John is at the top (lev­el 0), N is direct­ly below John (lev­el 1), and Thomas is below N (lev­el 2).

Our sam­ple data con­tains an ID col­umn that unique­ly iden­ti­fies each per­son and a Refer­rer col­umn that indi­cates who brought that per­son into the orga­ni­za­tion. The refer­ral rela­tion­ship estab­lish­es the hier­ar­chi­cal struc­ture we need to ana­lyze. Our goal is to cal­cu­late each per­son­’s hier­ar­chy lev­el auto­mat­i­cal­ly using Pow­er Query’s recur­sive capabilities.

Setting Up Power Query

To begin work­ing with our data, we need to import it into Pow­er Query where we can cre­ate and apply our recur­sive function:

1. Select any cell in your data table
2. Go to the Data tab
3. Click From Table/Range to import your data into Pow­er Query

This imports your data con­tain­ing the ID and Refer­rer columns into the Pow­er Query Edi­tor, where we can start build­ing our solution.

Creating a Recursive Function to Calculate Hierarchy Levels

The core of our solu­tion is a cus­tom func­tion that can call itself (recur­sion) to track up through the hier­ar­chy until it reach­es the top. Here’s how to cre­ate it:

1. In the Pow­er Query Edi­tor, go to the Home tab
2. Click New Source > Oth­er Sources > Blank Query

Now we need to define our func­tion. Our func­tion will require two para­me­ters: the per­son­’s ID we want to cal­cu­late the lev­el for and the com­plete table of peo­ple data to ref­er­ence. The func­tion will:

1. Find the row for the cur­rent person
2. Get their refer­rer’s ID
3. Check if they have a referrer
4. If they don’t (they’re at the top), return 0
5. If they do have a refer­rer, call the same func­tion for the refer­rer and add 1 to the result

The M code for our func­tion looks like this:

(per­son­ID, personTable) =>
let
per­son­Row = Table.SelectRows(personTable, each [ID] = personID){0},
per­son­AboveID = personRow[Referrer],
result = if per­son­AboveID = null
then 0
else @HierarchyLevel(personAboveID, per­son­Table) + 1
in
result

Make sure to name your func­tion Hier­ar­chyLev­el so that the recur­sive ref­er­ence to itself works prop­er­ly. The @ sym­bol in front of the func­tion name empha­sizes that we’re call­ing the same func­tion again.

Applying the Function to Our Data

After cre­at­ing our func­tion, we need to apply it to every row in our data table:

1. Go back to your main query with the import­ed data
2. Click Add Col­umn > Invoke Cus­tom Function
3. Select your Hier­ar­chyLev­el function
4. For per­son­ID, select the ID column
5. For per­son­Table, we need to ref­er­ence the cur­rent table

When set­ting the per­son­Table para­me­ter, we ini­tial­ly might try to ref­er­ence a col­umn name, but this will gen­er­ate errors. Instead, we need to ref­er­ence the entire table from the pre­vi­ous step. In Pow­er Query, we can do this by refer­ring to the pre­vi­ous step name.

Once cor­rect­ly con­fig­ured, the func­tion will cal­cu­late the hier­ar­chy lev­el for each per­son in our table. Thomas, who is referred by N (ID 2) who in turn is referred by John (ID 1), will show as lev­el 2. John, who has no refer­rer, will be at lev­el 0.

Testing and Validating the Recursion

To ver­i­fy our func­tion works cor­rect­ly, we can exam­ine the cal­cu­lat­ed lev­els for each per­son in our organization:

• John (ID 1): Lev­el 0 (top of hier­ar­chy, no referrer)
• Anne (ID 2): Lev­el 1 (referred by John)
• Thomas (ID 5): Lev­el 2 (referred by Anne)
• Paul: Lev­el 3 (referred by Thomas)

We can fur­ther test by chang­ing refer­rer rela­tion­ships. For exam­ple, if we change Paul’s refer­rer from Thomas (ID 5) to some­one who is already at lev­el 3, Paul would then become lev­el 4. After mak­ing such changes in the source data, we can sim­ply refresh our Pow­er Query to see the updat­ed hier­ar­chy levels.

Handling Edge Cases in Your Hierarchy

Our recur­sive func­tion han­dles sev­er­al impor­tant edge cases:

1. Top-lev­el mem­bers (those with null refer­rers) are assigned lev­el 0
2. The func­tion will work for orga­ni­za­tions of any depth, con­tin­u­ing to recurse up the chain until it reach­es the top
3. If the data con­tains cir­cu­lar ref­er­ences (Per­son A refers to Per­son B who refers back to Per­son A), the recur­sion could cre­ate an infi­nite loop — con­sid­er adding error han­dling for this sce­nario in real applications

In a real-world sce­nario, you might want to enhance this func­tion to han­dle more com­plex require­ments, such as detect­ing cir­cu­lar ref­er­ences or pro­cess­ing mul­ti­ple hier­ar­chies with­in the same dataset.

Loading the Results Back to Excel

Once you’re sat­is­fied with your hier­ar­chy lev­el calculations:

1. In the Pow­er Query Edi­tor, go to Home > Close & Load
2. Your data table with the new hier­ar­chy lev­el col­umn will appear in Excel
3. Any time your hier­ar­chy changes, sim­ply refresh the query to recal­cu­late all levels

This pow­er­ful tech­nique allows you to main­tain an up-to-date view of your orga­ni­za­tion­al struc­ture with min­i­mal effort. The recur­sive approach effi­cient­ly han­dles even large hier­ar­chies with­out requir­ing com­plex for­mu­las or man­u­al tracking.

With this solu­tion in place, you can eas­i­ly build reports and visu­al­iza­tions that lever­age hier­ar­chy lev­el infor­ma­tion, enabling bet­ter insights into your orga­ni­za­tion­al struc­ture, MLM down­lines, or any hier­ar­chi­cal data you’re work­ing with.