Forum Discussion

StoryofData's avatar
StoryofData
Icon for Helper III rankHelper III
3 years ago
Solved

Group By and List

I am trying to group by [case],[case status] and [case practice] and create a list from [title], fiddiling around with power query but cannot figure out how to group AND create a list, can anyone please help?

 

Need Help
Tutorial Requests
  • lbendlin's avatar
    lbendlin
    3 years ago
    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXLMS8ypLC5RitWJVjIC8n0T8xLTU4vgfHR5l8yi1OSSfIgCY5CC4uL85MzEklSl2FgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Case = _t, Title = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Case", Int64.Type}, {"Title", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Case"}, {{"Rows", each _, type table [Case=nullable number, Title=nullable text]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Title", each Text.Combine([Rows][Title],",")),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Case", "Title"})
in
    #"Removed Other Columns"

    How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done".

2 Replies

  • lbendlin's avatar
    lbendlin
    Icon for Super User rankSuper User
    3 years ago
    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXLMS8ypLC5RitWJVjIC8n0T8xLTU4vgfHR5l8yi1OSSfIgCY5CC4uL85MzEklSl2FgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Case = _t, Title = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Case", Int64.Type}, {"Title", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Case"}, {{"Rows", each _, type table [Case=nullable number, Title=nullable text]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Title", each Text.Combine([Rows][Title],",")),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Case", "Title"})
in
    #"Removed Other Columns"

    How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done".