Calculated column with the max per group

[Berl21]

I need to add a column in my query that would show the max of an index column per group. 

This is how it currently looks like into the tool:

In this case, there are two groups, the first one having a max of 7 and the second a max of 8. I need to create a column showing 8 for all of the rows of the first group and 7 for all of the rows of the second group.

I am still new at Power BI and not sure how to do this. Help will be much appreciated!

[Berl21]

@Vijay_A_Verma The test looks good, but I honestly have no idea how to modify my code. All my attempts return a mistake. Do you have any more precise information on how / where to copy paste the relevant lines?

This is what I currently have:

let
Source = GoogleSheets.Contents("MYURL"),
Rechenblatt_Table = Source{[name="Rechenblatt",ItemKind="Table"]}[Data],
#"Removed Columns" = Table.RemoveColumns(Rechenblatt_Table,{"Column2", "Column12", "Column11", "Column13", "Column14", "Column15", "Column16", "Column17", "Column18", "Column19", "Column20", "Column21", "Column23", "Column24"}),
#"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Column1", "Anfragenummer"}, {"Column3", "Opportunity-Name"}, {"Column4", "Erstelldatum"}, {"Column5", "Letzte Änderung"}, {"Column6", "Bis Phase"}, {"Column7", "Umsatz"}, {"Column8", "Art der Förderung"}, {"Column9", "Abrechenbare Änderung"}, {"Column10", "Antragsteller"}, {"Column22", "Revenue abzgl."}}),
#"Removed Columns1" = Table.RemoveColumns(#"Renamed Columns",{"Revenue abzgl."}),
#"Removed Top Rows" = Table.Skip(#"Removed Columns1",1),
#"Changed Type" = Table.TransformColumnTypes(#"Removed Top Rows",{{"Umsatz", Currency.Type}, {"Erstelldatum", type date}, {"Letzte Änderung", type date}}),
#"Filtered Rows" = Table.SelectRows(#"Changed Type", each [Anfragenummer] <> null and [Anfragenummer] <> ""),
#"Grouped Rows" = Table.Group(#"Filtered Rows", {"Anfragenummer"}, {{"ALL", each _, type table [Anfragenummer=text, #"Opportunity-Name"=text, Erstelldatum=nullable date, Letzte Änderung=nullable date, Bis Phase=text, Umsatz=nullable number, Art der Förderung=text, Abrechenbare Änderung=any, Antragsteller=text]}}),
#"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([ALL],"Rank",1)),
#"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Anfragenummer", "Opportunity-Name", "Erstelldatum", "Letzte Änderung", "Bis Phase", "Umsatz", "Art der Förderung", "Abrechenbare Änderung", "Antragsteller", "Rank"}, {"Custom.Anfragenummer", "Custom.Opportunity-Name", "Custom.Erstelldatum", "Custom.Letzte Änderung", "Custom.Bis Phase", "Custom.Umsatz", "Custom.Art der Förderung", "Custom.Abrechenbare Änderung", "Custom.Antragsteller", "Custom.Rank"}),
#"Removed Columns2" = Table.RemoveColumns(#"Expanded Custom",{"ALL", "Anfragenummer"}),
#"Renamed Columns1" = Table.RenameColumns(#"Removed Columns2",{{"Custom.Anfragenummer", "Anfragenummer"}, {"Custom.Opportunity-Name", "Opportunity-Name"}, {"Custom.Erstelldatum", "Erstelldatum"}, {"Custom.Letzte Änderung", "Letzte Änderung"}, {"Custom.Bis Phase", "Bis Phase"}, {"Custom.Umsatz", "Umsatz"}, {"Custom.Art der Förderung", "Art der Förderung"}, {"Custom.Abrechenbare Änderung", "Abrechenbare Änderung"}, {"Custom.Antragsteller", "Antragsteller"}, {"Custom.Rank", "Rank"}}),
#"Changed Type1" = Table.TransformColumnTypes(#"Renamed Columns1",{{"Letzte Änderung", type date}}),
#"Removed Columns3" = Table.RemoveColumns(#"Changed Type1",{"Opportunity-Name"}),
#"Changed Type2" = Table.TransformColumnTypes(#"Removed Columns3",{{"Erstelldatum", type date}, {"Umsatz", Currency.Type}}),
#"Replaced Value" = Table.ReplaceValue(#"Changed Type2","",0,Replacer.ReplaceValue,{"Abrechenbare Änderung"}),
#"Filtered Rows1" = Table.SelectRows(#"Replaced Value", each ([Anfragenummer] = "0000321844")),
#"Merged Queries" = Table.NestedJoin(#"Filtered Rows1", {"Bis Phase"}, #"Raten per Stage", {"Stage"}, "Raten per Stage", JoinKind.Inner),
#"Expanded Raten per Stage" = Table.ExpandTableColumn(#"Merged Queries", "Raten per Stage", {"Rate"}, {"Raten per Stage.Rate"})
in
#"Expanded Raten per Stage"

[Vijay_A_Verma]

if you can post a dummy file with few rows, then I can put the right code.

How to provide sample data

[Vijay_A_Verma]

See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test (later on when you use the query on your dataset, you will have to change the source appropriately. If you have columns other than these, then delete Changed type step and do a Changed type for complete table from UI again)

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Xc+xDQAhDATBXhyDZBsMfC2I/tv4k0g4pxPt7i2u7lVX1SFFTE55aIKcaYEa0wfqRKagYDLQYHLQZGqg9VBUtdQF8tQFaqkL1FMXKFJX3O3BNG/X+QE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Rank = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Rank", Int64.Type}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Added Index", "Highest Rank in a Group", each try if #"Added Index"[Rank]{[Index]+1}<[Rank] then [Rank] else null otherwise [Rank], Int64.Type),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Index"}),
    #"Filled Up" = Table.FillUp(#"Removed Columns",{"Highest Rank in a Group"})
in
    #"Filled Up"

👍 It's been a pleasure to help you | Help Hours: 11 AM to 9 PM (UTC+05:30)

How to get your questions answered quickly -- How to provide sample data