Summing values by category in "transform data" to create query capable of one to many relationship

[Anonymous]

Hi, 

I have a query with multiple AFE numbers. Within each afe number are a couple of major codes and minor codes. My end goal is to sum the costs associated with each AFE's major and minor cost so that I can have 1 row per AFE, major and minor code. Once the query is in this format, I will be able to create a one to many relationship with another query to transfer these values using the related function. I believe this has to be done in the "transform data" query editor otherwise I'll end up with a many to many relationship correct? 

The first table is an example of what my data looks like. The second table is the end result I'm shooting for. 

Thank you in advance!

AFE	Major	Minor	Cost
10	1	300	10
10	1	300	20
10	1	400	10
10	1	400	5
20	1	300	20
20	1	300	10
20	1	400	20
20	2	400	10
20	2	400	20

AFE	Major	Minor	Cost
10	1	300	30
10	1	400	15
20	1	300	30
20	1	400	20
20	2	400	30

[Greg_Deckler · Accepted solution]

Just use the Group By feature of the Query Editor.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjRQ0lEyBGJjAzDLQClWB0PUCE3UBKtaiKgpWNAIqwFGWC0zQjUAWa0RumVooiC1sQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [AFE = _t, Major = _t, Minor = _t, Cost = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"AFE", Int64.Type}, {"Major", Int64.Type}, {"Minor", Int64.Type}, {"Cost", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"AFE", "Major", "Minor"}, {{"Cost", each List.Sum([Cost]), type number}})
in
    #"Grouped Rows"