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

Calling all Data Engineers! Fabric Data Engineer (Exam DP-700) live sessions are back! Starting October 16th. Sign up.

Reply
Rahulanwar92
New Member

Sum without using group by in advance power query editor

I am trying to sum the "Fruits Received" column based on the "Name" column and check if the sum of those "Fruits Received" is 0. If the sum is 0 then I need to randomly give 1 Fruit to that person. 

NameFruitFruits Received 
MikeApple                       0
MikeBanana                       0
MikeMango                       0
JackApple                       0
JackBanana                       5
JackMango                       2
CodyApple                       0
CodyBanana                       0
CodyMango                       0
JamieApple                       3
JamieBanana                       0
JamieMango                       1

 

Using power query editor in Power BI, how can I solve this problem. Final table output is given below.

NameFruitFruits Received Adjusted value flag
MikeApple                     1              X
MikeBanana                     0 
MikeMango                     0 
JackApple                     0 
JackBanana                     5 
JackMango                     2 
CodyApple                     0 
CodyBanana                     1            X
CodyMango                     0 
JamieApple                     3 
JamieBanana                     0 
JamieMango                     1 
1 ACCEPTED SOLUTION
lbendlin
Super User
Super User

 

Why the restriction on Group By?

 

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8s3MTlXSUXIsKMgB0QrYgYFSrA5crVNiHhASqdg3MS89n5Bar8TkbGIdAVVLyBGmyIoJOMIIrNY5P6WSWEdA1RIXElDFRIZEbiYR8WGMopg4Z8BUE3CHoVJsLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, Fruit = _t, #"Fruits Received " = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Fruits Received ", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Name"}, {{"Rows", each _, type table [Name=nullable text, Fruit=nullable text, #"Fruits Received "=nullable number]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Random Fruit", each if List.Sum([Rows][#"Fruits Received "])=0 then [Rows][Fruit]{Number.Round(Number.RandomBetween(0,List.Count([Rows][Name])-1))} else null),
    #"Expanded Rows" = Table.ExpandTableColumn(#"Added Custom", "Rows", {"Fruit", "Fruits Received "}, {"Fruit", "Fruits Received "}),
    #"Added Custom1" = Table.AddColumn(#"Expanded Rows", "Adjusted value flag", each if [#"Fruits Received "]=0 and [Fruit]=[Random Fruit] then "X" else null,type text),
    #"Replaced Value" = Table.ReplaceValue(#"Added Custom1",each [#"Fruits Received "],each if [#"Adjusted value flag"]="X" then 1 else [#"Fruits Received "],Replacer.ReplaceValue,{"Fruits Received "}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Replaced Value",{{"Fruits Received ", Int64.Type}}),
    #"Removed Other Columns" = Table.SelectColumns(#"Changed Type2",{"Name", "Fruit", "Fruits Received ", "Adjusted value flag"})
in
    #"Removed Other Columns"

 

 

 

How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done".

View solution in original post

1 REPLY 1
lbendlin
Super User
Super User

 

Why the restriction on Group By?

 

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8s3MTlXSUXIsKMgB0QrYgYFSrA5crVNiHhASqdg3MS89n5Bar8TkbGIdAVVLyBGmyIoJOMIIrNY5P6WSWEdA1RIXElDFRIZEbiYR8WGMopg4Z8BUE3CHoVJsLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, Fruit = _t, #"Fruits Received " = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Fruits Received ", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Name"}, {{"Rows", each _, type table [Name=nullable text, Fruit=nullable text, #"Fruits Received "=nullable number]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Random Fruit", each if List.Sum([Rows][#"Fruits Received "])=0 then [Rows][Fruit]{Number.Round(Number.RandomBetween(0,List.Count([Rows][Name])-1))} else null),
    #"Expanded Rows" = Table.ExpandTableColumn(#"Added Custom", "Rows", {"Fruit", "Fruits Received "}, {"Fruit", "Fruits Received "}),
    #"Added Custom1" = Table.AddColumn(#"Expanded Rows", "Adjusted value flag", each if [#"Fruits Received "]=0 and [Fruit]=[Random Fruit] then "X" else null,type text),
    #"Replaced Value" = Table.ReplaceValue(#"Added Custom1",each [#"Fruits Received "],each if [#"Adjusted value flag"]="X" then 1 else [#"Fruits Received "],Replacer.ReplaceValue,{"Fruits Received "}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Replaced Value",{{"Fruits Received ", Int64.Type}}),
    #"Removed Other Columns" = Table.SelectColumns(#"Changed Type2",{"Name", "Fruit", "Fruits Received ", "Adjusted value flag"})
in
    #"Removed Other Columns"

 

 

 

How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done".

Helpful resources

Announcements
FabCon Global Hackathon Carousel

FabCon Global Hackathon

Join the Fabric FabCon Global Hackathon—running virtually through Nov 3. Open to all skill levels. $10,000 in prizes!

October Power BI Update Carousel

Power BI Monthly Update - October 2025

Check out the October 2025 Power BI update to learn about new features.

FabCon Atlanta 2026 carousel

FabCon Atlanta 2026

Join us at FabCon Atlanta, March 16-20, for the ultimate Fabric, Power BI, AI and SQL community-led event. Save $200 with code FABCOMM.

Top Kudoed Authors