Negating a number

Power Query
1

I am trying to negate a number if it meets a condition in a Custom Column Dialog.

=if[Negate]=“Yes” then [[6 mths Actuals]] else [[6 mths Actuals]]

How do I to multiply the value that meets the condition by -1.

Should the syntax be similar to this
#“Inserted Multiplication” = Table.AddColumn(#“Expanded TB”, “Multiplication”, each [6 mths Actuals] * -1, type number)

Thank You

2

This is in the new column dialog box:

Negation = IF(‘Category Codes’[Code Max] > 5000,‘Category Codes’[Code Max]*-1,‘Category Codes’[Code Max])

image
image.png804×396 13.4 KB

3

Thank You for your help

I put the following into the Custom Dialog Box

Custom = IF([GL Accounts Table.Account Class] = “Income”,[6 mths Actuals]*-1,[6 mths Actuals])

which generated this code

#“Added Custom” = Table.AddColumn(#“Expanded GL Accounts Table”, “Custom”, each IF([GL Accounts Table.Account Class] = “Income”,[6 mths Actuals]*-1,[6 mths Actuals]))

The Error I get is

Expression.Error: The name ‘IF’ wasn’t recognized. Make sure it’s spelled correctly.

What change do I need to make /

Thanks

4

@AllisterB,

The M code below is valid for entry in a Custom Column Dialog box:

if [Negate] = “Yes” then [6 mths Actuals] else [6 mths Actuals] *-1

if [GL Accounts Table.Account Class] = “Income” then [6 mths Actuals] *-1 else [6 mths Actuals]

Now if your [6 mths Actuals] is not yet of type number change it into:

Number.From([6 mths Actuals])

if this doesn’t resolve your issue please post a small sample, so we can better assist you.

5

I didn’t see this was in the Query Editor section. M is case sensitive. try changing the if to lowercase.

6

My code is like this and I the Custom v column only contains errors. Some of the values in GL Accounts Table.Account Class are null

Code is

let
Source = Table.NestedJoin(#“2019 Accredo Budget Export xlsx”, {“Branch”}, #“Accredo BRANCH Table”, {“Branch Code”}, “Accredo BRANCH Table”, JoinKind.LeftOuter),
#“Expanded Accredo BRANCH Table” = Table.ExpandTableColumn(Source, “Accredo BRANCH Table”, {“Branch Name”, “Inactive”}, {“Accredo BRANCH Table.Branch Name”, “Accredo BRANCH Table.Inactive”}),
#“Merged Queries” = Table.NestedJoin(#“Expanded Accredo BRANCH Table”, {“Department”}, #“Accredo Depts Table”, {“Department Code”}, “Accredo Depts Table”, JoinKind.LeftOuter),
#“Expanded Accredo Depts Table” = Table.ExpandTableColumn(#“Merged Queries”, “Accredo Depts Table”, {“Department Name”, “Inactive”}, {“Accredo Depts Table.Department Name”, “Accredo Depts Table.Inactive”}),
#“Merged Queries1” = Table.NestedJoin(#“Expanded Accredo Depts Table”, {“GL Account”}, #“GL Accounts Table”, {“Account Code”}, “GL Accounts Table”, JoinKind.LeftOuter),
#“Expanded GL Accounts Table” = Table.ExpandTableColumn(#“Merged Queries1”, “GL Accounts Table”, {“Account Name”, “Account Class”, “Account Type”, “Inactive”}, {“GL Accounts Table.Account Name”, “GL Accounts Table.Account Class”, “GL Accounts Table.Account Type”, “GL Accounts Table.Inactive”}),
#“Added Custom” = Table.AddColumn(#“Expanded GL Accounts Table”, “Custom”, each if [GL Accounts Table.Account Class] = “Income” then [6 mths Actuals] *-1 else [6 mths Actuals]),
#“Filtered Rows” = Table.SelectRows(#“Added Custom”, each ([GL Accounts Table.Account Class] = “Expense”)),
Custom = #“Filtered Rows”{4}[Custom]
in
Custom

Error shown is

Expression.Error: The field ‘GL Accounts Table.Account Class’ of the record wasn’t found.
Details:
Budget Rpt Name=Budget Preparation Report 15
Account Code=2018.000
Branch=29
Department=
GL Account=2018
Account Name=Contract Staff
6 mths Actuals=28333.39
6 My ths Budget=49999.98

Thank You

7

Ok, so try this:

#“Added Custom” = Table.AddColumn(#“Expanded GL Accounts Table”, “Custom”, each try if [GL Accounts Table.Account Class] = “Income” then [6 mths Actuals] *-1 else [6 mths Actuals] otherwise null),

8

Hi @AllisterB, we’ve noticed that no response has been received from you within the last 24 hrs. We just want to check if you still need further help with this post? In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the checkbox. Thanks!