Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
Brite
Helper I
Helper I

Power Query - Group count

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:

Brite_0-1643675393675.png

 

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

 

I am trying to group it like this:

Brite_1-1643675639975.png

 

 

1 ACCEPTED SOLUTION
v-rzhou-msft
Community Support
Community Support

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.

1.png

 

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.

 

View solution in original post

2 REPLIES 2
v-rzhou-msft
Community Support
Community Support

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.

1.png

 

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

@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

 

Full Power BI Video 20 Hours YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!