Forum Discussion
Using values in table as main column for new table
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!
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.
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 ascwhich 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 92Hopefully that helps y'all as well
3 Replies
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.
parry2k it worked! Idk how to mark it as solved or anything, but it got me what I wanted, thank you!
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 ascwhich 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 92Hopefully that helps y'all as well