Maximum of row with dynamic columns

[BenChain]

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 

[camargos88 · Accepted solution]

Hi @BenChain ,

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 

[BenChain]

Thank you, this worked for me perfectly!

[Anonymous]

try also this:

Table.AddColumn(Source, "max", each List.Max(Record.FieldValues(_)))

[Greg_Deckler]

@BenChain - 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.