Forum Discussion
How to extract Max from a row in Query editor
Hi Team, I am looking for a solution to be implanted in query editor, that I need to take max string from each row. For example: Loaded months Output M01 M01 M03 M03 M01,M0...
You can add a column with this formua in it:
List.Max(List.Transform(Text.Split([Loaded months], ","), Text.Trim))
It splits the string up to a list, trims it (as you sometimes have spaces between your elements and sometimes not) and selects the MAX (in alphabetical order) from it.
Hmm, the DAX for this wouldn't be that difficult. Maybe ImkeF has a suggestion for Power Query.
Oooo! Wait, this works:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8jUwVIrVAdHGUNpQx9fASAeVb4ymxgQupwBUjMIBEcbkiYAIExBhCiLMlGJjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Loaded months" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Loaded months", type text}}),
#"Replaced Value" = Table.ReplaceValue(#"Changed Type"," ","",Replacer.ReplaceText,{"Loaded months"}),
#"Added Custom" = Table.AddColumn(#"Replaced Value", "Custom", each List.Max(Text.Split([Loaded months],",")))
in
#"Added Custom"