Sum without using group by in advance power query editor

Question

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.&nbsp;NameFruitFruits Received&nbsp;MikeApple&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;0MikeBanana&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;0MikeMango&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;0JackApple&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;0JackBanana&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;5JackMango&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;2CodyApple&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;0CodyBanana&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;0CodyMango&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;0JamieApple&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;3JamieBanana&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;0JamieMango&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;1&nbsp;Using power query editor in Power BI, how can I solve this problem. Final table output is given below.NameFruitFruits Received&nbsp;Adjusted value flagMikeApple&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; XMikeBanana&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;0&nbsp;MikeMango&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;0&nbsp;JackApple&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;0&nbsp;JackBanana&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;5&nbsp;JackMango&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;2&nbsp;CodyApple&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;0&nbsp;CodyBanana&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; XCodyMango&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;0&nbsp;JamieApple&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;3&nbsp;JamieBanana&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;0&nbsp;JamieMango&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;1&nbsp;

lbendlin · Accepted Answer

&nbsp;
Why the restriction on Group By?
&nbsp;
&nbsp;
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"
&nbsp;
&nbsp;
&nbsp;
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".

Forum Discussion

Sum without using group by in advance power query editor

1 Reply

Related content

Advanced Editor does not apear

Text size in Advanced Editor

Advanced Editor not in view tab

Advanced editor to change source

[Advanced Editor] Conditional Concatenate

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