cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
PatrickWong
Helper I
Helper I

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)

PatrickWong_0-1685347285192.png

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

4 REPLIES 4
v-yanjiang-msft
Community Support
Community Support

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:

vyanjiangmsft_0-1685515798437.png

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
Super User
Super User

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

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)

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

Helpful resources

Announcements
PBI Sept Update Carousel

Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

Learn Live

Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

Dashboard in a day with date

Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

Top Solution Authors
Top Kudoed Authors