Forum Discussion
Power Query group to show only MAX REF
Hi I have a table which contains mainly sinlge rows per unit reference. However some unit references have more than one row.
Each entry has a SAP Ref . So I would like a function in power query to only show the Max SAP Ref for each row. This will remove the duplicate unit references that I do not require.
These are the Unit reference that I am expecting to see only one result for any the max ax SAP Ref they are unit refereneces 26, 84 & 133
| Unit Reference | Survey Date | SAP Rating | SAP Ref | SAP_Grouping | SAP_Grouping2 |
| 18 | 01/04/2010 | 55 | 1 | D | Cloned Apr 10 |
| 26 | 01/04/2010 | 54 | 1 | E | Cloned Apr 10 |
| 26 | 01/04/2010 | 60 | 3 | D | Cloned Apr 10 |
| 34 | 27/05/2022 | 69 | 3 | C | Current |
| 42 | 01/04/2010 | 67 | 1 | D | Cloned Apr 10 |
| 50 | 27/07/2021 | 65 | 2 | D | Current |
| 76 | 01/04/2010 | 59 | 1 | D | Cloned Apr 10 |
| 84 | 29/04/2009 | 74 | 1 | C | Expired |
| 84 | 29/04/2009 | 80 | 4 | C | Expired |
| 92 | 01/04/2010 | 67 | 1 | D | Cloned Apr 10 |
| 109 | 01/04/2010 | 58 | 1 | D | Cloned Apr 10 |
| 117 | 18/09/2015 | 61 | 2 | D | Current |
| 125 | 27/03/2018 | 69 | 2 | C | Current |
| 133 | 15/04/2021 | 73 | 2 | C | Current |
| 133 | 15/04/2021 | 99 | 3 | C | Current |
| 141 | 01/04/2010 | 61 | 1 | D | Cloned Apr 10 |
| 159 | 01/08/2022 | 74 | 3 | C | Current |
thank you
Richard
Hi Richard,
Try this example query:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("lVLLCoMwEPwVyVkwu3kfi/UrxJseCsVKaKGf32w00BIj9rABycxkdsa+Z2BZzTg0XDbIgYcPpcIBYa5h2vtjnsbqsvgqXA51z1BnBLkRupMETYcoviBID03DVSAgEsFthJbm5f00PyNUYqZtDt0rvmkb0iacpnUxEb60Tb6oO9S20bdbCZywJiVDvrv3cvPTWIBaekDuQN2/K0LU+zVujxkQFW3DHTEoEA2FVADVlqAgrE3t4E47IKgzUKuTmLYR57Gu1DpIyDKB4w1VysSmfyp2k6kPHw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Unit Reference" = _t, #"Survey Date" = _t, #"SAP Rating" = _t, #"SAP Ref" = _t, SAP_Grouping = _t, SAP_Grouping2 = _t]), chgTypes = Table.TransformColumnTypes(Source,{{"Unit Reference", Int64.Type}, {"Survey Date", type date}, {"SAP Rating", Int64.Type}, {"SAP Ref", Int64.Type}, {"SAP_Grouping", type text}, {"SAP_Grouping2", type text}}), groupUnitRef = Table.Group(chgTypes, {"Unit Reference"}, {{"data", each _, type table [Unit Reference=nullable number, Survey Date=nullable date, SAP Rating=nullable number, SAP Ref=nullable number, SAP_Grouping=nullable text, SAP_Grouping2=nullable text]}}), addMaxSAPRecord = Table.AddColumn(groupUnitRef, "maxSAP", each Table.Max([data], "SAP Ref")), expandMaxSAPRecord = Table.ExpandRecordColumn(addMaxSAPRecord, "maxSAP", {"Survey Date", "SAP Rating", "SAP Ref", "SAP_Grouping", "SAP_Grouping2"}, {"Survey Date", "SAP Rating", "SAP Ref", "SAP_Grouping", "SAP_Grouping2"}), removeDataCol = Table.RemoveColumns(expandMaxSAPRecord,{"data"}) in removeDataColSummary:
-1- Group By [Unit Reference] and change default aggregated column from Count to 'All Rows'.
-2- Add a new column using Table.Max to pick out only records that have the MAX value of your chosen column.
-3- Expand the new nested records column to reinstate all original columns.
Pete
3 Replies
Hi Richard,
Try this example query:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("lVLLCoMwEPwVyVkwu3kfi/UrxJseCsVKaKGf32w00BIj9rABycxkdsa+Z2BZzTg0XDbIgYcPpcIBYa5h2vtjnsbqsvgqXA51z1BnBLkRupMETYcoviBID03DVSAgEsFthJbm5f00PyNUYqZtDt0rvmkb0iacpnUxEb60Tb6oO9S20bdbCZywJiVDvrv3cvPTWIBaekDuQN2/K0LU+zVujxkQFW3DHTEoEA2FVADVlqAgrE3t4E47IKgzUKuTmLYR57Gu1DpIyDKB4w1VysSmfyp2k6kPHw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Unit Reference" = _t, #"Survey Date" = _t, #"SAP Rating" = _t, #"SAP Ref" = _t, SAP_Grouping = _t, SAP_Grouping2 = _t]), chgTypes = Table.TransformColumnTypes(Source,{{"Unit Reference", Int64.Type}, {"Survey Date", type date}, {"SAP Rating", Int64.Type}, {"SAP Ref", Int64.Type}, {"SAP_Grouping", type text}, {"SAP_Grouping2", type text}}), groupUnitRef = Table.Group(chgTypes, {"Unit Reference"}, {{"data", each _, type table [Unit Reference=nullable number, Survey Date=nullable date, SAP Rating=nullable number, SAP Ref=nullable number, SAP_Grouping=nullable text, SAP_Grouping2=nullable text]}}), addMaxSAPRecord = Table.AddColumn(groupUnitRef, "maxSAP", each Table.Max([data], "SAP Ref")), expandMaxSAPRecord = Table.ExpandRecordColumn(addMaxSAPRecord, "maxSAP", {"Survey Date", "SAP Rating", "SAP Ref", "SAP_Grouping", "SAP_Grouping2"}, {"Survey Date", "SAP Rating", "SAP Ref", "SAP_Grouping", "SAP_Grouping2"}), removeDataCol = Table.RemoveColumns(expandMaxSAPRecord,{"data"}) in removeDataColSummary:
-1- Group By [Unit Reference] and change default aggregated column from Count to 'All Rows'.
-2- Add a new column using Table.Max to pick out only records that have the MAX value of your chosen column.
-3- Expand the new nested records column to reinstate all original columns.
Pete
Thank you for your quick reponse . Works fine
You can use the Table.Group function Table.Group - PowerQuery M | Microsoft Learn
Should be something like this, but you should check what you need to keep.
Table.Group( #table/step, {"Unit Reference", "Survey Date", "SAP_Grouping2"}, {"MaxRef", each List.Max ([SAP])} )
