cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
EnthusedBA
New Member

How to query a column in a different query which has multiple values?

I have a table of about 2,000 requests which at various stages in their lifecycle get multiple assignees.  Most columns are single values such as RequestID, Requestor, CreateDate, DueDate, RequestType, etc.  But the Assignees column holds all Assignees that work on the record, separated by commas.  Examples:

RequestIDRequestorRequestTypeAssignee
1536Jane TorvaldSoftwareTim Vaca, Sal Rand, Dan Tuli, Lil Hoyt, Jo King, Zack Lee, Joy Bell, , 
1742Brett SmithConsulting ServicesEve Barr, , Zack Lee,
1770Kelly SchmidtJanitorial Supplies,Joy Bell, Sky Blue, Ray Daw

 

I need the resulting table to pull out the Assignees and list the RequestIDs that person is working on and the Request Type, even though the original Assignee column is 1) multiple values separated by commas and 2) commas appear in inconvenient places (before, after, and middle).  In the example, the resulting list would show a row for Zack Lee for #1536 and another row for Zack Lee for #1742, only one row for Eve Barr for #1742, etc.   I have been able to pull out in a separate query the list of Assignees, and removed the extra commas and spaces and duplicates.  I think what is now needed is an iterating query that takes a value in that list, say Zack Lee, and searches each row on the Assignee column to bring back every ID where Zack Lee appears, then moves on to the next assignee.  

Even a hint about the approach to take, like which iterative function would be a smart start, would be a big help.  Desired end result, although these people would in the real life situation be connected to many more records than shown here.

AssigneeRequestIDRequestType
Tim Vaca1536Software
Sal Rand1536Software
Dan Tuli1536Software
Lil Hoyt1536Software
Jo King1536Software
Zack Lee1536Software
Zack Lee1742Consulting Services
Joy Bell1536Software
Joy Bell1770Janitorial Supplies
Eve Barr1742Consulting Services
Sky Blue1770Janitorial Supplies
Ray Daw1770Janitorial Supplies

 

2 REPLIES 2
v-yalanwu-msft
Community Support
Community Support

Hi, @EnthusedBA ;

You could use power query and the steps are as follows:

1.split column by ",".

vyalanwumsft_0-1627264858054.png

2.Press “Shift” to select all columns that have been separated, and unpivot it.

vyalanwumsft_1-1627264962510.png

3.Remove the "Attribute" column ,then remove the empty rows.

vyalanwumsft_2-1627265134111.png

The final output is shown below:

vyalanwumsft_3-1627265228011.png

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("RY/NbsJADIRfZbRnH/pD4R5aCQEnNuJQlIOVbMHC2UXLJihvX9NW5eaxPJ9nDgf3/PY6d+TWHAPqlEfWzqRPX+XGOdhYS489t0zwrNhx7AjvHFEPKoStKFZpKoR1wkbikfDJ7RnbEO6rCVVQJRBcQ/ZsMXsxZJVDKfC9lJOpZYrXQYt54UMepQ1X236MARXnfPf+E/8giyc72Bh4gm9PvXTlt4GUlMVC+uFyUfnB0CODP9ukg+Xa8WQVbq5pvgE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [RequestID = _t, Requestor = _t, RequestType = _t, Assignee = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"RequestID", Int64.Type}, {"Requestor", type text}, {"RequestType", type text}, {"Assignee", type text}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "Assignee", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Assignee.1", "Assignee.2", "Assignee.3", "Assignee.4", "Assignee.5", "Assignee.6", "Assignee.7", "Assignee.8", "Assignee.9"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Assignee.1", type text}, {"Assignee.2", type text}, {"Assignee.3", type text}, {"Assignee.4", type text}, {"Assignee.5", type text}, {"Assignee.6", type text}, {"Assignee.7", type text}, {"Assignee.8", type text}, {"Assignee.9", type text}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type1", {"RequestID", "Requestor", "RequestType"}, "Attribute", "Value"),
    #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Columns",{"Attribute"}),
    #"Replaced Value" = Table.ReplaceValue(#"Removed Columns"," ","",Replacer.ReplaceText,{"Value"}),
    #"Filtered Rows" = Table.SelectRows(#"Replaced Value", each [Value] <> null and [Value] <> "")
in
    #"Filtered Rows"

Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

parry2k
Super User III
Super User III

@EnthusedBA it is better to split Assignee column in Power Query by rows and then it should be straightforward. choose split column by a delimiter, here is screenshot

 

parry2k_0-1627255341830.png

 

Follow us on LinkedIn

 

Check my latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would  Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

 

Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.

 

 

 






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.





Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Top Solution Authors
Top Kudoed Authors