How to extract Max from a row in Query editor

[Anonymous]

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

[Stachu]

is the data always sorted? if so you could just take last 3 characters

Text.End([Loaded months],3)

[Greg_Deckler]

Hmm, the DAX for this wouldn't be that difficult. Maybe @ImkeF has a suggestion for Power Query.

[ImkeF · Accepted solution]

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.

[Anonymous]

Thanks @ImkeF, this is the solution I am looking for.

Have a great day ahead!

Thanks and Regards

Abhijit

[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"