Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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 |
Solved! Go to 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".
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".