Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Trying to determine how to return only the rows where the value for that ID number is a maximum. Example:
Row | ID | Value |
1 | 1 | 3 |
2 | 1 | 7 |
3 | 1 | 5 |
4 | 2 | 21 |
5 | 2 | 12 |
6 | 3 | 17 |
7 | 3 | 9 |
8 | 3 | 3 |
9 | 3 | 32 |
10 | 4 | 4 |
11 | 4 | 9 |
In this case, I'd be looking for only rows 2, 4, 9, and 11 to remain when the query is applied. Any thoughts on how to accomplish would be appreciated. Thanks.
Solved! Go to Solution.
Use this forumula:
let
varID = [ID],
varValue = [Value]
in
Table.Max(
Table.SelectRows(#"Changed Type", each [ID] = varID),
"Value"
)[Value] = varValue
It will return this table:
Then just filter on the true/false to keep what you want. Full M code below:
1) In Power Query, select New Source, then Blank Query
2) On the Home ribbon, select "Advanced Editor" button
3) Remove everything you see, then paste the M code I've given you in that box.
4) Press Done
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Nc3BDcAwCAPAXXjnUZOkaWZB2X+NUuM+kHxChgiDNU6308JcWlSXJjUyfXsHOUU4efNGsqpL3NQj1ZP9q5q4Mg8OCTGr5wU=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Row = _t, ID = _t, Value = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Row", Int64.Type}, {"ID", Int64.Type}, {"Value", Int64.Type}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each let
varID = [ID],
varValue = [Value]
in
Table.Max(
Table.SelectRows(#"Changed Type", each [ID] = varID),
"Value"
)[Value] = varValue)
in
#"Added Custom"
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingUse this forumula:
let
varID = [ID],
varValue = [Value]
in
Table.Max(
Table.SelectRows(#"Changed Type", each [ID] = varID),
"Value"
)[Value] = varValue
It will return this table:
Then just filter on the true/false to keep what you want. Full M code below:
1) In Power Query, select New Source, then Blank Query
2) On the Home ribbon, select "Advanced Editor" button
3) Remove everything you see, then paste the M code I've given you in that box.
4) Press Done
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Nc3BDcAwCAPAXXjnUZOkaWZB2X+NUuM+kHxChgiDNU6308JcWlSXJjUyfXsHOUU4efNGsqpL3NQj1ZP9q5q4Mg8OCTGr5wU=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Row = _t, ID = _t, Value = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Row", Int64.Type}, {"ID", Int64.Type}, {"Value", Int64.Type}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each let
varID = [ID],
varValue = [Value]
in
Table.Max(
Table.SelectRows(#"Changed Type", each [ID] = varID),
"Value"
)[Value] = varValue)
in
#"Added Custom"
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingWith a little tweaking to the process of getting it there, this worked. Thanks!
Glad I was able to help @vha_labo
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI Reporting