Power Query - Aggregate multiple columns within group by

Hi MonkeySam 

my approach was slightly different, doing 2 group bys (first with the date, to include it in the file reference and order no lists, and then once again just on the allperiodkey/series number to aggregate the rows correctly

Here is my code:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("tY2xDsIwDET/JXOlns9xqoylTBUbMFVVhMTIyv/jACqILgzgwTqf7XvTFMQIQHO0Qm5DE/rDEN1JkRk+QsbrheaKoLVdKy4VKafq9ZvB+3FfH3e4V92L5OWq1Ju5eSOlFYn/IXUs1Bcpwp4k/SDpNyRgTTqdH+lC/V3ufAM=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"File Reference" = _t, #"Order No" = _t, #"Text Date" = _t, Date = _t, #"Client ID" = _t, Client = _t, Currency = _t, #"Series Number" = _t, #"P Code" = _t, AllPeriodKey = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}}),
    step = Table.Group(#"Changed Type", {"AllPeriodKey", "Series Number", "Text Date"}, {
{"SN", each List.Distinct([Series Number])},
{"Currency", each List.Distinct([Currency])},
{"P", each List.Distinct([P Code])},
{"All File References", each Text.Combine(List.Distinct([File Reference]), "; "), type nullable text},
{"All Order Nos", each Text.Combine(List.Distinct([Order No]), "; "), type nullable text}
}),
    #"Expanded SN#" = Table.ExpandListColumn(step, "SN"),
    #"Expanded Currency#" = Table.ExpandListColumn(#"Expanded SN#", "Currency"),
    #"Expanded P#" = Table.ExpandListColumn(#"Expanded Currency#", "P"),
    #"Added Custom" = Table.AddColumn(#"Expanded P#", "Custom", each "("&[Text Date]&") "&[All Order Nos]),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each "("&[Text Date]&") "&[All File References]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"All Order Nos", "All File References"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Custom", "All Order Nos"}, {"Custom.1", "All File References"}}),
    step2 = Table.Group(#"Renamed Columns", {"AllPeriodKey", "Series Number"}, {
{"SN#", each List.NonNullCount(List.Distinct([SN])), Int64.Type},
{"Currency#", each List.NonNullCount(List.Distinct([Currency])), Int64.Type},
{"P#", each List.NonNullCount(List.Distinct([P])), Int64.Type},
{"All File References", each Text.Combine(List.Distinct([All File References]), "; "), type nullable text},
{"All Order Nos", each Text.Combine(List.Distinct([All Order Nos]), "; "), type nullable text}
})
in
    step2

Which gives the same result as your screenshot. (I added an extra row to your sample file to validate my process)

Let me know if you have any questions.

• In your Table.Group function,
  • Group by {"AllPeriodKey", "Series Number","Date"}
  • prepend the All File and All Order column specification with the date:
       "(" &Date.ToText([Date]{0},"ddMMMyy") & ") "
• Delete the Date column
• Group by "AllPeriodKey" and "Series Number"
  • Combine the unique rows in each column with a line feed
• In Excel, be sure to set the format of the table to allow wordwrap

let
    Source = Excel.CurrentWorkbook(){[Name="Examples"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"File Reference", type text}, {"Order No", type text}, {"Text Date", type text}, {"Date", type date}, {"Client ID", type text}, {"Client", type text}, {"Currency", type text}, {"Series Number", type text}, {"P Code", type text}, {"AllPeriodKey", type text}}),
    
    #"Grouped Rows" = Table.Group(#"Changed Type", {"AllPeriodKey", "Series Number","Date"}, {
{"SN#", each List.NonNullCount(List.Distinct([Series Number])), Int64.Type},
{"Currency#", each List.NonNullCount(List.Distinct([Currency])), Int64.Type},
{"P#", each List.NonNullCount(List.Distinct([P Code])), Int64.Type},

{"All File References", each "(" &Date.ToText([Date]{0},"ddMMMyy") & ") " & Text.Combine(List.Distinct([File Reference]), "; "), type nullable text},

