Forum Discussion

PatrickWong's avatar
PatrickWong
Helper I
3 years ago

Preferred column value based on another column + conditional blank

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

Need Help

4 Replies

  • wdx223_Daniel's avatar
    wdx223_Daniel
    Community Champion
    3 years ago

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

    • PatrickWong's avatar
      PatrickWong
      Helper I
      3 years ago

      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's avatar
        wdx223_Daniel
        Community Champion
        3 years ago

        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]))

  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    Community Support
    3 years ago

    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.