MCode - if/then/else - troubleshooting help

[amyfb]

I can't get the last step with an "if/then/else construct" to return correct values.
I've proved that the Rate can be multiplied by the Per Case column. But I can't get that multiplication to execute in the If/then statement. The text (UOM) has been trimmed, I checked the spelling of the criteria, and the data types are all correctly set. I'm out of ideas and have written that If/then in every way I could think of but it doesn't do the math.

Any clues from the wizards here?

thanks
amyfb

let
    Source = Table.NestedJoin(commissionWdateParam, {"SKU"}, internalLookup, {"ITEM_CODE"}, "internalLookup", JoinKind.LeftOuter),
    #"Expanded internalLookup" = Table.ExpandTableColumn(Source, "internalLookup", {"UOM", "DF", "FF", "T9", "T9-calculated", "PER_CASE", "TFC", "WEIGHT", "LTFC"}, {"UOM", "DF", "FF", "T9", "T9-calculated", "PER_CASE", "TFC", "WEIGHT", "LTFC"}),
    #"change type (factors to decimal)" = Table.TransformColumnTypes(#"Expanded internalLookup",{{"DF", type number}, {"FF", type number}, {"T9", type number}}),
    #"calc newLTFC" = Table.AddColumn(#"change type (factors to decimal)", "newLTFC", each ( ( ([WEIGHT] * 5.04) - ([WEIGHT] * [FF]) * 1.17) + [LTFC])),
    #"calc newT9" = Table.AddColumn(#"calc newLTFC", "newT9", each [newLTFC] * [T9]),
    #"Changed Type" = Table.TransformColumnTypes(#"calc newT9",{{"newLTFC", type number}, {"newT9", type number}}),
    #"calc threshold for newComm" = Table.AddColumn(#"Changed Type", "Threshold", each (([newT9] - [newLTFC]) / 2) + [newLTFC]),
    #"calc newComm" = Table.AddColumn(#"calc threshold for newComm", "newComm", each if [RATE] > [newT9] then "Full Commission" else 
if [RATE] < [newLTFC] then "No Commission" else if [RATE] > [newLTFC] and [RATE] < [Threshold] then "1% Commission" else if [RATE] > [Threshold] and [RATE] < [newT9] then "Half Commission" else "check"),
    #"add commission  change alert" = Table.AddColumn(#"calc newComm", "FlagCommissionChange", each if [currComm] <> [newComm] then "change alert" else null),
    #"Reordered Columns" = Table.ReorderColumns(#"add commission  change alert",{"SKU", "ITEM_SHORT_NAME", "PRICE_LIST", "commission", "t9 uom", "ltfc uom", "RATE", "PRICE_LIST_RATE", "oldThreshold", "UOM", "DF", "FF", "T9", "T9-calculated", "PER_CASE", "TFC", "WEIGHT", "LTFC", "newLTFC", "newT9", "Threshold", "currComm", "newComm", "FlagCommissionChange"}),
    #"calc SM % change" = Table.AddColumn(#"Reordered Columns", "SM % change", each (( [RATE] - [newT9] ) / [RATE]) - (([RATE] - [#"T9-calculated"] )/ [RATE])),
    #"calc GM % change" = Table.AddColumn(#"calc SM % change", "GM % change", each (( [RATE] - [newLTFC]) / [RATE]) - (([RATE] - [LTFC] )/ [RATE])),
    #"rate times per case to proof" = Table.AddColumn(#"calc GM % change", "rateperCaseCustom", each [RATE] * [PER_CASE]),
    #"Changed Type1" = Table.TransformColumnTypes(#"rate times per case to proof",{{"rateperCaseCustom", type number}}),
    #"Trimmed Text" = Table.TransformColumns(#"Changed Type1",{{"UOM", Text.Trim, type text}}),
    trytofixrate = Table.AddColumn(#"Trimmed Text", "fixtherateCustom", each if [UOM] = "Each" then [RATE] * [PER_CASE] else if [UOM] <> "Each" then [RATE] * 1 else "")
in
    trytofixrate

 

[amyfb]

I did get it to work by redoing the query with the if/then as the first action step. All is good now. If anyone know why the sequence made a difference, I'm all ears. thanks.