Forum Discussion
get column header for max column
I have below "DE MAX" column that gets the maxiumum value in the row. I'd like to be able to get the column name of the item where the max value was found. At times the max value may be shared between two values. See below Desired result column. Any help is appreciated. Id like to be able to do this in PQ.
| Store | DE/F1 Base | DE/F1 HTS | DE/F1 Patient Experience | DE Special Projects and Temp Support | DE MAX | DESIRED RESULT |
| 2492 | 1.3 | 0.15 | 1.45 | 1.3 | 1.45 | DE/F1 Patient Experience |
| 5103 | 1.3 | null | null | 1.3 | 1.3 | DE Special Projects and Temp Support; DE/F1 Base |
| 5388 | 1.3 | null | null | 1.3 | 1.3 | DE Special Projects and Temp Support; DE/F1 Base |
| 5444 | 1.3 | null | null | 1.3 | 1.3 | DE Special Projects and Temp Support; DE/F1 Base |
| 9313 | 1.3 | 0.15 | null | 1.3 | 1.3 | DE Special Projects and Temp Support; DE/F1 Base |
| 5184 | 1.3 | null | 0.6 | 1.3 | 1.3 | DE/F1 Patient Experience |
You can add custom column. I am not sure how the last one does not include base.
Text.Combine(List.RemoveItems(List.Transform(List.PositionOf(Record.ToList(_),[DE MAX], Occurrence.All), (x) => Record.FieldNames(_){x}), {"DE MAX"}),";")
2 Replies
You can add custom column. I am not sure how the last one does not include base.
Text.Combine(List.RemoveItems(List.Transform(List.PositionOf(Record.ToList(_),[DE MAX], Occurrence.All), (x) => Record.FieldNames(_){x}), {"DE MAX"}),";")
Thank you This worked, sorry that last row was a mistake on my part base would have been included
