Pivot (or Unpivot?) and Group and Summarize

[domtrump]

Here is my delemna: I have a data table in this format:

But I need to get the data in this format:

So I need the Unique ID, the account numbers from each store and the sum of the transactions and spend for each unique id all in one row. Is there some combination of Transforms that will get me to this end result?

[ronrsnfld · Accepted solution]

Yes Group & Summarize and one of the aggregations will be a Pivot:

1. Note that the automatic data typing of PQ will type all the columns to whole numbers. If they really aren't such, some editing will be required.

2. No error checking for valid entries is done. For example, if there is a missing entry in the "STORE" column, an error will be returned. This can be added if needed.

Main Query

let
    Source = Excel.CurrentWorkbook(){[Name="Table21"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{
        {"Unique Patron ID", Int64.Type}, {"Account#", Int64.Type}, {"STORE", type text}, 
        {"TRANSACTIONS", Int64.Type}, {"SPEND", Int64.Type}}),

//For the Pivot table
    Stores = List.Sort(List.Distinct(#"Changed Type"[STORE])),
    #"Acct Fields" = List.Combine(List.Transform(Stores, each {_ & "_Acct"})),
    #"Rename List" =  List.Zip({Stores, #"Acct Fields"}),
    #"Acct Field Types" = fnRecordTypes(List.Combine(List.Transform(Stores, each {_ & "_Acct"})), List.Repeat({Int64.Type}, List.Count(Stores))),

    #"Grouped Rows" = Table.Group(#"Changed Type", {"Unique Patron ID"}, {
        {"Accounts", (t)=>
            let 
                Pivot=Table.Pivot(t,Stores,"STORE", "Account#"),
                #"Store Columns" = Table.SelectColumns(Pivot,Stores),
                #"Fill Up" = Table.FillUp(#"Store Columns",Stores){0},
                
                #"Rename" = Record.RenameFields(#"Fill Up", #"Rename List")   
            in 
                 #"Rename", #"Acct Field Types" },

        {"TRANSACTIONS", each List.Sum([TRANSACTIONS]), type nullable number}, 
        {"SPEND", each List.Sum([SPEND]), type nullable number}}),
    #"Expanded Accounts" = Table.ExpandRecordColumn(#"Grouped Rows", "Accounts", #"Acct Fields")
in
    #"Expanded Accounts"

Custom Function (paste into blank query)

//Rename Query "fnRecordTypes"

(fieldNames as list, fieldTypes as list)=>
   
let
    rowColumnTypes = List.Transform(fieldTypes, (t) => [Type = t, Optional = false]),
    rowType = Type.ForRecord(Record.FromList(rowColumnTypes, fieldNames),false)
in
   rowType

Data

Results

[ronrsnfld · Accepted solution]

Yes Group & Summarize and one of the aggregations will be a Pivot:

1. Note that the automatic data typing of PQ will type all the columns to whole numbers. If they really aren't such, some editing will be required.

2. No error checking for valid entries is done. For example, if there is a missing entry in the "STORE" column, an error will be returned. This can be added if needed.

Main Query

let
    Source = Excel.CurrentWorkbook(){[Name="Table21"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{
        {"Unique Patron ID", Int64.Type}, {"Account#", Int64.Type}, {"STORE", type text}, 
        {"TRANSACTIONS", Int64.Type}, {"SPEND", Int64.Type}}),

//For the Pivot table
    Stores = List.Sort(List.Distinct(#"Changed Type"[STORE])),
    #"Acct Fields" = List.Combine(List.Transform(Stores, each {_ & "_Acct"})),
    #"Rename List" =  List.Zip({Stores, #"Acct Fields"}),
    #"Acct Field Types" = fnRecordTypes(List.Combine(List.Transform(Stores, each {_ & "_Acct"})), List.Repeat({Int64.Type}, List.Count(Stores))),

    #"Grouped Rows" = Table.Group(#"Changed Type", {"Unique Patron ID"}, {
        {"Accounts", (t)=>
            let 
                Pivot=Table.Pivot(t,Stores,"STORE", "Account#"),
                #"Store Columns" = Table.SelectColumns(Pivot,Stores),
                #"Fill Up" = Table.FillUp(#"Store Columns",Stores){0},
                
                #"Rename" = Record.RenameFields(#"Fill Up", #"Rename List")   
            in 
                 #"Rename", #"Acct Field Types" },

        {"TRANSACTIONS", each List.Sum([TRANSACTIONS]), type nullable number}, 
        {"SPEND", each List.Sum([SPEND]), type nullable number}}),
    #"Expanded Accounts" = Table.ExpandRecordColumn(#"Grouped Rows", "Accounts", #"Acct Fields")
in
    #"Expanded Accounts"

Custom Function (paste into blank query)

//Rename Query "fnRecordTypes"

(fieldNames as list, fieldTypes as list)=>
   
let
    rowColumnTypes = List.Transform(fieldTypes, (t) => [Type = t, Optional = false]),
    rowType = Type.ForRecord(Record.FromList(rowColumnTypes, fieldNames),false)
in
   rowType

Data

Results

[v-xinruzhu-msft · Accepted solution]

Hi,

Thankfs for the solution @ronrsnfld  provided, it is excellent, and i want to offer some more information for user to refer to.

hello @domtrump , you can create a blank query, and input the following code to advanced editor in power query.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bY9LCgNBCAXv0utZ+LTV9izDkNWsA0nuT1oDYTpkI36KR7nvDSxd29YA8iA3mv3zdX+cN+S2z+JO7diuqLKSin1Rzq1XsVGs+QhQwQjj7rrkjryI/qKEENZYciu228cAYEhJAdI5DLzk5sR/2DSwy28lLHUaU/h4Aw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Unique Patron ID" = _t, #"Account#" = _t, STORE = _t, TRANSACTIONS = _t, SPEND = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Unique Patron ID", Int64.Type}, {"Account#", Int64.Type}, {"STORE", type text}, {"TRANSACTIONS", Int64.Type}, {"SPEND", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Unique Patron ID"}, {{"Sum_Transactions", each List.Sum([TRANSACTIONS]), type nullable number}, {"Sum_spend", each List.Sum([SPEND]), type nullable number}, {"Data", each _, type table [Unique Patron ID=nullable number, #"Account#"=nullable number, STORE=nullable text, TRANSACTIONS=nullable number, SPEND=nullable number]}}),
    #"Expanded Data" = Table.ExpandTableColumn(#"Grouped Rows", "Data", {"Account#", "STORE"}, {"Account#", "STORE"}),
    #"Pivoted Column" = Table.Pivot(#"Expanded Data", List.Distinct(#"Expanded Data"[STORE]), "STORE", "Account#"),
    #"Reordered Columns" = Table.ReorderColumns(#"Pivoted Column",{"Unique Patron ID", "store_1", "store_2", "Sum_Transactions", "Sum_spend"})
in
    #"Reordered Columns"

Output

And you can refer to the attachment.

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.