cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
NikD
New Member

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

1 ACCEPTED SOLUTION
AntrikshSharma
Community Champion
Community Champion

@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

View solution in original post

2 REPLIES 2
AntrikshSharma
Community Champion
Community Champion

@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
Super User
Super User

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

Helpful resources

Announcements
Vote for T-Shirt Design

Power BI T-Shirt Design Challenge 2023

Vote for your favorite t-shirt design now through March 28.

March 2023 Update3

Power BI March 2023 Update

Find out more about the March 2023 update.

March Events 2023A

March 2023 Events

Find out more about the online and in person events happening in March!

Top Kudoed Authors