Forum Discussion
Dynamic Max of each column Grouped by ID
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 |
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
2 Replies
jgordon11Resolver II
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