PowerQuery - trying to create a hierarchy

[keinz]

Hello everyone,

I've been trying to get this resolved for the longest time with no luck.

My dataset currently has:
Name
Reports To
Role

For the "Role" - there are 5 tiers (from Lowest to Highest) - it is showing the current role of "Name"
1. Supervisor
2. Manager
3. Director
4. VP
5. CEO

Repots To = as the name states, it is who "Name" reports to

So this is what I've done so far:

First Column = Supervisor
if [Role] = "Supervisor" then [Name]

Second Column = Manager
if [Role] = "Manager" then [Reports To] else null

Third Column = Director
if [Role] = "Manager" then [Reports To] else if [Role] = "Supervisor" then let
Manager_Name = [Manager],
Director_Name = Table.SelectRows(#"Org Chart", each [Name] = Manager_Name){0}[Reports To]
in
Director_Name
else null

So the theory is that - I am trying to build an org chart.

if a supervisor is the main target in the row - it will populate that supervisors, manager, then director, then VP

i keep running into the error "Expression.Error: A cyclic reference was encountered during evaluation."

I cannot seem to figure out the logic behind it.

Essentially it only errors out if the "Role" is Supervisor - because I am trying to get it to look up - if Role is Supervisor, then look into Manger column then from there look up Name with the Manger's name to look up Reports To - so this way I can still generate the Directors name

I've included a screenshot of roughly what I am looking for.
Top part of the screenshot - is what I currently have
2nd part of the screenshot is what I want to be able to populate

Hope this makes sense


Thank you in advance!

 

[Muhammad_Usman]

The error message "A cyclic reference was encountered during evaluation" typically indicates that there's a circular reference or a loop in your Power Query logic. In Power Query, you can't create a reference that depends on itself directly or indirectly, which is likely happening in your case.

Here's the part of your logic that seems to be causing the issue:

```powerquery
iF [Role] = "Supervisor" then
let
Manager_Name = [Manager],
Director_Name = Table.SelectRows(#"Org Chart", each [Name] = Manager_Name){0}[Reports To]
in
Director_Name
else null
```

The problem is that when `[Role] = "Supervisor"`, it tries to look up the `Manager_Name`, which is `[Manager]`. However, `[Manager]` also depends on the value of `[Role]`, which causes a circular reference. You need to break this circular reference.

One way to handle this in Power Query is to split your transformation into multiple steps. First, calculate the `Manager_Name`, and then calculate the `Director_Name` based on that result. Here's an example of how you could structure your code:

```powerquery
let
Manager_Name = Table.AddColumn(#"PreviousStep", "Manager_Name", each if [Role] = "Supervisor" then [Manager] else null),
Director_Name = Table.AddColumn(Manager_Name, "Director_Name", each if [Role] = "Supervisor" then
let
ManagerName = [Manager_Name],
Director = Table.SelectRows(#"Org Chart", each [Name] = ManagerName){0}[Reports To]
in
Director
else null)
in
Director_Name
```

This way, you first create a new column `Manager_Name` without a circular reference, and then use that column to calculate the `Director_Name`. Make sure to adjust the column names and table references to match your specific dataset and Power Query steps.

 

[keinz]

thank you for that - exactly what was the issue and fixed it!

rather than start a new thread - i want to ask... any question

In my matrix table - when I have it in PowerBi , all the numbers are different and unique
However - when I publish it to my workspace - all of a sudden every row is displaying the same number

Only thing I've done is publish it - what could cause something like this?