Forum Discussion

MalyMajo's avatar
MalyMajo
Frequent Visitor
4 years ago

AD group and resource hierarchy

Hello all,

 

I have tabular data, which includes list of resources and the groups to which they belong.

Is it possible to format the data somehow to always show the structure when a resources is filtered?

 

IndexLevelValue
1Level1ProjectName_All
2Level2ProjectName_Team1
3Level3ProjectName_Team1_Offshore
4Level4peter.pan
5Level3ProjectName_Team1_Onshore
6Level4peter.parker
7Level4tony.hawk
8Level2ProjectName_Team2
9Level3ProjectName_Team2_Offshore
10Level4kendrick.lamar
11Level4tony.stark
12Level4slim.shady
13Level4cristiano.ronaldo
14Level4lionel.messi
15Level4alexander.ovechkin
16Level3ProjectName_Team2_Onshore
17Level4tony.hawk

 

So for example if I would filter tony.hawk, I would like to see following table:

 

IndexLevelValue
1Level1ProjectName_All
2Level2ProjectName_Team1
5Level3ProjectName_Team1_Onshore
7Level4tony.hawk
8Level2ProjectName_Team2
16Level3ProjectName_Team2_Onshore
17Level4tony.hawk

 

Please note, that resources can be in multiple teams. Additionally, resources does not necessary need to always be on "Level4". It can be anything between Level3 and Level6. 
Naming convention of groups is always same - in each case it starts with "ProjectName".

Need Help

2 Replies

  • BA_Pete's avatar
    BA_Pete
    Icon for Super User rankSuper User
    4 years ago

    Hi MalyMajo ,

     

    To filter like this in Power Query, you would do the following:

    Table.SelectRows(
    previousStep,
    each [Value] = "tony.hawk" or Text.Contains([Value], "ProjectName")
)

     

    Pete

    • MalyMajo's avatar
      MalyMajo
      Frequent Visitor
      4 years ago

      Actually, the project name never contains "ProjectName", it was just an example.

      What I want to achieve is to see the hierarchy. So if I filter Tony Hawk, I want to see under which hierarchy he belongs.