Dynamic Max of each column Grouped by ID

[AyubSherif]

I have a step where I am retreiving the max of each column grouped by ID (#"Grouped Rows"). I want to convert this step to a dynamic function (dynamic list of columns) that takes all the columns in the table instead of hard coded names. Here is a sample code:

let
table = Table.FromRecords({
[SiteID = 1, Address = "123 street", City = "C", State = null],
[SiteID = 2, Address = null, City = "C", State = "O"],
[SiteID = 1, Address = null, City = "C", State = "O"],
[SiteID = 2, Address = "234 street", City = null, State = "O"]
}),
#"Changed Type" = Table.TransformColumnTypes(table,{{"SiteID", type text}, {"Address", type text}, {"City", type text}, {"State", type text}}),
/*#"Grouped Rows" = Table.Group(#"Changed Type", {"SiteID"}, {{"Adress", each List.Max([Address]), type nullable text}, {"City", each List.Max([City]), type nullable text}, {"State", each List.Max([State]), type nullable text}}),*/
columnNames = List.RemoveMatchingItems(Record.FieldNames(#"Changed Type"{0}), {"SiteID"}),
groupedTable = Table.Group(#"Changed Type", "SiteID", List.Transform(columnNames, each {_, List.Max}))
in
groupedTable

 My code generates a table of error:

Expression.Error: We cannot apply operator < to types Record and Record.
Details:
Operator=<
Left=
SiteID=1
Address=123 street
City=C
State=
Right=
SiteID=1
Address=123 street
City=C
State=

 The final objective of what I am trying to remove duplicate SiteIDs and fill all the columns with none null fields if found.

To go from this:

SiteID	Address	City	State
1	123 street	C	null
2	null	C	O
1	null	C	O
2	234 street	null	O

 To this:

SiteID	Adress	City	State
1	123 street	C	O
2	234 street	C	O

[AyubSherif]

Thank You ❤️❤️❤️❤️

[jgordon11 · Accepted solution]

let
    tbl = Table.FromRecords({
        [SiteID = 1, Address = "123 street", City = "C", State = null],
        [SiteID = 2, Address = null, City = "C", State = "O"],
        [SiteID = 1, Address = null, City = "C", State = "O"],
        [SiteID = 2, Address = "234 street", City = null, State = "O"]
        }),
    tcn = List.Skip(Table.ColumnNames(tbl)),
    tbl1 = Table.Group(tbl, {"SiteID"}, {{"All", each Table.SelectColumns(_, tcn)}}),
    tbl2 = Table.TransformColumns(tbl1, {{"All", (t)=> 
        let
            lst = Table.ToColumns(t),
            lst1 = List.Transform(lst, each List.Accumulate(_, "", (s,c)=> 
                    if c <> null then 
                        let ct = Text.From(c) 
                        in (if Text.Length(ct) > Text.Length(s) then ct else s)
                    else s))
        in Table.FromRows({lst1}, tcn)
    }}),
    Result = Table.ExpandTableColumn(tbl2, "All", tcn)
in
    Result