Forum Discussion
Transposing AD extract
Hello,
I'm new to PowerBi and new to the community so bare with me.
For a report I want to have an overview off alle the people how have acces to a certain folder on a netwerk drive. I got two files one from the networkdrive with groups who have access to the folder and one extract from AD with users in that group.
The problem however is that some groups contain over 1000 users and the all make a new column in Power BISo each Sam is a member of the group and I'm unable to make a dashboard this way.
Due to privacy reasons I can't post the actual content but my current view is like this (simplified)
| Current | |||||
| Name | Sam.1 | Sam.2 | Sam.3 | Sam.4 | Sam.5 |
| Group A | User 1 | User 2 | User 3 | User 4 | User 5 |
| Group B | User 1 | null | null | null | null |
| Group C | User 1 | null | null | null | null |
| Group D | User 2 | null | null | null | null |
| Group E | User 2 | null | null | null | null |
When transposing the data it looks like this. The groups have become the headers and the members of the group are listen in the cells below.
| Transposed | ||||
| Group A | Group B | Group C | Group D | Group E |
| USER 1 | USER 1 | USER 1 | ||
| null | null | USER 2 | USER 2 | USER 2 |
But what I (I think) want is:
| Username | groupname |
| USER 1 | Group A |
| USER 1 | Group B |
| USER 1 | Group C |
| USER 2 | Group C |
| USER 3 | Group D |
| USER 4 | Group E |
I hope this makes sense what I want to achieve. It probaly takes multiple steps but I just can't see how I can match the members of the group to the networkdrive. Any help or brainwaves appreciated.
Hey Anonymous ,
Have you tried unpivoting the columns in Power Query?
as in
Select the group column in your AD table --> Unpivot Other columns like shown below:
It will give the data in the below structure which is still efficient to use:
2 Replies
Hey Anonymous ,
Have you tried unpivoting the columns in Power Query?
as in
Select the group column in your AD table --> Unpivot Other columns like shown below:
It will give the data in the below structure which is still efficient to use: