Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to Solution.
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])} }),
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
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.
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])} }),
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
97 | |
80 | |
69 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |