Groupby Maximum in Transform Data

Question

Hi,&nbsp;&nbsp;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.&nbsp;&nbsp;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:&nbsp;Thanks again for your efforts on the forum, the community has helped me many times!&nbsp;&nbsp;AFEMajorMinorCostGL_YEAR10130010YR1910130020YR2010130015YR2010140010YR191014005YR2020130020YR1920130010YR2020140020YR2020240010YR1920240020YR19&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;AFEMajorMinorCostGL_YEAR MAX10130035YR201014005YR2020130010YR2020140020YR2020240030YR19&nbsp;

hotchilli · Accepted Answer

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
&nbsp;
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"
&nbsp;
Let me know how you get on

Forum Discussion

Groupby Maximum in Transform Data

1 Reply

Related content

Running Maximum

GroupBY and summarize

Table Transformation

Transform columns / type

Data transform using power query

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