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.
Greg_Deckler
Community Champion
Community ChampionHmm, the DAX for this wouldn't be that difficult. Maybe ImkeF has a suggestion for Power Query.
- Greg_Deckler
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" - ImkeF
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.
Thanks ImkeF, this is the solution I am looking for.
Have a great day ahead!
Thanks and Regards
Abhijit