Sum without using group by in advance power query editor

[Rahulanwar92]

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. 

Name	Fruit	Fruits Received
Mike	Apple	0
Mike	Banana	0
Mike	Mango	0
Jack	Apple	0
Jack	Banana	5
Jack	Mango	2
Cody	Apple	0
Cody	Banana	0
Cody	Mango	0
Jamie	Apple	3
Jamie	Banana	0
Jamie	Mango	1

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

Name	Fruit	Fruits Received	Adjusted value flag
Mike	Apple	1	X
Mike	Banana	0
Mike	Mango	0
Jack	Apple	0
Jack	Banana	5
Jack	Mango	2
Cody	Apple	0
Cody	Banana	1	X
Cody	Mango	0
Jamie	Apple	3
Jamie	Banana	0
Jamie	Mango	1

[lbendlin · Accepted solution]

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".