Forum Discussion
Solved
retrieve Max value based on other columns
Hi Everybody,
I have a table "MainTable" as follow:
| ID | complete | Name | from | to |
| 88 | 27/06/2019 | Elly | 5/09/2019 | 12/10/2019 |
| 88 | 27/06/2019 | Ann | 4/01/2019 | 10/10/2019 |
| 88 | 27/06/2019 | Lary | 1/07/2017 | 10/10/2019 |
| 22 | 27/06/2019 | Joe | 5/09/2019 | 12/10/2019 |
| 22 | 27/06/2019 | Henry | 4/01/2019 | 10/06/2019 |
| 22 | 27/06/2019 | Adam | 1/07/2017 | 10/10/2019 |
| 53 | 1/07/2019 | Mary | 5/01/2019 | 12/10/2019 |
| 53 | 1/07/2019 | Ferry | 4/01/2019 | 10/10/2019 |
| 53 | 1/07/2019 | Lucy | 1/07/2017 | 10/10/2019 |
Each ID has a single Complete Date, but has different name with different From & To dates.
What I want to extract from this table is: Getting the maximum From date for each ID where the Complete date is between From & To date. So the result table should be something like that:
| ID | Complete | Name | from | to |
| 88 | 27/06/2019 | Ann | 4/01/2019 | 10/10/2019 |
| 22 | 27/06/2019 | Adam | 1/07/2017 | 10/10/2019 |
| 53 | 1/07/2019 | Mary | 5/01/2019 | 12/10/2019 |
I would be glad if anyone can help me deal with the situation whether using DAX or Power querry.
Cheers,
Hi SadStatue
Try this M code.
Please see attached file as well
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WsrBQ0lEKySgtKk5JrNRR8CrNS1UwMtdRMDIwtATKuObkVKIoCE4tKEnNTUotUjCFKwpOLCktAsv6J5fkg+QMjaCSsToErXDMywOSbkWZENnEvNLEokoFE7g8QifcdAPiTfcBGobiRK/SnEoFQ7C8OWHTjYzwmu6Vn0pp6BCwwSM1D+wB3OHjm5+H0EmK4x1TEnMpCBpTYxTL4VrBbsIIdaiziQ4WfKa7pRYRCBMKnO5Tmkx2gokFAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ID = _t, complete = _t, Name = _t, from = _t, to = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"complete", type date}, {"Name", type text}, {"from", type date}, {"to", type date}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"ID", "complete"}, {{"AllRows", each _, type table [ID=number, complete=date, Name=text, from=date, to=date]}}), #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.Max( Table.SelectRows([AllRows],(x)=>[complete]>=x[from] and [complete]<=x[to]), "from")), #"Expanded Custom" = Table.ExpandRecordColumn(#"Added Custom", "Custom", {"Name", "from", "to"}, {"Name", "from", "to"}), #"Removed Columns" = Table.RemoveColumns(#"Expanded Custom",{"AllRows"}) in #"Removed Columns"
2 Replies
Hi SadStatue
Try this M code.
Please see attached file as well
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WsrBQ0lEKySgtKk5JrNRR8CrNS1UwMtdRMDIwtATKuObkVKIoCE4tKEnNTUotUjCFKwpOLCktAsv6J5fkg+QMjaCSsToErXDMywOSbkWZENnEvNLEokoFE7g8QifcdAPiTfcBGobiRK/SnEoFQ7C8OWHTjYzwmu6Vn0pp6BCwwSM1D+wB3OHjm5+H0EmK4x1TEnMpCBpTYxTL4VrBbsIIdaiziQ4WfKa7pRYRCBMKnO5Tmkx2gokFAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ID = _t, complete = _t, Name = _t, from = _t, to = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"complete", type date}, {"Name", type text}, {"from", type date}, {"to", type date}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"ID", "complete"}, {{"AllRows", each _, type table [ID=number, complete=date, Name=text, from=date, to=date]}}), #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.Max( Table.SelectRows([AllRows],(x)=>[complete]>=x[from] and [complete]<=x[to]), "from")), #"Expanded Custom" = Table.ExpandRecordColumn(#"Added Custom", "Custom", {"Name", "from", "to"}, {"Name", "from", "to"}), #"Removed Columns" = Table.RemoveColumns(#"Expanded Custom",{"AllRows"}) in #"Removed Columns"Hi Zubair,
Thank you so much for your response. It is working as I wanted.
