cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

Using values in table as main column for new table

Hello Community,

 

I have a table that looks like this:

 

Workflow #RequesterApprover1Approver2Processor1Processor2Processor3
12345BobJimNULLMaryNULLNULL
12346BobFrankSarahJanetMaryNULL
12347BillMaryJimNancyJakeBob

 

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:

 

NameWorkflow Count
Bob3
Jim2
Mary3
Frank1
Janet1
Bill1
Nancy1
Jake1

 

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! 

 

 

 

2 ACCEPTED SOLUTIONS

Accepted Solutions
Highlighted
Super User VII
Super User VII

Re: Using values in table as main column for new table

@chasedalton 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.

 






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





View solution in original post

Highlighted
Frequent Visitor

Re: Using values in table as main column for new table

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

View solution in original post

3 REPLIES 3
Highlighted
Super User VII
Super User VII

Re: Using values in table as main column for new table

@chasedalton 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.

 






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





View solution in original post

Highlighted
Frequent Visitor

Re: Using values in table as main column for new table

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

Highlighted
Frequent Visitor

Re: Using values in table as main column for new table

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

View solution in original post

Helpful resources

Announcements
Meet the 2020 Season 2 Power BI Super Users!

Meet the 2020 Season 2 Power BI Super Users!

Find out who's part of the program this season, and welcome the new Super Users.

August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

July 2020 Community Highlights

July 2020 Community Highlights

Learn about the exciting things that happened in July.

Featured Data Story of The Month

Featured Data Story of The Month

All Data Stories Gallery contributions are reviewed for each month. We select a contribution and feature the community member the following month.

Power BI Dev Camp - Developing with .NET Core

Power BI Dev Camp - Developing with .NET Core

Learn how to develop custom web applications for Power BI using .NET Core 3.1 and .NET 5.

Top Solution Authors
Top Kudoed Authors