This is a brief follow on to my earlier blog on the Maven Analytics NYC Taxi challenge, and cleaning well over 25 million line items of data in Power Query, before developing a Power BI dashboard. In particular, I am looking at amending an “If/And” statement to become a nested “If/Or/And” statement.

One of the cleaning steps involved looking at values in multiple columns to check that if they were ALL negative, then we should change each value to a positive (multiply by -1).

Original M Code

To do the above, my original M Code involved adding a new column which returned a statement based on a set of IF/AND statements

= Table.AddColumn(#"Changed Type", "negative_charges", 
each if [fare_amount] <0 and 
[extra] <0 and 
[mta_tax] <0 and 
[improvement_surcharge] <0 and 
[congestion_surcharge] <0 then 
"all negative" else 
"ok")

This effectively adds a new column called “negative charges”, with each row value being returned as either “all negative” or “ok” based on the if statements.

So, if the fare amount, extra, mta tax, improvement surcharge AND congestion surcharge were ALL negative for that particular journey, then the result would be “all negative”.

However, if one or more were greater than or equal to zero, they would be deemed “ok”. This would allow us to then amend values in rows which were only calculated as “all negative”.

Minor Problem

It was pointed out to me however, that the [congestion surcharge] may also contain ‘null’ values. These would not be considered in the above calculation. Typically, we may consider ‘null’ to equal zero, therefore it would not matter; however for these surcharges, ‘null’ may mean that the surcharge was not being applied at that time.

Therefore, I had to look at amending the M code to accommodate this.

Updated M Code Solution

In the end, the amendment is not so complicated, but you can create a nested conditional statement by wrapping it in parentheses – see below amended M code. This allows you to check for multiple Or and And conditions and return the appropriate result.

= Table.AddColumn(#"Changed Type", "All Negative Check", 
each if ([fare_amount] <0 or [fare_amount] = null) and 
([extra] <0 or [extra] = null) and
([mta_tax] <0 or [mta_tax] = null) and
([improvement_surcharge] <0 or [improvement_surcharge] = null) and 
([congestion_surcharge] <0 or [congestion_surcharge] = null) then 
"all negative" else 
"ok")

If you want to see this in action, I have created a short walk through on my youtube channel here.