Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hello,
As the title suggests, in Power Query, is there a way to identify the max date across multiple coloums but return the column name the max date is within, instead of the date? e.g
I'm happy finding the max date using List.Max, but i don't know how to return the colmun name.
If there are mutiple columns with the same date could column names be returned seperated by a comma?
Thanks in advance
DomDom
Solved! Go to Solution.
Find the positions of the maximal value and then take those positions from the list of column names.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTICYmMgNgFiUyA2UIrVwZQxBvNBMqZIIjA9JkqxsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t, Column5 = _t, Column6 = _t]),
#"Added Custom" = Table.AddColumn(
Source,
"Max",
each
let
row_values = Record.ToList(_),
column_names = Record.FieldNames(_),
max_value = List.Max(row_values),
max_positions = List.PositionOf(row_values, max_value, 6),
max_names = List.Transform(max_positions, each column_names{_}),
combine_names = Text.Combine(max_names, ", ")
in
combine_names,
type text
)
in
#"Added Custom"
If you don't like all the intermediate variables, you can put this in the custom column box:
Text.Combine(
List.Transform(
List.PositionOf(
Record.ToList(_),
List.Max(Record.ToList(_)),
Record.FieldCount(_)
),
(pos) => Record.FieldNames(_){pos}
),
", "
)
Thankyou AlexisOlson, it works perfectly.
Find the positions of the maximal value and then take those positions from the list of column names.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTICYmMgNgFiUyA2UIrVwZQxBvNBMqZIIjA9JkqxsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t, Column5 = _t, Column6 = _t]),
#"Added Custom" = Table.AddColumn(
Source,
"Max",
each
let
row_values = Record.ToList(_),
column_names = Record.FieldNames(_),
max_value = List.Max(row_values),
max_positions = List.PositionOf(row_values, max_value, 6),
max_names = List.Transform(max_positions, each column_names{_}),
combine_names = Text.Combine(max_names, ", ")
in
combine_names,
type text
)
in
#"Added Custom"
If you don't like all the intermediate variables, you can put this in the custom column box:
Text.Combine(
List.Transform(
List.PositionOf(
Record.ToList(_),
List.Max(Record.ToList(_)),
Record.FieldCount(_)
),
(pos) => Record.FieldNames(_){pos}
),
", "
)
Thank you for your solution! May I ask how to limit the selection to about 5 (out of 20) columns in my table only please? And if you know of a similar solution but with DAX?
Could you please explain how this works? I mean where are the column names specified in this code?
Each row is a record and Record.FieldNames is the list of column names for that row. The code finds the positions of the maximal values in the list of values and then applies those positions to the list of column names.