Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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:
RequestID | Requestor | RequestType | Assignee |
1536 | Jane Torvald | Software | Tim Vaca, Sal Rand, Dan Tuli, Lil Hoyt, Jo King, Zack Lee, Joy Bell, , |
1742 | Brett Smith | Consulting Services | Eve Barr, , Zack Lee, |
1770 | Kelly Schmidt | Janitorial 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.
Assignee | RequestID | RequestType |
Tim Vaca | 1536 | Software |
Sal Rand | 1536 | Software |
Dan Tuli | 1536 | Software |
Lil Hoyt | 1536 | Software |
Jo King | 1536 | Software |
Zack Lee | 1536 | Software |
Zack Lee | 1742 | Consulting Services |
Joy Bell | 1536 | Software |
Joy Bell | 1770 | Janitorial Supplies |
Eve Barr | 1742 | Consulting Services |
Sky Blue | 1770 | Janitorial Supplies |
Ray Daw | 1770 | Janitorial Supplies |
Solved! Go to Solution.
Hi, @Anonymous ;
You could use power query and the steps are as follows:
1.split column by ",".
2.Press “Shift” to select all columns that have been separated, and unpivot it.
3.Remove the "Attribute" column ,then remove the empty rows.
The final output is shown below:
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.
Hi, @Anonymous ;
You could use power query and the steps are as follows:
1.split column by ",".
2.Press “Shift” to select all columns that have been separated, and unpivot it.
3.Remove the "Attribute" column ,then remove the empty rows.
The final output is shown below:
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.
@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
✨ 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.
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
118 | |
101 | |
71 | |
61 |