Pivot, Lookup or something else

[689touch]

I have a dataset as attached in the image. There are further columns but this will explain the issue.

I would like to be able to pick a certain process, for example:

GS_RW_MDH_Window_Center Accounts

and determine who (column A) has access to this process. In this case there are two people (highlighted in green)

I have shown a further example where three people have access to another process.

Any thoughts on best way to do this please. I have a master list of approx 25 processes.

THANK YOU!

 

[Alex Blakenburg]

I would simply unpivot the data with Power Query. You would then have a table with UserName, Group No, Process and you could just filter on any or all of those 3 fields.

 

[Alex Blakenburg]

Test Data:

20240328 PQ Unpivot 689touch.xlsx
	A	B	C	D	E	F	G
1	Username	Group 1	Group 2	Group 3	Group 4	Group 5	Group 6
2	Fred	All	Domain Users	GS_RW_MDH_Window_Center Accounts	Cash_Report	Center_Staff	ProcessControl
3	Jim	All	Domain Users	TLD	COVID	ProcessControl	Supervisors
4	Sam	All	Domain Users
5	Rod	All	Domain Users	ProcessControl	WGShare	WindowCenters	Processing
6	Martin	All	Company Car	Domain Users	SSLVPN	Lincoln	GS_RW_MDH_Window_Center Accounts
7	Brett	All	Domain Users	Employee Info	SSLVPN	HR
Sheet1

Result with filter applied to table in Excel:

20240328 PQ Unpivot 689touch.xlsx
	A	B	C
1	Username	Group	Process
4	Fred	Group 3	GS_RW_MDH_Window_Center Accounts
27	Martin	Group 6	GS_RW_MDH_Window_Center Accounts
Table1

Power Query code

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Username"}, "Attribute", "Value"),
    #"Renamed Columns" = Table.RenameColumns(#"Unpivoted Other Columns",{{"Attribute", "Group"}, {"Value", "Process"}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Username", type text}, {"Group", type text}, {"Process", type text}})
in
    #"Changed Type"

Power Query Steps

 

[689touch]

Test Data:

20240328 PQ Unpivot 689touch.xlsx
	A	B	C	D	E	F	G
1	Username	Group 1	Group 2	Group 3	Group 4	Group 5	Group 6
2	Fred	All	Domain Users	GS_RW_MDH_Window_Center Accounts	Cash_Report	Center_Staff	ProcessControl
3	Jim	All	Domain Users	TLD	COVID	ProcessControl	Supervisors
4	Sam	All	Domain Users
5	Rod	All	Domain Users	ProcessControl	WGShare	WindowCenters	Processing
6	Martin	All	Company Car	Domain Users	SSLVPN	Lincoln	GS_RW_MDH_Window_Center Accounts
7	Brett	All	Domain Users	Employee Info	SSLVPN	HR
Sheet1

Result with filter applied to table in Excel:

20240328 PQ Unpivot 689touch.xlsx
	A	B	C
1	Username	Group	Process
4	Fred	Group 3	GS_RW_MDH_Window_Center Accounts
27	Martin	Group 6	GS_RW_MDH_Window_Center Accounts
Table1

Power Query code

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Username"}, "Attribute", "Value"),
    #"Renamed Columns" = Table.RenameColumns(#"Unpivoted Other Columns",{{"Attribute", "Group"}, {"Value", "Process"}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Username", type text}, {"Group", type text}, {"Process", type text}})
in
    #"Changed Type"

Power Query Steps
View attachment 109121

Hi Alex,
The Power Query functionality has worked well. Thank you for your help!

 

[Alex Blakenburg]

You did well to get it up and running that quickly . Thanks for letting me know and glad I could help.