Forum Discussion

lennox25's avatar
lennox25
Post Patron
2 years ago
Solved

Conditional Column, IF statement or something else?

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! 🙂

Need Help
  • AlienSx's avatar
    AlienSx
    2 years ago
    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's avatar
    dufoq3
    2 years ago

    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

     

8 Replies

  • AlienSx's avatar
    AlienSx
    Super User
    2 years ago
    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's avatar
      lennox25
      Post Patron
      2 years ago

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

      • AlienSx's avatar
        AlienSx
        Super User
        2 years ago

        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)

  • dufoq3's avatar
    dufoq3
    Community Champion
    2 years ago

    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's avatar
      lennox25
      Post Patron
      2 years ago

      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's avatar
        dufoq3
        Community Champion
        2 years ago

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