Power Query - Group count

[Brite]

I am currently trying to group Count ZP by ID. The payment frequency above should be overwriting the one below it. 

This is an example of what I have right now:

CountZP --> Monthly = 1, Quarterly = 3, Annually = 12 per count 1

I am trying to group it like this:

[Anonymous · Accepted solution]

Hi @Brite ,

I think you can copy and paste my code into Advance Editor in Power Query Editor to build a virtual table by "Group By" function and then merge it with your data table.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSi4tjjc0MjZR0lEyAGI3R59gVyAdkFiZm5pXouCcn1uQk1qSqgcUMzQ2ACkxBGLf/LySjJxKMC9WhyRTjC3hpgSWJhaVpBaBzTGGm2NibGRIuWuINcXUDG6MY15eaWIOxBwjuEFGhhQFTiwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, amount.refunded = _t, disputed = _t, outcome.seller_message = _t, Amount = _t, count = _t, #"Payment frequecy" = _t, #"Count ZP" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", type text}, {"amount.refunded", Int64.Type}, {"disputed", type logical}, {"outcome.seller_message", type text}, {"Amount", Int64.Type}, {"count", Int64.Type}, {"Payment frequecy", type text}, {"Count ZP", Int64.Type}}),
    #"Merged Queries" = 
    let  #"Grouped Rows" = Table.Group(#"Changed Type", {"ID"}, {{"Sum", each List.Sum([Count ZP]), type nullable number}}) in
    Table.NestedJoin(#"Changed Type", {"ID"}, #"Grouped Rows", {"ID"}, "Grouped Rows", JoinKind.LeftOuter),
    #"Expanded Group Rows" = Table.ExpandTableColumn(#"Merged Queries", "Grouped Rows", {"Sum"}, {"Grouped Rows.Sum"}),
    #"Filtered Rows" = Table.SelectRows(#"Expanded Group Rows", each ([Payment frequecy] = "Monthly")),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Count ZP"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Grouped Rows.Sum", "Count ZP"}})
in
    #"Renamed Columns"

Result is as below.

Best Regards,
Rico Zhou

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

[amitchandak]

@Brite , You can have two measure

Cnt = Sum(Table[CountZP])

Amount Measure = Divide( sum(Table[Amount]), [Cnt])

Rest you can take add group by

Or take all columns in other than CountZP and Amount in summarize and then use addcolumns and for these two as new table 

Addcolumns( Summarize(Table, Table[ID], Table[Disputed] ) , // Add others

"Cnt" , Sum(Table[CountZP]),

"Amount Measure", Divide( sum(Table[Amount]), Sum(Table[CountZP]) )

)

In power query use group by and add cound and amount and then add calculated column for final amount

https://docs.microsoft.com/en-us/power-query/group-by