Identify column using Max result

[Comfy]

Hi Boardies,

Here's a sample of my source data.

let
    Source = #table({"Invoice Ref", "Organistion Code", "Account", "High Level Cat", "Amount", "Abs"},{{"A12345","Sub1",88888,"I&E",120,120},{"A12345","Sub2",33333,"I&E",-100,100},{"A12346","Sub2",77777,"I&E",240,240},{"A12346","Sub3",33333,"I&E",-200,200}}),
    #"Grouped Rows" = Table.Group(Source, {"Invoice Ref", "Organistion Code", "High Level Cat"}, {{"Data", each _, type table}}),
    #"Pivoted Column" = Table.Pivot(#"Grouped Rows", List.Distinct(#"Grouped Rows"[#"Organistion Code"]), "Organistion Code", "Data")
in
    #"Pivoted Column"

I wish to add two additional columns (Account and Amount)

Account - will return the Account from the Sub1, Sub2, Sub3 columns where the Absolute amount in those tables is the maximum. So row 1 will return 88888 from [Sub1] and row 2 will return 77777 from [Sub2].
Amount - will return the sum of [Amount] in each of the <sub>tables. So Row 1 will return 20 and row 2 will return 40.

I cannot hardcode the column names in the solution as there are more and I want to make it flexible for when others are added or removed.
I can achieve this by using List.RemoveItems and Table.ColumnNames but am struggling with how to use that for the two columns above.</sub>

 

[Comfy]

Well I managed to rehash some code from elsewhere in the project.

It's not pretty but I get the results I need:

let
    Source = #table({"Invoice Ref", "Organistion Code", "Account", "High Level Cat", "Amount", "Abs"},{{"A12345","Sub1",88888,"I&E",120,120},{"A12345","Sub2",33333,"I&E",-100,100},{"A12346","Sub1",77777,"I&E",240,240},{"A12346","Sub3",33333,"I&E",-200,200}}),
    #"Grouped Rows" = Table.Group(Source, {"Invoice Ref", "Organistion Code", "High Level Cat"}, {{"Data", each _, type table}}),
    #"Pivoted Column" = Table.Pivot(#"Grouped Rows", List.Distinct(#"Grouped Rows"[#"Organistion Code"]), "Organistion Code", "Data"),
    AddDiff = Table.AddColumn(#"Pivoted Column", "Correction", each List.Sum(Table.ExpandTableColumn(Table.SelectRows(Record.ToTable(Record.RemoveFields(_, {"Invoice Ref", "High Level Cat"})), each [Value] <> null), "Value", {"Amount"}, {"Amount"})[Amount]) *- 1, Currency.Type),
    IDSub = Table.AddColumn(AddDiff, "GroupedSubs", each Table.ExpandTableColumn(Table.SelectRows(Record.ToTable(Record.RemoveFields(_, {"Invoice Ref", "High Level Cat", "Correction"})), each [Value] <> null), "Value", {"Account", "Abs"}, {"Account", "Abs"}), type table),
    AddMax = Table.AddColumn(IDSub, "Max", each List.Max(_[GroupedSubs][Abs])),
    AddAccount = Table.AddColumn(AddMax, "Account", each List.First(Table.SelectRows([GroupedSubs], (Magic) =>[Max]=Magic[Abs])[Account])),
    AddSub = Table.AddColumn(AddAccount, "Name", each List.First(Table.SelectRows([GroupedSubs], (Magic) =>[Max]=Magic[Abs])[Name])),
    #"Removed Other Columns" = Table.SelectColumns(AddSub,{"Name", "Account", "Correction"})
in
    #"Removed Other Columns"