Forum Discussion
lennox25
Post Patron
Post PatronConditional 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 co...
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 resultHi 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
dufoq3
Community Champion
Community ChampionHi 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
lennox25Hi 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).