Conditional Column, IF statement or something else?

[lennox25]

Hello, 

I'm stuck. I have the above data in a table below.

I need to look at Result 2 against Result 1. If Result  is better (UP), Worse (DOWN) or the same (SAME) then I need a conditional column of IF statement or something else to show in the 'Result' Column.

Results - Good being the best, then Satisfactory, Poor and Very Poor.

Thank you! 🙂

[AlienSx · Accepted solution]

let
    Source = your_table,
    score = [Very Poor = 1, Poor = 2, Satisfactory = 3, Good = 4],
    res = {"DOWN", "SAME", "UP"},
    result = Table.AddColumn(
        Source, 
        "Result", 
        (x) => res{Value.Compare(Record.Field(score, x[Result 2]), Record.Field(score, x[Result 1])) + 1})
in
    result

[dufoq3 · Accepted solution]

Hi @lennox25, another solution here:

you can edit this step and add/remove conditions - don't forget to keep correct order from best (lowest value) to worst.

Result

let
    DataTable = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wcs/PT1HSUQpOLMksTktMLskvqlSK1YlGFdBRCsjPL8ImjqEPrBC3erB1IPGw1KJKBXTFUMcg5JAEITpjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Result 1" = _t, #"Result 2" = _t]),
    ConditionTable = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wcs/PT1HSUTJUitWJVgpOLMksTktMLskvqgQKGoEFA/Lzi4AcYzAnLLWoUgEqYqIUGwsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Condition = _t, #"Order Best to Worst" = _t]),
    ConditionTableChangedType = Table.TransformColumnTypes(ConditionTable,{{"Order Best to Worst", Int64.Type}}),
    MergedQueriesRes1 = Table.NestedJoin(DataTable, {"Result 1"}, ConditionTableChangedType, {"Condition"}, "Res1Value", JoinKind.LeftOuter),
    ExpandedRes1Value = Table.ExpandTableColumn(MergedQueriesRes1, "Res1Value", {"Order Best to Worst"}, {"Res1Value"}),
    MergedQueriesRes2 = Table.NestedJoin(ExpandedRes1Value, {"Result 2"}, ConditionTableChangedType, {"Condition"}, "Res2Value", JoinKind.LeftOuter),
    ExpandedRes2Value = Table.ExpandTableColumn(MergedQueriesRes2, "Res2Value", {"Order Best to Worst"}, {"Res2Value"}),
    Ad_Result = Table.AddColumn(ExpandedRes2Value, "Result", each if [Res1Value] < [Res2Value] then "UP" else if [Res1Value] = [Res2Value] then "SAME" else "DOWN", type text),
    RemovedColumns = Table.RemoveColumns(Ad_Result,{"Res1Value", "Res2Value"})
in
    RemovedColumns

[lennox25]

Hi @dufoq3 -  Im unsure how to do this? I have created a 'Condition Table' but have now idea how to work this with my orignal table where there is already many applied steps?

[dufoq3]

Hi, have you read note below my post? Keep in mind that in this case Source is DataTable (in my query).

[lennox25]

Hi @dufoq3 yes, thanks, ok got that part now I have a seperate query resulting in exactly the same as you first answered just have no clue how to incorporate that into my working table

[dufoq3]

Edit 1st step of MyQuery this way:

[AlienSx · Accepted solution]

let
    Source = your_table,
    score = [Very Poor = 1, Poor = 2, Satisfactory = 3, Good = 4],
    res = {"DOWN", "SAME", "UP"},
    result = Table.AddColumn(
        Source, 
        "Result", 
        (x) => res{Value.Compare(Record.Field(score, x[Result 2]), Record.Field(score, x[Result 1])) + 1})
in
    result

[lennox25]

Hi @AlienSx , Thank you. It seems simple but how to I incoporate this into my table which already has many applied steps? TIA

[AlienSx]

@lennox25 create blank query, copy and paste my code, replace your_table in my code with the name of your query - the one with many steps (it's name is on the left pane in PQ Editor)