PowerQuery - trying to create a hierarchy

keinz

New Member
Joined
Dec 2, 2015
Messages
44
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!
 

Attachments

  • Screenshot 2023-10-24 203551.png
    Screenshot 2023-10-24 203551.png
    18 KB · Views: 17

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
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.
 
Upvote 1
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?
 
Upvote 0

Forum statistics

Threads
1,223,236
Messages
6,170,917
Members
452,366
Latest member
TePunaBloke

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top