Forum Discussion
Maximum of row with dynamic columns
Hi helpers
I have been searching a few hours for this issue but cannot work it out. I have data like so
I want to add a maximum column to get the highest value from these columns. Like so
= Table.AddColumn(#"Pivoted Column", "Maximum", each List.Max({[#"2019-9"], [#"2019-8"], [#"2019-7"], [#"2019-6"], [#"2019-5"], [#"2019-4"], [#"2019-3"], [#"2019-2"]}), type number)
My data however is dynamic and so the column names will change and may increase/decrease in count.
I thought I could simply create a list and refer to that list like so
= Table.AddColumn(#"Pivoted Column", "Maximum", each List.Max(LISTNAME), type number)
but this does not work. All that does is give me the list names
Hi Anonymous ,
You can added an index to this table and create a column like:
let _index = [Index] in List.Max(Table.Transpose(Table.RemoveColumns(Table.SelectRows(#"Added Index", each [Index] = _index), {"Index"}))[Column1])Also, you can exclude the columns using the Table.RemoveColumns and leave only those you want to compare.
Ricardo
4 Replies
camargos88Community Champion
Hi Anonymous ,
You can added an index to this table and create a column like:
let _index = [Index] in List.Max(Table.Transpose(Table.RemoveColumns(Table.SelectRows(#"Added Index", each [Index] = _index), {"Index"}))[Column1])Also, you can exclude the columns using the Table.RemoveColumns and leave only those you want to compare.
Ricardo
- Greg_Deckler
Anonymous - With column names changing, that could prove difficult in Power Query. ImkeF or edhans may be of assistance. However, when you have that kind of data layout you generally want to unpivot those columns so that you end up with 2 columns. That's generally a recipe for success. Then, if you want the maximum, it pretty much becomes dirt simple.
