Adding Try Results is Trying

I was playing around with a scenario this morning where I was adding try results together in order to count how many columns were filled with information.  What I needed to do kind of surprised me a little bit.

The Goal

There's a hundred ways to do this, but I was trying to write a formula that returns the total count of options someone has subscribed to based on the following data:

image

To return this:

Adding try results - Attempt 1

So I figured I'd try to add the position of the first characters (which should always be one) together.  If there is a null value, it won't have a character, so will need the try statement to provide a 0 instead.  I cooked up the following formula to return a 1 or an error:

= Text.PositionOf([Golf Option 1],Text.Middle([Golf Option 1],1,1))

And then, to replace the error with a 0, modified it to this:

= try Text.PositionOf([Golf Option 1],Text.Middle([Golf Option 1],1,1)) otherwise 0
+
try Text.PositionOf([Golf Option 2],Text.Middle([Golf Option 2],1,1)) otherwise 0

But when I tried to commit it, I got this feedback:

image

Adding try results - Attempt 2

Now I've seen this kind of weirdness before, so I knew what do do here.  You wrap the final try clause in parenthesis like this:

= try Text.PositionOf([Golf Option 1],Text.Middle([Golf Option 1],1,1)) otherwise 0
+

(
try Text.PositionOf([Golf Option 2],Text.Middle([Golf Option 2],1,1)) otherwise 0)

At least now the formula compiles.  But the results weren't exactly what I expected…

image

So why am I getting 1 where there should plainly be a result of 2 for the highlighted records?

Adding try results - The fix

Just on a whim, I decided to wrap BOTH try clauses in parenthesis, like this:

= (try Text.PositionOf([Golf Option 1],Text.Middle([Golf Option 1],1,1)) otherwise 0)
+
(try Text.PositionOf([Golf Option 2],Text.Middle([Golf Option 2],1,1)) otherwise 0)

And the results are what I need:

image

So why?

I thought this was pretty weird, but looking back at it in retrospect, it is following the correct order of operations.  The original formula I wrote was "otherwise 0 + …".  So in truth, the entire second try statement was only getting evaluated if no Golf Option 1 was present.

I guess writing formulas is hard in any language!

6 thoughts on “Adding Try Results is Trying

  1. I realize this is probably an example to make the point about how "try" works, but you could just select all the columns, then do Add Columns -> Statistics -> Count Values to do the same thing through the UI.

  2. Hi Ken,
    To summarise:
    = try 1 otherwise 0 + try 1 otherwise 0 => cannot work because it needs ()
    = try 1 otherwise 0 + (try 1 otherwise 0) => means = try 1 otherwise ( 0 + (try 1 otherwise 0) )
    = (try 1 otherwise 0) + (try 1 otherwise 0) => means what it means

  3. True enough Matthew. I was actually working on something for a course to show how try works. As always, there are multiple ways to get the job done. 🙂

  4. Ken --- building on Matt's comment:
    List.NonNullCount({ [Path1], [Path2] }), Int64.Type)

    Thanks - DrewBbc

Leave a Reply

Your email address will not be published. Required fields are marked *