Forum Discussion
Latest Record in Group via Query Editor
We are trying to get the latest record/row in a group via Query Editor. After we expand the group and we filter on the latest record, it filters the entire dataset and not within the group. Please see below.
let
Data = Source{[Name="Data"]}[Data],
dbo_PercentOfProceeds = Data{[Schema="dbo",Item="PercentOfProceeds"]}[Data],
#"Grouped Rows" = Table.Group(#"Removed Duplicates", {"settleID", "productionDate"}, {{"SettleID-Statement_Group", each _, type table}}),
#"Expanded SettleID-Statement_Group" = Table.ExpandTableColumn(#"Grouped Rows", "SettleID-Statement_Group", {"fileAndPage", "statementDate"}, {"SettleID-Statement_Group.fileAndPage", "SettleID-Statement_Group.statementDate"}),
#"Latest Records" = Table.SelectRows(#"Expanded SettleID-Statement_Group", let latest = List.Max(#"Expanded SettleID-Statement_Group"[#"SettleID-Statement_Group.statementDate"]) in each [#"SettleID-Statement_Group.statementDate"] = latest)
in
#"Latest Records"
Any suggestions?
Thanks for your help!
You should select them before you expand the group. So try modifying your group-command like this:
#"Grouped Rows" = Table.Group(#"Removed Duplicates", {"settleID", "productionDate"}, {{"SettleID-Statement_Group", each _, type table}, {"Max", each List.Max([statementDate])} }),
Somehow this post was duplicated, I provided a similar, but slightly different answer in the other version: https://community.powerbi.com/t5/Desktop/Latest-Record-in-Group-via-Query-Editor/td-p/225190
Especially for ImkeF refering to our discussion about "types" elsewhere (but also interesting for others :-) ):
Table.Group with option "All Rows" generates code with "type table", which means that all columns in the nested tables have data type any. This is a pity (or a shame?), because such tables have the same structure as the original table, so e.g. in this example: if type table is replaced with Value.Type(#"Removed Duplicates"), then the nested tables have the same type as the original table and the columns will be expanded with the correct data type instead of any..
This would also be an additional suggestion for the solution I provided in the other post I linked above.
2 Replies
You should select them before you expand the group. So try modifying your group-command like this:
#"Grouped Rows" = Table.Group(#"Removed Duplicates", {"settleID", "productionDate"}, {{"SettleID-Statement_Group", each _, type table}, {"Max", each List.Max([statementDate])} }),
Somehow this post was duplicated, I provided a similar, but slightly different answer in the other version: https://community.powerbi.com/t5/Desktop/Latest-Record-in-Group-via-Query-Editor/td-p/225190
Especially for ImkeF refering to our discussion about "types" elsewhere (but also interesting for others :-) ):
Table.Group with option "All Rows" generates code with "type table", which means that all columns in the nested tables have data type any. This is a pity (or a shame?), because such tables have the same structure as the original table, so e.g. in this example: if type table is replaced with Value.Type(#"Removed Duplicates"), then the nested tables have the same type as the original table and the columns will be expanded with the correct data type instead of any..
This would also be an additional suggestion for the solution I provided in the other post I linked above.