Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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

 

1 ACCEPTED SOLUTION
v-yalanwu-msft
Community Support
Community Support

Hi, @Anonymous ;

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.

View solution in original post

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

Hi, @Anonymous ;

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
Super User

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

 

 

 



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.