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:

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:

# 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…

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:

# 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!

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.

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

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. 🙂

You got it, Anthony. 🙂

Ken --- building on Matt's comment:

List.NonNullCount({ [Path1], [Path2] }), Int64.Type)

Thanks - DrewBbc

Nice! 🙂