Forum Discussion

Amar-Agnihotri's avatar
Amar-Agnihotri
Resolver I
4 years ago
Solved

How to do aggregation on multiple columns in power query based on a single column?

Hi All,

I have created this table in power query 

 

Now i want data in format like in the customer name column i should have single customer name and in the columns 2017, 2018, 2019 it should be the sum of values for that particular column for that customer. Like - 

Can anyone suggest what steps are needed to achive this kind of format 

 

Need Help
  • Vijay_A_Verma's avatar
    Vijay_A_Verma
    4 years ago

    See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("TZBBFsMgCETv4jqLCFFg2Ta3yPP+16gMtbjh8YbvMPI85VWOYnbOeqlXPVGhtNmPIxiB3sgrYWroOyXTxZVaMWV4dq9sySh0BSm2HKZzD+bt/AW94S3IULgG8/nrGhkiP/ZWSSaSMP5lyE/omTYGW6qlW2Trm88vIRyCj43L5/bp5kC00sbdFiNQLvzFNC9AsjF4K7hb49zIs47xBQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Customer Name" = _t, #"2017" = _t, #"2018" = _t, #"2019" = _t, #"2020" = _t, #"2021" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Customer Name", type text}, {"2017", Int64.Type}, {"2018", Int64.Type}, {"2019", Int64.Type}, {"2020", Int64.Type}, {"2021", Int64.Type}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Customer Name"}, "Attribute", "Value"),
    #"Pivoted Column" = Table.Pivot(#"Unpivoted Other Columns", List.Distinct(#"Unpivoted Other Columns"[Attribute]), "Attribute", "Value", List.Sum)
in
    #"Pivoted Column"

     

2 Replies

  • Vijay_A_Verma's avatar
    Vijay_A_Verma
    Most Valuable Professional
    4 years ago

    See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("TZBBFsMgCETv4jqLCFFg2Ta3yPP+16gMtbjh8YbvMPI85VWOYnbOeqlXPVGhtNmPIxiB3sgrYWroOyXTxZVaMWV4dq9sySh0BSm2HKZzD+bt/AW94S3IULgG8/nrGhkiP/ZWSSaSMP5lyE/omTYGW6qlW2Trm88vIRyCj43L5/bp5kC00sbdFiNQLvzFNC9AsjF4K7hb49zIs47xBQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Customer Name" = _t, #"2017" = _t, #"2018" = _t, #"2019" = _t, #"2020" = _t, #"2021" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Customer Name", type text}, {"2017", Int64.Type}, {"2018", Int64.Type}, {"2019", Int64.Type}, {"2020", Int64.Type}, {"2021", Int64.Type}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Customer Name"}, "Attribute", "Value"),
    #"Pivoted Column" = Table.Pivot(#"Unpivoted Other Columns", List.Distinct(#"Unpivoted Other Columns"[Attribute]), "Attribute", "Value", List.Sum)
in
    #"Pivoted Column"