Forum Discussion
Weight based on multiple GROUPBY conditions
Hi Everyone, This is my first community post, I apolgise in advance if the structure of the question is unclear (I have read throught the guidelines of asking questions so will try to stick them ...
Hi Anonymous ,
Pste this into a new blank query using Advanced Editor:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTI21Dc00jcyMDIEcsLCwoCkoZGpUqwOFumIiAggaY4ka6BvYImm2cAAuzREM0zaCd3syMhIIGmGQzYqKgpIGiHJIhsN0WuBQxahNxYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [fund = _t, quarter = _t, security = _t, dollar_position = _t]), chgTypes = Table.TransformColumnTypes(Source,{{"fund", type text}, {"quarter", type date}, {"security", type text}, {"dollar_position", Int64.Type}}), groupRows = Table.Group(chgTypes, {"fund", "quarter"}, {{"data", each _, type table [fund=nullable text, quarter=nullable date, security=nullable text, dollar_position=nullable number]}, {"dollar_posn_sum", each List.Sum([dollar_position]), type nullable number}}), expandData = Table.ExpandTableColumn(groupRows, "data", {"security", "dollar_position"}), addWeight = Table.AddColumn(expandData, "weight", each [dollar_position] / [dollar_posn_sum]) in addWeightThis gives me the following output:
Pete
Hi Anonymous ,
Pste this into a new blank query using Advanced Editor:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTI21Dc00jcyMDIEcsLCwoCkoZGpUqwOFumIiAggaY4ka6BvYImm2cAAuzREM0zaCd3syMhIIGmGQzYqKgpIGiHJIhsN0WuBQxahNxYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [fund = _t, quarter = _t, security = _t, dollar_position = _t]),
chgTypes = Table.TransformColumnTypes(Source,{{"fund", type text}, {"quarter", type date}, {"security", type text}, {"dollar_position", Int64.Type}}),
groupRows = Table.Group(chgTypes, {"fund", "quarter"}, {{"data", each _, type table [fund=nullable text, quarter=nullable date, security=nullable text, dollar_position=nullable number]}, {"dollar_posn_sum", each List.Sum([dollar_position]), type nullable number}}),
expandData = Table.ExpandTableColumn(groupRows, "data", {"security", "dollar_position"}),
addWeight = Table.AddColumn(expandData, "weight", each [dollar_position] / [dollar_posn_sum])
in
addWeight
This gives me the following output:
Pete
Thank you very much BA_Pete ! This worked like a charm !
I'm just going to post my adapted Advanced Query for anyone struggling with the Source (It took me longer than expected although I'm a complete newbie to PowerBI)
Perhaps its worth including in your answer ?
Kindest,
JC
let
Source = Excel.Workbook(
File.Contents("C:\Users\..\Downloads\power_bi_question_sample_data.xlsx"),
null,
true
){[Item = "Sheet2", Kind = "Sheet"]}[Data],
#"Promote Header" = Table.PromoteHeaders(Source, [PromoteAllScalars = true]),
#"Transform Column" = Table.TransformColumnTypes(
#"Promote Header",
{
{"fund", type text},
{"quarter_end_date", type date},
{"security", type text},
{"dollar_position", Int64.Type}
}
),
#"Group Rows" = Table.Group(
#"Transform Column",
{"fund", "quarter_end_date"},
{
{
"data",
each _,
type table [
fund = nullable text,
quarter = nullable date,
security = nullable text,
dollar_position = nullable number
]
},
{"dollar_posn_sum", each List.Sum([dollar_position]), type nullable number}
}
),
#"Expand Data" = Table.ExpandTableColumn(#"Group Rows", "data", {"security", "dollar_position"}),
#"Add Column" = Table.AddColumn(
#"Expand Data",
"weight",
each [dollar_position] / [dollar_posn_sum]
),
#"Removed Columns" = Table.RemoveColumns(#"Add Column", {"dollar_posn_sum"})
in
#"Removed Columns"@