Groupby Maximum in Transform Data

[Anonymous]

Hi, 

I'd like to create a database, using the transform data query editor, with one row grouped and summed by [AFE], [Major], [Minor], [Cost] when [GL_YEAR] is a MAXIMUM. This is because there is a potentially an afe, major minor and cost for each year, but I only want the most recent year. 

I'm able to use the groupby function in the Transform data to group and sum, however, like I mentioned, I only want to group and sum if the [GL_YEAR] is a maximum. I'm assuming we will split the [GL_YEAR] to remove the first two characters and turn the column into a number type, although I'm not sure how to work the maximum code since the groupby feature I'm using is a "gui". The first table below is a sample of my data, the following table is the result I'm pursuing:

Thanks again for your efforts on the forum, the community has helped me many times! 

AFE	Major	Minor	Cost	GL_YEAR
10	1	300	10	YR19
10	1	300	20	YR20
10	1	300	15	YR20
10	1	400	10	YR19
10	1	400	5	YR20
20	1	300	20	YR19
20	1	300	10	YR20
20	1	400	20	YR20
20	2	400	10	YR19
20	2	400	20	YR19
AFE	Major	Minor	Cost	GL_YEAR MAX
10	1	300	35	YR20
10	1	400	5	YR20
20	1	300	10	YR20
20	1	400	20	YR20
20	2	400	30	YR19

[HotChilli · Accepted solution]

I split the column, did a groupBy to get Max Year, (maintaining original data).

Then Expand the column and select the rows that match Max Year.

Then another GroupBy to get Total Cost

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjRQ0lEyBGJjAzALREQGGVoqxepgSBpBJIEUFklDU6ySJviMhUiiaDTCaiVUoxFWx6LrNMHiWDDPCId70CSR7IwFAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [AFE = _t, Major = _t, Minor = _t, Cost = _t, GL_YEAR = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"AFE", Int64.Type}, {"Major", Int64.Type}, {"Minor", Int64.Type}, {"Cost", Int64.Type}, {"GL_YEAR", type text}}),
    #"Split Column by Positions" = Table.SplitColumn(#"Changed Type", "GL_YEAR", Splitter.SplitTextByPositions({2}), {"GL_YEAR.1"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Positions",{{"GL_YEAR.1", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type1", {"AFE", "Major", "Minor"}, {{"MaxYear", each List.Max([GL_YEAR.1]), type number}, {"all", each _, type table [AFE=number, Major=number, Minor=number, Cost=number, GL_YEAR.1=number]}}),
    #"Expanded all" = Table.ExpandTableColumn(#"Grouped Rows", "all", {"Cost", "GL_YEAR.1"}, {"all.Cost", "all.GL_YEAR.1"}),
    Custom1 = Table.SelectRows(#"Expanded all", each ( [MaxYear] = [all.GL_YEAR.1])),
    #"Removed Columns" = Table.RemoveColumns(Custom1,{"all.GL_YEAR.1"}),
    #"Grouped Rows1" = Table.Group(#"Removed Columns", {"AFE", "Major", "Minor", "MaxYear"}, {{"TotCost", each List.Sum([all.Cost]), type number}})
in
    #"Grouped Rows1"

Let me know how you get on