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,M02,M03 | M03 |
| M01,M03 | M03 |
| M03 | M03 |
| M04 | M04 |
| M01, M02 | M02 |
| M01, M02, M03 | M03 |
| M01, M02, M03 | M03 |
| M01, M02, M03 | M03 |
| M01, M02, M03, M04, M05, M06 | M06 |
Thanks for your support
Regards
Abhijit
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.
5 Replies
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"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
is the data always sorted? if so you could just take last 3 characters
Text.End([Loaded months],3)