{"All Order Nos", each "(" &Date.ToText([Date]{0},"ddMMMyy") & ") " & Text.Combine(List.Distinct([Order No]), "; "), type nullable text}

}),
    #"Removed Columns" = Table.RemoveColumns(#"Grouped Rows",{"Date"})  ,
   
    #"Combine Rows" = Table.Group(#"Removed Columns",{"AllPeriodKey","Series Number"},{
        {"Combine", (t)=>
            [a=Table.ToColumns(t),
             b=List.Transform(a, each List.Distinct(_)),
             c=List.Transform(b,(L)=> List.Transform(L, each  Text.From(_)) ),
             d=List.Transform(c, each Text.Combine(_, "#(lf)")),
             e=Record.FromList(d,Table.ColumnNames(t))
             
             ][e]}}),
    #"Expanded Combine" = Table.ExpandRecordColumn(#"Combine Rows", "Combine", {"SN#", "Currency#", "P#", "All File References", "All Order Nos"}, {"SN#", "Currency#", "P#", "All File References", "All Order Nos"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Combine",{{"All Order Nos", type text}, {"All File References", type text}, {"P#", type text}, {"Currency#", type text}, {"SN#", type text}, {"Series Number", type text}, {"AllPeriodKey", type text}})
in
    #"Changed Type1"

Results from your data

MonkeySam Please try with conditional replacements: 

let
  Source = Excel.CurrentWorkbook(){[ Name = "Examples" ]}[Content],
  #"Changed Type" = Table.TransformColumnTypes (
    Source,
    {
      { "File Reference", type text },
      { "Order No", type text },
      { "Text Date", type text },
      { "Date", type date },
      { "Client ID", type text },
      { "Client", type text },
      { "Currency", type text },
      { "Series Number", type text },
      { "P Code", type text },
      { "AllPeriodKey", type text }
    }
  ),
  Replace1 = Table.ReplaceValue (
    #"Changed Type",
    each [File Reference],
    each if [Text Date] <> null 
         then "(" & [Text Date] & ") " & [File Reference] else null,
    Replacer.ReplaceValue,
    { "File Reference" }
  ),
  Replace2 = Table.ReplaceValue (
    Replace1,
    each [Order No],
    each if [Text Date] <> null 
         then "(" & [Text Date] & ") " & [Order No] else null,
    Replacer.ReplaceValue,
    { "Order No" }
  ),
  #"Grouped Rows" = Table.Group (
    Replace2,
    { "AllPeriodKey", "Series Number" },
    {
      { "SN#", each List.NonNullCount ( List.Distinct ( [Series Number] ) ), Int64.Type },
      { "Currency#", each List.NonNullCount ( List.Distinct ( [Currency] ) ), Int64.Type },
      { "P#", each List.NonNullCount ( List.Distinct ( [P Code] ) ), Int64.Type },
      {
        "All File References",
        each Text.Combine ( List.Distinct ( [File Reference] ), "; 
"
  ),
        type nullable text
      },
      {
        "All Order Nos",
        each Text.Combine ( List.Distinct ( [Order No] ), "; 
"
  ),
        type nullable text
      }
    }
  )
in
  #"Grouped Rows"

 I hope this helps. if so please mark it as a solution. Kudos are welcome!

let
    fx_nnc = (col) => (tbl) as number => List.NonNullCount(List.Distinct(Table.Column(tbl, col))),
    fx_string = (col01, col02) => (tbl) as text => [
        sx = List.Buffer(List.Zip({Table.Column(tbl, col01), Table.Column(tbl, col02)})),
        acc = List.Accumulate(
            List.Positions(sx),
            "",
            (s, c) => s & Text.Format(
                if c = 0 then "#{0} #{1}" else if sx{c}{0} = sx{c - 1}{0} then "; #{1}" else "#(lf)#{0} #{1}",
                sx{c}
            )
        )
    ][acc],
    Source = Excel.CurrentWorkbook(){[Name="Examples"]}[Content],
    brackets = Table.TransformColumns(Source, {"Text Date", (x) => "(" & x & ")"}),
    sort = Table.Sort(brackets, {"AllPeriodKey", "Series Number", "Date"}),
    group = Table.Group(
        sort, 
        {"AllPeriodKey", "Series Number"},
        {
            {"SN#", fx_nnc("Series Number")},
            {"Currency#", fx_nnc("Currency")},
            {"P#", fx_nnc("P Code")},
            {"All File References", fx_string("Text Date", "File Reference")},
            {"All Order Nos", fx_string("Text Date", "Order No")}
        },
        GroupKind.Local
    )
in
    group