Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi folks,
Is there an efficient way to return a previously split column (by row) back to a comma-separated list in a single cell after performing transformations on it? I have a table like the following, which I split by delimiter into rows so I could perform Left Outer Joins via the Merge Query utility in order to identify the users by another table:
Initial Table:
JobID | Other Users |
111 | UserID1, UserID2 |
222 | UserID3, UserID5, UserID6 |
Resulting Table After Splitting "Other Users" Column And Performing Outer Joing with Other Table to ID users:
Job ID | Other Users | User Names |
111 | UserID1 | Steve |
111 | UserID2 | Sally |
222 | UserID3 | Juan |
222 | UserID5 | Eric |
222 | UserID6 | Tran |
Desired Result (for the purposes of the report, it needs to return to this format):
Job ID | User Names |
111 | Steve, Sally |
222 | Juan, Eric, Tran |
I am not sure how to get that final result so any insights would be appreciated. Thanks!
Solved! Go to Solution.
@pbrenneise Do a Group By step like this:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQ0VNJRCi1OLfJ0AbGCS1LLUpVidVBljEAyiTk5lWAZIyMjuIwxkOVVmpiHIWEKZLkWZSZjSJgBWSFFIB2xAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Job ID" = _t, #"Other Users" = _t, #"User Names" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Job ID", Int64.Type}, {"Other Users", type text}, {"User Names", type text}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"Job ID"}, {{"User Names", each Text.Combine([User Names], ", "), type nullable text}})
in
#"Grouped Rows"
@pbrenneise Do a Group By step like this:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQ0VNJRCi1OLfJ0AbGCS1LLUpVidVBljEAyiTk5lWAZIyMjuIwxkOVVmpiHIWEKZLkWZSZjSJgBWSFFIB2xAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Job ID" = _t, #"Other Users" = _t, #"User Names" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Job ID", Int64.Type}, {"Other Users", type text}, {"User Names", type text}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"Job ID"}, {{"User Names", each Text.Combine([User Names], ", "), type nullable text}})
in
#"Grouped Rows"
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.