Forum Discussion
Solved
Min/MAX column with numeric maximum value
Hello all, I am trying to add a cusom column to calculate the Max and Min for 3 columns(More in the future). Issue is that the data is alphanumeric for Cancelled values. So when I try it, I get t...
Hi Binara ,
Try copying this query in your advanced editor:let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjRX0lHKK83JgVGxOtFKRsZAnnNiXnJqTk5qCpBtaKkUGwsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [D1 = _t, D2 = _t, D3 = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"D1", type text}, {"D2", type text}, {"D3", type text}}), Custom1 = Table.AddColumn(#"Changed Type", "Lists", each Text.Split(Text.Combine({[D1],[D2],[D3]},"|"),"|")), Custom2 = Table.AddColumn(#"Custom1", "MaxVal", each List.Max(List.Transform([Lists], each try Number.FromText(_) otherwise null))), Custom3 = Table.AddColumn(#"Custom2", "MinVal", each List.Min(List.Transform([Lists], each try Number.FromText(_) otherwise null))), #"Removed Columns" = Table.RemoveColumns(Custom3,{"Lists"}) in #"Removed Columns"Sample output:
