Power Query - Aggregate multiple columns within group by

[MonkeySam]

Hello PQ Experts,

Based on the raw data below, I am tasked to present that by which File Reference and Order No that Series Number were ordered.

Raw Data

I was able to aggregate all unique File References and Order Nos in the respective columns in Power Query -

= Table.Group(#"Changed Type", {"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}
})

Current PQ Aggregation

Now, the new requirement is to further advise when (by Text Date column) those Files References and Order No happened. To be specific, if multiple File References / Orders were associated to the same Date, the Date should be still distinctly presented (see below expected result).

I am struggled here and wonder if anyone can help? Thanks.

Expected PQ Aggregation

Sample File:

https://docs.google.com/spreadsheets/d/1SsCkFFhguVsFewHbvFhgZNZL2KVE3DPM/edit?usp=drivesdk&ouid=1026...

[dk_dk · Accepted solution]

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.

[ronrsnfld · Accepted solution]

• 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

[pcoley · Accepted solution]

@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!

[MonkeySam]

@AlienSx , @dk_dk , @pcoley , @ronrsnfld 
Hi All,
Really appreciate your ideas and solutions. I tried and all worked as expected.
If I could not mark every of you, my apology for the limit.

[AlienSx · Accepted solution]

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

[pcoley · Accepted solution]

@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!

[pcoley]

@MonkeySam 
Please try adding a CustomColumn concatenating your [TextDate] and the [File Reference] and another Custom Column with the concatenation of [TextDate] and [Order Number].... then resolve as you did with Table.Group (but using the new columns instead)... the step to add each of this custom columns should be something like this (just replace the name of the steps as neede:

NewFileReference= 
Table.AddColumn(
 PreviousStep, 
 "NewFileReferenceCol", 
 each "(" & [DateText] & ") " & [File Reference],
 Text.Type)

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

[ronrsnfld · Accepted solution]

• 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

[dk_dk · Accepted solution]

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.