Forum Discussion

jcastr02's avatar
jcastr02
Post Prodigy
1 year ago

grouping by ID

hello please see the attachment current versus the expected result. How can I achieve this in Power Query within Power BI. Note that the column PR&A Leader.title is a people picker columns I trie...
Need Help
parry2k's avatar
parry2k
Super User
1 year ago

jcastr02 start a new blank query, click advanced editor and paste this code, follow these steps on real data.

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUVJQitWJVjIGstyLEvNKdBScMxLzUnJSwcImcAWmIAX5+SlFpbk6Ct6JOZlgUTPcopiGmWMRjgUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, #"PR&A Leader.title" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"PR&A Leader.title", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"ID"}, {{"All", each _, type table [ID=nullable number, #"PR&A Leader.title"=nullable text]}}),
    #"Added PR&A Leader.title" = Table.AddColumn(#"Grouped Rows", "PR&A Leader.title", each Lines.ToText([All][#"PR&A Leader.title"],if Table.RowCount([All]) > 1 then ";" else ""), type text),
    #"Removed Columns" = Table.RemoveColumns(#"Added PR&A Leader.title",{"All"})
in
    #"Removed Columns"

 

output: