Using values in table as main column for new table

[Anonymous]

Hello Community,

I have a table that looks like this:

Workflow #	Requester	Approver1	Approver2	Processor1	Processor2	Processor3
12345	Bob	Jim	NULL	Mary	NULL	NULL
12346	Bob	Frank	Sarah	Janet	Mary	NULL
12347	Bill	Mary	Jim	Nancy	Jake	Bob

What I want is to know how many times someone's "touched" something, basically if a name shows up in ANY field (Requester, Approver1, Approver2, Processor1, Processor2, Processor3), to count that Workflow Number towards their total. So the result (ideally) would look like this:

Name	Workflow Count
Bob	3
Jim	2
Mary	3
Frank	1
Janet	1
Bill	1
Nancy	1
Jake	1

Since Bob's name showed up on each line item (doesn't matter which column), he gets a count of 3 workflows, Jim 2 etc. Each line item is based on Workflow Number (it's my primary key, so all unique values). How do I populate those names spread out across multiple fields, condense it into a singular column, and then show how many times their names were associated with a workflow (e.g. Jim is approver1 for workflow #12345 and approver for workflow #12347, so his count is 2). If it helps, if a name is in one of the fields it'll unlikely be repeated. Please help, thanks! 

[parry2k · Accepted solution]

@Anonymous best way to achieve this would be to unpivot your table.

in query editor, click workflow #, right click and select "unpivot other columns"

you will get two column "attribute" and "value", rename value to "name", apply the changes.

in table visual, drop name and workflow # and change the aggregation to workflow # to count, and you will get the result.

[Anonymous · Accepted solution]

Hi there, OP here: Turns out the "Unpivot Other Columns" Power Query M solution, while valid, only works on import tables and not DirectQuery. However I could use UNPIVOT() on the SQL side to get the results how I wanted and just import the results table. This is my query  

select B.Name,count(workflow_number) as Workflow_Count
     from (
                 select Workflow_Number, Position, Name
                      from Workflow_Table
                           UNPIVOT(
                                             Name
                                             FOR Position in (Requester, Approver1, Approver2, Processor1, Processor2, Processor3)
                                           ) AS A
                ) as B
Group by Name
order by name asc

which give me output like this (Which is what I wanted):

Name                       WF_Count
Aaron Carter             5704
Aaron D. Price          4880
Aaron Random         92
Abby Dates               24
Abby Kamehameha  92

Hopefully that helps y'all as well

[parry2k · Accepted solution]

@Anonymous best way to achieve this would be to unpivot your table.

in query editor, click workflow #, right click and select "unpivot other columns"

you will get two column "attribute" and "value", rename value to "name", apply the changes.

in table visual, drop name and workflow # and change the aggregation to workflow # to count, and you will get the result.

[Anonymous]

@parry2k it worked! Idk how to mark it as solved or anything, but it got me what I wanted, thank you!