Transposing AD extract

[Anonymous]

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. 

[Anonymous]

No I didn't try is this way. And I think it might do the trick. Thank you.

[PC2790 · Accepted solution]

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: