Preferred column value based on another column + conditional blank

[PatrickWong]

I have a difficult problem (or so I think)

The table comes from column A to F (green portion)

I am able to get column G based on List.Max({[1],[2],[3],[4]}) in power query

but I am not able to think of a solution for column H and I based on the rules in column J

Hep much appreciated

[v-yanjiang-msft]

Hi @PatrickWong ,

According to your description, here's my solution, add a custom column.

if Record.FieldOrDefault(_,Text.From([Preferred route]))<>null then [Preferred route] else Table.ColumnNames(Source){List.PositionOf({[1],[2],[3],[4]},List.Max({[1],[2],[3],[4]}))+1}

Get the correct result:

Here's the whole M syntax, you can copy-paste it in a blank query to see the details:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wck4sUtJRMjcAEqZgwgxIAJGxUqxOtFJIUWlyNogHkjI2BRImBhB5E7C8b35JflFyZXJOKlDIDCRlBtJvBFZuDGIpxcYCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Model = _t, #"1" = _t, #"2" = _t, #"3" = _t, #"4" = _t, #"Preferred route" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"1", Int64.Type}, {"2", Int64.Type}, {"3", Int64.Type}, {"4", Int64.Type}, {"Preferred route", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "recommend route", each if Record.FieldOrDefault(_,Text.From([Preferred route]))<>null then [Preferred route] else if[1]=List.Max({[1],[2],[3],[4]})then 1 else if [2]=List.Max({[1],[2],[3],[4]}) then 2 else if [3]=List.Max({[1],[2],[3],[4]})then 3 else 4),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom", each if Record.FieldOrDefault(_,Text.From([Preferred route]))<>null then [Preferred route] else Table.ColumnNames(Source){List.PositionOf({[1],[2],[3],[4]},List.Max({[1],[2],[3],[4]}))+1}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom1",{{"Custom", Int64.Type}})
in
    #"Changed Type1"

I attach my sample below for your reference.

Best regards,

Community Support Team_yanjiang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[wdx223_Daniel]

=Record.FieldOrDefault(_,Text.From([Preferred route]),List.Max(List.Range(Record.ToList(_),1,3)))

[PatrickWong]

Thanks, This works for column I although in truck, it returns a null value. I was able to use DAX to complete column I to what is required.

The next challange is how to get to the values of column H, it needs some logic to determine which column has the highest value and return the column (name: 1,2 ,3 or 4)

[wdx223_Daniel]

M code:

=Record.FieldOrDefault(_,Text.From([Preferred route]),List.Max(List.Range(Record.ToList(_),1,4)))

DAX code:

=VAR _p=Table[1]&"|"&Table[2]&"|"&Table[3]&"|"&Table[4] VAR _r=VALUE(0&PATHITEM(_p,Table[Preferred Route])) RETURN IF(_r,_r,MAXX(ADDCOLUMNS(GENERATESERIES(1,4),"v",VALUE(0&PATHITEM(_p,[Value]))),[v]))