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
Exciting changes

Power BI Community Changes

Check out the changes to the Power BI Community announced at Build.

May 2023 update

Power BI May 2023 Update

Find out more about the May 2023 update.

Kudo Data Story carousel

Data Stories Gallery

Visit our Data Stories Gallery and give kudos to your favorite Data Stories.

Top Solution Authors
Top Kudoed Authors