Forum Discussion

NikD's avatar
NikD
New Member
3 years ago
Solved

Sum rows in power query

Hi guys, 

i have a table and need to summ the Amounts based on 2 columns : issue date and customer name , so same customer on the same day would be shown as 1 row with amount summarised.  I assume power query will do, but how? 

IssueDate                        CustomerName                                        Sum

03/10/2022UMBRELLA LOUNGE BAR88.69
03/10/2022UMBRELLA LOUNGE BAR90
04/10/2022FINE GOURMET DELI110.4
04/10/2022LIMOR'S RESTAURANT279.39
04/10/2022KAZACHOK384.92
04/10/2022KAZACHOK76.38

 

03/10/2022UMBRELLA LOUNGE BAR178.69
04/10/2022FINE GOURMET DELI110.4
04/10/2022LIMOR'S RESTAURANT279.39
04/10/2022KAZACHOK461.30

 

thanks,

NIK

Need Help
  • AntrikshSharma's avatar
    AntrikshSharma
    3 years ago

    NikD Paste this in Advanced Editor in Power Query.

     

    let
    Source = Table.FromRows (
        Json.Document (
            Binary.Decompress (
                Binary.FromText (
                    "xZA5CsMwEEWvIlQbebSgpZQTxRHWArLV2PExcv8IUiRgCO7SDf+/X7zZNgy8p9AzYAx3uMahuBAsCrmm0aHBlpZqTaTBe3cKNvAmxRd588mhMdcS3YKuLviWUQpEHNHgYy6PJwBTMypuXmwtNi2tYcoQbo6Lya72cs9TO7kWxLCfiJKE67MuVH3M/+EjJCW8/XN/AQ==",
                    BinaryEncoding.Base64
                ),
                Compression.Deflate
            )
        ),
        let
            _t = ( ( type nullable text ) meta [ Serialized.Text = true ] )
        in
            type table [ IssueDate = _t, CustomerName = _t, Sum = _t ]
    ),
    ChangedType = 
        Table.TransformColumnTypes (
            Source,
            { { "IssueDate", type date }, { "CustomerName", type text }, { "Sum", type number } }
        ),
    GroupedRows =
        Table.Group (
            ChangedType,
            { "IssueDate", "CustomerName" },
            { { "Sum", each List.Sum ( [Sum] ), type nullable number } }
        )
in
    GroupedRows

2 Replies

  • AntrikshSharma's avatar
    AntrikshSharma
    Community Champion
    3 years ago

    NikD Paste this in Advanced Editor in Power Query.

     

    let
    Source = Table.FromRows (
        Json.Document (
            Binary.Decompress (
                Binary.FromText (
                    "xZA5CsMwEEWvIlQbebSgpZQTxRHWArLV2PExcv8IUiRgCO7SDf+/X7zZNgy8p9AzYAx3uMahuBAsCrmm0aHBlpZqTaTBe3cKNvAmxRd588mhMdcS3YKuLviWUQpEHNHgYy6PJwBTMypuXmwtNi2tYcoQbo6Lya72cs9TO7kWxLCfiJKE67MuVH3M/+EjJCW8/XN/AQ==",
                    BinaryEncoding.Base64
                ),
                Compression.Deflate
            )
        ),
        let
            _t = ( ( type nullable text ) meta [ Serialized.Text = true ] )
        in
            type table [ IssueDate = _t, CustomerName = _t, Sum = _t ]
    ),
    ChangedType = 
        Table.TransformColumnTypes (
            Source,
            { { "IssueDate", type date }, { "CustomerName", type text }, { "Sum", type number } }
        ),
    GroupedRows =
        Table.Group (
            ChangedType,
            { "IssueDate", "CustomerName" },
            { { "Sum", each List.Sum ( [Sum] ), type nullable number } }
        )
in
    GroupedRows
  • wdx223_Daniel's avatar
    wdx223_Daniel
    Community Champion
    3 years ago

    NewStep=Table.Group(PreviousStepName,{"IssueDate","CustomerName"},{"Sum",each List.Sum([Sum])})