Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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
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
Thank you, this worked for me perfectly!
try also this:
Table.AddColumn(Source, "max", each List.Max(Record.FieldValues(_)))
@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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.