Help using conditionals when expanding tables or running queries

[JohnFabric]

I am using the Microsoft Graph API to get organizational information (https://learn.microsoft.com/en-us/graph/api/user-list-manager?view=graph-rest-1.0&tabs=http) by calling

GET https://graph.microsoft.com/v1.0/me?$expand=manager($levels=max;$select=id,displayName)&$select=id,d...

This is working as expected and returning a table containing the org chart for each user in my table

From this table I want to extract the SVP of each user. All the SVPs have "SVP" in their jobTitle like so

The issue is, each user I'm searching on has their SVP into a different part of their management chain. So someone might report directly to the SVP and someone else has 3 managers between them and the SVP

Below is what my Power Query looks like. What I want to do is basically

1. Check if manager.jobTitle includes "SVP"
2. If it does, put manager.displayName in a new column for the user and stop looking
3. If it does not, check if manager.manager.jobTitle includes "SVP"
4. If manager.manager.jobTitle includes "SVP" put manager.manager.displayName in a new column for the user and stop looking
5. if not, keep looking

And so on until I find the users SVP. I've tried doing this in the PowerQuery and by adding a Custom Column but I haven't been able to get anything to compile. How do I go about doing this? Thank you!

#"Converted to Table" = Table.FromRecords({Source}),
#"Expanded manager" = Table.ExpandRecordColumn(#"Converted to Table", "manager", {"@odata.type", "id", "displayName", "jobTitle", "manager"}, {"manager.@odata.type", "manager.id", "manager.displayName", "manager.jobTitle", "manager.manager"}),
#"Expanded manager.manager" = Table.ExpandRecordColumn(#"Expanded manager", "manager.manager", {"@odata.type", "id", "displayName", "jobTitle", "manager"}, {"manager.manager.@odata.type", "manager.manager.id", "manager.manager.displayName", "manager.manager.jobTitle", "manager.manager.manager"}),
#"Expanded manager.manager.manager" = Table.ExpandRecordColumn(#"Expanded manager.manager", "manager.manager.manager", {"@odata.type", "id", "displayName", "jobTitle", "manager"}, {"manager.manager.manager.@odata.type", "manager.manager.manager.id", "manager.manager.manager.displayName", "manager.manager.manager.jobTitle", "manager.manager.manager.manager"}),
#"Expanded manager.manager.manager.manager" = Table.ExpandRecordColumn(#"Expanded manager.manager.manager", "manager.manager.manager.manager", {"@odata.type", "id", "displayName", "jobTitle", "manager"}, {"manager.manager.manager.manager.@odata.type", "manager.manager.manager.manager.id", "manager.manager.manager.manager.displayName", "manager.manager.manager.manager.jobTitle", "manager.manager.manager.manager.manager"}),
#"Expanded manager.manager.manager.manager.manager" = Table.ExpandRecordColumn(#"Expanded manager.manager.manager.manager", "manager.manager.manager.manager.manager", {"@odata.type", "id", "displayName", "jobTitle"}, {"manager.manager.manager.manager.manager.@odata.type", "manager.manager.manager.manager.manager.id", "manager.manager.manager.manager.manager.displayName", "manager.manager.manager.manager.manager.jobTitle"}),
#"Changed Type" = Table.TransformColumnTypes(#"Expanded manager.manager.manager.manager.manager",{{"@odata.context", type text}, {"id", type text}, {"displayName", type text}, {"jobTitle", type text}, {"manager.@odata.type", type text}, {"manager.id", type text}, {"manager.displayName", type text}, {"manager.jobTitle", type text}, {"manager.manager.@odata.type", type text}, {"manager.manager.id", type text}, {"manager.manager.displayName", type text}, {"manager.manager.jobTitle", type text}, {"manager.manager.manager.@odata.type", type text}, {"manager.manager.manager.id", type text}, {"manager.manager.manager.displayName", type text}, {"manager.manager.manager.jobTitle", type text}, {"manager.manager.manager.manager.@odata.type", type text}, {"manager.manager.manager.manager.id", type text}, {"manager.manager.manager.manager.displayName", type text}, {"manager.manager.manager.manager.jobTitle", type text}, {"manager.manager.manager.manager.manager.@odata.type", type text}, {"manager.manager.manager.manager.manager.id", type text}, {"manager.manager.manager.manager.manager.displayName", type text}, {"manager.manager.manager.manager.manager.jobTitle", type text}})

[lbendlin]

what should happen if there is no SVP at the end of the user's management trunk?

[JohnFabric]

There should always be an SVP, but if there is not, we can enter in the name of the highest ranking manager in their chain